Oracle自适应共享游标

拼搏现实的明天。 2022-05-15 13:10 144阅读 0赞

自适应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g的新特性之一，主要用于解决以前版  
本中由于绑定变量窥探导致SQL语句无法获得最佳执行计划的缺陷，即能够对效率低下的游标(子游标)进行自动识别而选择最佳的执行计划。本  
文详细描述了自适应游标共享并给出示例。  
有关绑定变量窥探请参考：[Oracle 绑定变量窥探][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。  
  
四、延伸参考  
 [Oracle 绑定变量窥探  
][Oracle] [Oracle自适应共享游标  
][Oracle_] [绑定变量及其优缺点][Link 1]  
[ 父游标、子游标及共享游标][Link 2]  
[ dbms\_xplan之display\_cursor函数的使用][dbms_xplan_display_cursor]  
[ dbms\_xplan之display函数的使用][dbms_xplan_display]  
[ 执行计划中各字段各模块描述][Link 3]

[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6923627
[Oracle_]: http://blog.csdn.net/robinson_0612/article/details/6923670
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/6904229
[Link 2]: http://blog.csdn.net/robinson_0612/article/details/6893477
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[dbms_xplan_display]: http://blog.csdn.net/robinson_0612/article/details/6866870
[Link 3]: http://blog.csdn.net/robinson_0612/article/details/6860007