Oracle自适应共享游标

Oracle自适应共享游标

自适应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g的新特性之一，主要用于解决以前版本中由于绑定变量窥探导致SQL语句无法获得最佳执行计划的缺陷，即能够对效率低下的游标(子游标)进行自动识别而选择最佳的执行计划。本文详细描述了自适应游标共享并给出示例。

    有关绑定变量窥探请参考：Oracle 绑定变量窥探

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

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语句并获得首次执行情况

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个字段www.bkjia.com值                                          
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