自顺应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g的新特征之一,首要用于解决以前版本中因为绑定变量窥探导致SQL语句无法获得最佳执行打算的缺陷,即能够对效率低下的游标(子游标)进行自动识别而选择最佳的执行打算。本文具体描述了自顺应游标共享并给出示例。 有关绑定变量窥探请参考:Oracle 绑定变量窥探一、示例自顺应游标共享1、建树演示情形view plain SQL> select * from v$version where rownum create table t(id,owner,object_id) as 2 select rownum,owner,object_id from all_objects where rownum alter table t add constraint t_pk primary key(id);
+ B- q% H" v# t _' Q! p7 q SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);2 B% T8 p( |" P/ O$ [" L* o$ F
SQL> select count(id),count(distinct id),min(id),max(id) from t;: K ?4 J/ Z# r! S
COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
3 X& r% X5 ~# h% k, b$ J---------- ----------------- ---------- ----------* n) A& h5 T9 J) ^
1000 1000 1 1000 5 u8 E0 ]1 s! ^
2、使用绑定变量执行SQL语句并获得初度执行情形view plain SQL> var v_id number;SQL> exec :v_id:=9;SQL> set linesize 180 SQL> select sum(object_id) from t where id select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));SQL_ID 7qcp6urqh7d2j, child number 0, R1 i$ g, H& j' {
-------------------------------------
) \ _$ j' N# {: p select sum(object_id) from t where id变量值为9时,使用了正确的执行打算,且预估的行数也切确
' p8 S) X1 R0 m' S! T( { Plan hash value: 4270555908; e2 z( D1 y( @7 }
-------------------------------------------------------------------------------------
- a$ {% z( u1 K* x& w( y* w| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
! X; Q8 o, Z$ t( M- x-------------------------------------------------------------------------------------
& `& D. g5 D3 Q! E5 x* W| 0 | SELECT STATEMENT | | | | 3 (100)| |( p8 R! M: M4 c, s' D9 G& X! D
| 1 | SORT AGGREGATE | | 1 | 8 | | |
& {* ~- H) G4 R| 2 | TABLE ACCESS BY INDEX ROWID| T | 8 | 64 | 3 (0)| 00:00:01 |
, K9 G4 m8 i: }" L: T: ?9 p, @. V* D, j| 3 | INDEX RANGE SCAN | T_PK | 8 | | 2 (0)| 00:00:01 |
5 p; C( D. C+ E- n" S) A-------------------------------------------------------------------------------------- t" k5 X; G& S) h" w1 W
4 O6 N6 L0 Q& }& O- o. m# C) ]- C 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%';: c' K' B8 Z* H O" F T6 F
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I% v5 d5 t5 ^6 D3 U* n$ n' a2 _5 h
------------- --------------------------------------------- ------------ ---------- - - -0 ^* c. f* v& b( c
$ i/ O! n/ g. l
7qcp6urqh7d2j select sum(object_id) from t where id |