ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle自适应共享游标

Oracle自适应共享游标

Oracle 作者：dawn009 时间：2014-08-01 18:05:08 0 删除 编辑
    自适应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g的新特性之一，主要用于解决以前版
本中由于绑定变量窥探导致SQL语句无法获得最佳执行计划的缺陷，即能够对效率低下的游标(子游标)进行自动识别而选择最佳的执行计划。本
文详细描述了自适应游标共享并给出示例。
    有关绑定变量窥探请参考：Oracle 绑定变量窥探

一、示例自适应游标共享
    1、创建演示环境       

[sql] view plaincopyprint?
  1. SQL> select * from v$version where rownum<2;                                                    
  2.                                                                                                 
  3. BANNER                                                                                          
  4. --------------------------------------------------------------------------------                
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                          
  6.                                                                                                     
  7. SQL> create table t(id,owner,object_id) as                                                      
  8.   2  select rownum,owner,object_id from all_objects where rownum<=1000;                         
  9.                                                                                                 
  10. SQL> alter table t add constraint t_pk primary key(id);                                         
  11.                                                                                                 
  12. SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);                             
  13.                                                                                                 
  14. SQL> select count(id),count(distinct id),min(id),max(id) from t;                                
  15.                                                                                                 
  16.  COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)                                              
  17. ---------- ----------------- ---------- ----------                                              
  18.       1000              1000          1       1000                                              

    2、使用绑定变量执行SQL语句并获得首次执行情况        

