Oracle自适应共享游标
自适应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g的新特性之一，主要用于解决以前版 本中由于绑定变量窥探导致SQL语句无法获得最佳执行计划的缺陷，即能够对效率低下的游标(子游标)进行自动识别而选择最佳的执行计划。本 文详细描述了自适应游标共享并给出示例。 有关绑定变量窥探请参考：Oracle 绑定变量窥探
一、示例自适应游标共享 1、创建演示环境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> create table t(id,owner,object_id) as
2 select rownum,owner,object_id from all_objects where rownum<=1000;
SQL> alter table t add constraint t_pk primary key(id);
SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
SQL> select count(id),count(distinct id),min(id),max(id) from t;
COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
---------- ----------------- ---------- ----------
1000 1000 1 1000 2、使用绑定变量执行SQL语句并获得首次执行情况
SQL> var v_id number;
SQL> exec :v_id:=9;
SQL> set linesize 180
SQL> select sum(object_id) from t where id<:v_id;
SUM(OBJECT_ID)
--------------
2078
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
SQL_ID 7qcp6urqh7d2j, child number 0
-------------------------------------
select sum(object_id) from t where id<:v_id -->变量值为9时，使用了正确的执行计划，且预估的行数也准确
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
SQL> col SQL_TEXT format a45 -->下面的语句获得自适应游标共享的3个字段值
SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
------------- --------------------------------------------- ------------ ---------- - - -
7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 0 1 Y N Y 3、自适应游标共享的外在体现 自适应游标共享主要通过三个字段来得以体现，即is_bind_sensitive,is_bind_aware,is_shareable。(注:此三个字段仅在Oracle 11g 中存在)。通过上面从v$sql(v$sqlarea中不存在is_shareable)的查询可知，三个字段分别被赋予了不同的值，代表了不同的含义。 is_bind_sensitive(绑定是否敏感) 表示该子游标中是否使用了绑定变量要素，且采用bind peeking方法进行执行计划生成。如果执行计划依赖于窥探到的值，此处为Y， 否则为N。 is_bind_aware(绑定是否可知) 表示该子游标是否使用了extended cursor sharing技术，是则为Y，否则为N，如为N，则该游标将废弃，不再可用。 is_shareable(是否可共享) 表示该子游标可否被下次软解析是否可共享使用。可共享则为Y，否则为N，表示该子游标失去了共享价值，按LRU算法淘汰。 由于该SQL语句为首次执行，因此从v$sql查询的结果中得知 is_bind_sensitive 为Y值(首次运行，执行了bind peeking) is_bind_aware 为N值(首次运行，不被extended cursor sharing支持) is_shareable 为Y值(执行计划可共享)
4、重新赋值后观察游标共享情况
SQL> exec :v_id:=900;
SQL> select sum(object_id) from t where id<:v_id;
SUM(OBJECT_ID)
--------------
1826561
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
SQL_ID 7qcp6urqh7d2j, child number 0
-------------------------------------
select sum(object_id) from t where id<:v_id -->此次执行的变量值为900，执行计划位上次变量为9的执行计划
-->此时为非正确的执行计划，等同于Oracle 9i,10g中的情形
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
-->自适应游标共享的3个字段值并未发生任何变化
SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
------------- --------------------------------------------- ------------ ---------- - - -
7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 0 2 Y N Y
SQL> select sum(object_id) from t where id<:v_id; -->再次执行变量为900值的SQL语句
SUM(OBJECT_ID)
--------------
1826561
SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));
SQL_ID 7qcp6urqh7d2j, child number 1
-------------------------------------
select sum(object_id) from t where id<:v_id -->此时执行计划较上一次发生了变化，使用了全表扫描,Rows接近于实际值
-->自适应游标共享特性得以体现
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
-->自适应游标共享特性的几个值发生了变化，生成了新的子游标，其子游标号为1
SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I
------------- --------------------------------------------- ------------ ---------- - - -
7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 0 2 Y N Y
7qcp6urqh7d2j select sum(object_id) from t where id<:v_id 1 1 Y Y Y
SQL> exec :v_id:=800 -->为变量赋于不同的值
SQL> select sum(object_id) from t where id<:v_id; -->利用新的变量值执行SQL语句
SUM(OBJECT_ID)
--------------
1548431
SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
SQL_ID CHILD_NUMBER EXECUTIONS I I I
------------- ------------ ---------- - - -
7qcp6urqh7d2j 0 2 Y N Y
7qcp6urqh7d2j 1 1 Y Y N
7qcp6urqh7d2j 2 1 Y Y Y -->生成了新的子游标号为2
SQL> exec :v_id:=500; -->为变量赋于新值
SQL> select sum(object_id) from t where id<:v_id; -->利用新的变量值执行SQL语句
SUM(OBJECT_ID)
--------------
826694
/**************************************************/
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: robinson_0612@hotmail.com */
/* QQ: 645746311 */
/**************************************************/
SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable
2 from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';
SQL_ID CHILD_NUMBER EXECUTIONS I I I
------------- ------------ ---------- - - -
7qcp6urqh7d2j 0 2 Y N Y
7qcp6urqh7d2j 1 1 Y Y N
7qcp6urqh7d2j 2 1 Y Y N -->注意看子游标1,2的is_shareable值为N，表示不可共享
7qcp6urqh7d2j 3 1 Y Y Y -->生成了新的子游标号为3，
-->查看最终该SQL语句的不同子游标的所有执行计划
SQL> select * from table(dbms_xplan.display_cursor('7qcp6urqh7d2j',null,'typical -predicate'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID 7qcp6urqh7d2j, child number 0
-------------------------------------
select sum(object_id) from t where id<:v_id -->0号子游标为索引范围扫描
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
SQL_ID 7qcp6urqh7d2j, child number 1
-------------------------------------
select sum(object_id) from t where id<:v_id -->1号子游标为全表扫描，其预估的行数接近实际影响行数的值为900
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL_ID 7qcp6urqh7d2j, child number 2
-------------------------------------
select sum(object_id) from t where id<:v_id -->2号子游标为全表扫描，但其预估的行数接近实际影响行数的值为800
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS FULL| T | 800 | 6400 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL_ID 7qcp6urqh7d2j, child number 3
-------------------------------------
select sum(object_id) from t where id<:v_id -->3号子游标为全表扫描，但其预估的行数等于实际影响行数的值499
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS FULL| T | 499 | 3992 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------- 二、自适应游标共享的几个相关视图
1、v$sql_cs_statistics 用于监控自适应游标共享的相关统计信息.下面的查询中列出了每个子游标的peeking情况,以及执行次数,预处理行数,BUFFER_GETS等
SQL> select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets
2 from v$sql_cs_statistics where sql_id='7qcp6urqh7d2j'
3 order by 1;
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ ------------------- - ---------- -------------- -----------
0 1706589901 Y 1 17 69 -->17行，索引范围扫描
1 3116944019 Y 1 900 5 -->900行，全表扫描
2 1328865654 Y 1 800 5 -->800行，全表扫描
3 1624350242 Y 1 500 5 -->500行，全表扫描 2、v$sql_cs_selectivity 显示每个子游标的游标的选择性范围。下面的查询中列出了谓词，选择性范围，列上的选择性的值
SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity
2 where sql_id='7qcp6urqh7d2j' order by 1;
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ------------------ ---------- ---------- ----------
1 <V_ID 0 0.809910 0.989890
2 <V_ID 0 0.719820 0.989890
3 <V_ID 0 0.449550 0.989890 3、v$sql_cs_histogram 用于决定一个查询是否允许自适应游标共享，以直方图形式存储
SQL> select CHILD_NUMBER,BUCKET_ID,COUNT from v$sql_cs_histogram where SQL_ID='7qcp6urqh7d2j'
2 order by 1;
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 1 1
0 0 1
0 2 0
1 1 0
1 0 1
1 2 0
2 1 0
2 0 1
2 2 0
3 1 0
3 0 1
3 2 0 三、总结 1、自适应游标共享在SQL语句首次执行时(使用绑定变量)，进行窥探，并记录窥探结果，如果后续有相同的的SQL语句执行，则对窥探结果 进行比较以判断是否需要生成新的执行计划。此即为绑定变量是否敏感。 2、绑定变量的可知性用于判断当前的游标是否为可扩展性游标共享，当不可知时，则游标被废弃。 3、自适应游标共享的实质是在Oracle 10g以前的基础上实现了多次绑定变量窥探，增加了获取最佳执行计划选择的机率。 4、尽管使用自适应游标共享特性，但并不能保证每次执行SQL语句一定按最佳计划执行，本次演示中SQL语句的第二次执行并非最佳执行计划。 5、自适应游标共享也不能保证两次执行相同SQL语句一定按相同的执行计划执行，因为自适应游标共享会不断尝试peeking。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2011年11月01日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读