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。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏前端桃园

知识体系解决迷茫的你

最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

20940
来自专栏Ken的杂谈

【系统设置】CentOS 修改机器名

17930
来自专栏FSociety

SQL中GROUP BY用法示例

GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

5.1K20
来自专栏腾讯NEXT学位

今天我就说三句话

11520
来自专栏非著名程序员

「我真的没有改需求」

11910
来自专栏腾讯大讲堂的专栏

白底黑字or黑底白字,眼睛更喜欢哪一个?

12210
来自专栏web前端教室

你可以从面试中学到什么?

讲一下我对面试的一些。。。“偏见”,哈哈,熟悉我的同学们一定要批判的读接下来的内容哈。

12100
来自专栏haifeiWu与他朋友们的专栏

复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

28440
来自专栏华章科技

穿越十年后看互联网+:家电行业的金矿在哪里?

现在市场上炒得火热的智能家居未来出路在何方?做智能家居的创业者应该注意哪些机会?传统家电厂商又到底如何借助互联网进行转型?本文以智能空调为例,用故事的形式,提前...

8310
来自专栏非著名程序员

这是对付产品经理的一副毒药,程序员慎入

程序员和产品经理的日常就像是一对天生的冤家,为了需求的实现,几乎天天在争吵。这不,就在昨天各大技术和产品群里一个程序员暴打产品经理的视频火了,被广泛传播。

12320

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励