[sql] view plaincopyprint?
  1. SQL> var v_id number;                                                                                              
  2. SQL> exec :v_id:=9;                                                                                                
  3. SQL> set linesize 180                                                                                              
  4. SQL> select sum(object_id) from t where id<:v_id;                                                                  
  5.                                                                                                                    
  6. SUM(OBJECT_ID)                                                                                                     
  7. --------------                                                                                                     
  8.           2078                                                                                                     
  9. SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));                               
  10. SQL_ID  7qcp6urqh7d2j, child number 0                                                                              
  11. -------------------------------------                                                                              
  12. select sum(object_id) from t where id<:v_id            -->变量值为9时，使用了正确的执行计划，且预估的行数也准确    
  13.                                                                                                                    
  14. Plan hash value: 4270555908                                                                                        
  15.                                                                                                                    
  16. -------------------------------------------------------------------------------------                              
  17. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                              
  18. -------------------------------------------------------------------------------------                              
  19. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                              
  20. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                              
  21. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |                              
  22. |   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |                              
  23. -------------------------------------------------------------------------------------                              
  24.                                                                                                                    
  25. SQL> col SQL_TEXT format a45    -->下面的语句获得自适应游标共享的3个字段值                                         
  26. SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                   
  27.   2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';   
  28.                                                                                                                    
  29. SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I                          
  30. ------------- --------------------------------------------- ------------ ---------- - - -                          
  31. 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] view plaincopyprint?
  1. SQL> exec :v_id:=900;                                                                                                  
  2. SQL> select sum(object_id) from t where id<:v_id;                                                                      
  3.                                                                                                                        
  4. SUM(OBJECT_ID)                                                                                                         
  5. --------------                                                                                                         
  6.        1826561                                                                                                         
  7.                                                                                                                        
  8. SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));                                   
  9.                                                                                                                        
  10. SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
  11. -------------------------------------                                                                                  
  12. select sum(object_id) from t where id<:v_id  -->此次执行的变量值为900，执行计划位上次变量为9的执行计划                 
  13.                                              -->此时为非正确的执行计划，等同于Oracle 9i,10g中的情形                    
  14. Plan hash value: 4270555908                                                                                            
  15.                                                                                                                        
  16. -------------------------------------------------------------------------------------                                  
  17. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
  18. -------------------------------------------------------------------------------------                                  
  19. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
  20. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
  21. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |                                  
  22. |   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |                                  
  23. -------------------------------------------------------------------------------------                                  
  24.                                                                                                                        
  25. -->自适应游标共享的3个字段值并未发生任何变化                                                                           
  26. SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                       
  27.   2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
  28.                                                                                                                        
  29. SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I                              
  30. ------------- --------------------------------------------- ------------ ---------- - - -                              
  31. 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id              0          2 Y N Y                              
  32.                                                                                                                        
  33. SQL> select sum(object_id) from t where id<:v_id;   -->再次执行变量为900值的SQL语句                                    
  34.                                                                                                                        
  35. SUM(OBJECT_ID)                                                                                                         
  36. --------------                                                                                                         
  37.        1826561                                                                                                         
  38.                                                                                                                        
  39. SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));                                   
  40.                                                                                                                        
  41. SQL_ID  7qcp6urqh7d2j, child number 1                                                                                  
  42. -------------------------------------                                                                                  
  43. select sum(object_id) from t where id<:v_id    -->此时执行计划较上一次发生了变化，使用了全表扫描,Rows接近于实际值      
  44.                                                -->自适应游标共享特性得以体现                                           
  45. Plan hash value: 2966233522                                                                                            
  46.                                                                                                                        
  47. ---------------------------------------------------------------------------                                            
  48. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  49. ---------------------------------------------------------------------------                                            
  50. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  51. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  52. |   2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
  53. ---------------------------------------------------------------------------                                            
  54.                                                                                                                        
  55. -->自适应游标共享特性的几个值发生了变化，生成了新的子游标，其子游标号为1                                               
  56. SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                       
  57.   2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
  58.                                                                                                                        
  59. SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I                              
  60. ------------- --------------------------------------------- ------------ ---------- - - -                              
  61. 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id              0          2 Y N Y                              
  62. 7qcp6urqh7d2j select sum(object_id) from t where id<:v_id              1          1 Y Y Y                              
  63.                                                                                                                        
  64. SQL> exec :v_id:=800      -->为变量赋于不同的值                                                                        
  65.                                                                                                                        
  66. SQL> select sum(object_id) from t where id<:v_id;  -->利用新的变量值执行SQL语句                                        
  67.                                                                                                                        
  68. SUM(OBJECT_ID)                                                                                                         
  69. --------------                                                                                                         
  70.        1548431                                                                                                         
  71.                                                                                                                        
  72. SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                                
  73.   2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
  74.                                                                                                                        
  75. SQL_ID        CHILD_NUMBER EXECUTIONS I I I                                                                            
  76. ------------- ------------ ---------- - - -                                                                            
  77. 7qcp6urqh7d2j            0          2 Y N Y                                                                            
  78. 7qcp6urqh7d2j            1          1 Y Y N                                                                            
  79. 7qcp6urqh7d2j            2          1 Y Y Y    -->生成了新的子游标号为2                                                
  80.                                                                                                                        
  81. SQL> exec :v_id:=500;     -->为变量赋于新值                                                                            
  82.                                                                                                                        
  83. SQL> select sum(object_id) from t where id<:v_id;  -->利用新的变量值执行SQL语句                                        
  84.                                                                                                                        
  85. SUM(OBJECT_ID)                                                                                                         
  86. --------------                                                                                                         
  87.         826694                                                                                                         
  88.                                                                                                                        
  89. /**************************************************/                                                                   
  90. /* Author: Robinson Cheng                         */                                                                   
  91. /* Blog:   http://blog.csdn.net/robinson_0612     */                                                                   
  92. /* MSN:    robinson_0612@hotmail.com              */                                                                   
  93. /* QQ:     645746311                              */                                                                   
  94. /**************************************************/                                                                   
  95.                                                                                                                        
  96. SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                                
  97.   2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
  98.                                                                                                                        
  99. SQL_ID        CHILD_NUMBER EXECUTIONS I I I                                                                            
  100. ------------- ------------ ---------- - - -                                                                            
  101. 7qcp6urqh7d2j            0          2 Y N Y                                                                            
  102. 7qcp6urqh7d2j            1          1 Y Y N                                                                            
  103. 7qcp6urqh7d2j            2          1 Y Y N       -->注意看子游标1,2的is_shareable值为N，表示不可共享                  
  104. 7qcp6urqh7d2j            3          1 Y Y Y       -->生成了新的子游标号为3，                                           
  105.                                                                                                                        
  106. -->查看最终该SQL语句的不同子游标的所有执行计划                                                                         
  107. SQL> select * from table(dbms_xplan.display_cursor('7qcp6urqh7d2j',null,'typical -predicate'));                        
  108.                                                                                                                        
  109. PLAN_TABLE_OUTPUT                                                                                                      
  110. ------------------------------------------------------------------------------------------------------------------     
  111. SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
  112. -------------------------------------                                                                                  
  113. select sum(object_id) from t where id<:v_id     -->0号子游标为索引范围扫描                                             
  114.                                                                                                                        
  115. Plan hash value: 4270555908                                                                                            
  116.                                                                                                                        
  117. -------------------------------------------------------------------------------------                                  
  118. | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
  119. -------------------------------------------------------------------------------------                                  
  120. |   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
  121. |   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
  122. |   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |                                  
  123. |   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |                                  
  124. -------------------------------------------------------------------------------------                                  
  125.                                                                                                                        
  126. SQL_ID  7qcp6urqh7d2j, child number 1                                                                                  
  127. -------------------------------------                                                                                  
  128. select sum(object_id) from t where id<:v_id   -->1号子游标为全表扫描，其预估的行数接近实际影响行数的值为900            
  129.                                                                                                                        
  130. Plan hash value: 2966233522                                                                                            
  131.                                                                                                                        
  132. ---------------------------------------------------------------------------                                            
  133. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  134. ---------------------------------------------------------------------------                                            
  135. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  136. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  137. |   2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
  138. ---------------------------------------------------------------------------                                            
  139.                                                                                                                        
  140. SQL_ID  7qcp6urqh7d2j, child number 2                                                                                  
  141. -------------------------------------                                                                                  
  142. select sum(object_id) from t where id<:v_id   -->2号子游标为全表扫描，但其预估的行数接近实际影响行数的值为800          
  143.                                                                                                                        
  144. Plan hash value: 2966233522                                                                                            
  145.                                                                                                                        
  146. ---------------------------------------------------------------------------                                            
  147. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  148. ---------------------------------------------------------------------------                                            
  149. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  150. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  151. |   2 |   TABLE ACCESS FULL| T    |   800 |  6400 |     3   (0)| 00:00:01 |                                            
  152. ---------------------------------------------------------------------------                                            
  153.                                                                                                                        
  154. SQL_ID  7qcp6urqh7d2j, child number 3                                                                                  
  155. -------------------------------------                                                                                  
  156. select sum(object_id) from t where id<:v_id  -->3号子游标为全表扫描，但其预估的行数等于实际影响行数的值499             
  157.                                                                                                                        
  158. Plan hash value: 2966233522                                                                                            
  159.                                                                                                                        
  160. ---------------------------------------------------------------------------                                            
  161. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
  162. ---------------------------------------------------------------------------                                            
  163. |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
  164. |   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
  165. |   2 |   TABLE ACCESS FULL| T    |   499 |  3992 |     3   (0)| 00:00:01 |                                            
  166. ---------------------------------------------------------------------------                                            

二、自适应游标共享的几个相关视图

 1、v$sql_cs_statistics
        用于监控自适应游标共享的相关统计信息.下面的查询中列出了每个子游标的peeking情况,以及执行次数,预处理行数,BUFFER_GETS等       

[sql] view plaincopyprint?
  1. SQL> select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets                        
  2.   2  from v$sql_cs_statistics where sql_id='7qcp6urqh7d2j'                                                       
  3.   3  order by 1;                                                                                                 
  4.                                                                                                                  
  5. CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS                                         
  6. ------------ ------------------- - ---------- -------------- -----------                                         
  7.            0          1706589901 Y          1             17          69     -->17行，索引范围扫描               
  8.            1          3116944019 Y          1            900           5     -->900行，全表扫描                  
  9.            2          1328865654 Y          1            800           5     -->800行，全表扫描                  
  10.            3          1624350242 Y          1            500           5     -->500行，全表扫描                  

    2、v$sql_cs_selectivity
        显示每个子游标的游标的选择性范围。下面的查询中列出了谓词，选择性范围，列上的选择性的值       

[sql] view plaincopyprint?
  1. SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity         
  2.   2  where sql_id='7qcp6urqh7d2j' order by 1;                                          
  3.                                                                                        
  4. CHILD_NUMBER PREDICATE            RANGE_ID LOW        HIGH                             
  5. ------------ ------------------ ---------- ---------- ----------                       
  6.            1 
  7.            2 
  8.            3 

    3、v$sql_cs_histogram
        用于决定一个查询是否允许自适应游标共享，以直方图形式存储       

[sql] view plaincopyprint?
  1. SQL> select CHILD_NUMBER,BUCKET_ID,COUNT from v$sql_cs_histogram where SQL_ID='7qcp6urqh7d2j'     
  2.   2  order by 1;                                                                                  
  3.                                                                                                   
  4. CHILD_NUMBER  BUCKET_ID      COUNT                                                                
  5. ------------ ---------- ----------                                                                
  6.            0          1          1                                                                
  7.            0          0          1                                                                
  8.            0          2          0                                                                
  9.            1          1          0                                                                
  10.            1          0          1                                                                
  11.            1          2          0                                                                
  12.            2          1          0                                                                
  13.            2          0          1                                                                
  14.            2          2          0                                                                
  15.            3          1          0                                                                
  16.            3          0          1                                                                
  17.            3          2          0                                                                

三、总结
    1、自适应游标共享在SQL语句首次执行时(使用绑定变量)，进行窥探，并记录窥探结果，如果后续有相同的的SQL语句执行，则对窥探结果
       进行比较以判断是否需要生成新的执行计划。此即为绑定变量是否敏感。
    2、绑定变量的可知性用于判断当前的游标是否为可扩展性游标共享，当不可知时，则游标被废弃。
    3、自适应游标共享的实质是在Oracle 10g以前的基础上实现了多次绑定变量窥探，增加了获取最佳执行计划选择的机率。
    4、尽管使用自适应游标共享特性，但并不能保证每次执行SQL语句一定按最佳计划执行，本次演示中SQL语句的第二次执行并非最佳执行计划。
    5、自适应游标共享也不能保证两次执行相同SQL语句一定按相同的执行计划执行，因为自适应游标共享会不断尝试peeking。
               
四、延伸参考
    Oracle 绑定变量窥探
    Oracle自适应共享游标
    绑定变量及其优缺点 
    父游标、子游标及共享游标 
    dbms_xplan之display_cursor函数的使用 
    dbms_xplan之display函数的使用 
    执行计划中各字段各模块描述

-->>转载于:http://blog.csdn.net/leshami/article/details/6923670

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29119536/viewspace-1243966/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle 绑定变量窥探
下一篇： ORACLE 常见系统包及常用方法
请登录后发表评论 登录
全部评论
dawn009

注册时间：2013-08-15

  • 博文量
    595
  • 访问量
    4989219

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593