Oracle自适应共享游标

会计考友

　自适应游标共享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）；
　　SQL> exec dbms_stats.gather_table_stats（'SCOTT'，'T'，cascade=>true）；
7 v3 w. Y% l/ o8 H! O- @6 @　　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
6 f2 W$ d0 k1 m, t6 z! }7 Z# [       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
$ [; {4 C  S  u, w( t$ ]! A: X-------------------------------------
0 |+ r  m- T- I1 R) z* }* R　　select sum（object_id） from t where id变量值为9时，使用了正确的执行计划，且预估的行数也准确
　　Plan hash value： 4270555908
-------------------------------------------------------------------------------------
* o, v5 L9 r; a6 R| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time |
5 Y; W8 q% s. x! \-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |    |      |       |     3 (100)|       |
|   1 |  SORT AGGREGATE            |   |   1 |     8 |           |       |
1 Y: {( z1 x! d8 {% F|   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 |
, G1 f5 t7 O+ W( ~2 O-------------------------------------------------------------------------------------
; I1 q9 C% J2 ~
　　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
$ |* q0 e8 i, a------------- --------------------------------------------- ------------ ---------- - - -
　　7qcp6urqh7d2j select sum（object_id） from t where id exec ：v_id：=900；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
-------------------------------------
　　select sum（object_id） from t where id此次执行的变量值为900，执行计划位上次变量为9的执行计划——>此时为非正确的执行计划，等同于Oracle 9i，10g中的情形Plan hash value： 4270555908
-------------------------------------------------------------------------------------
　　| Id  | Operation                    | Name | Rows  | Bytes | Cost （%CPU）| Time     |
-------------------------------------------------------------------------------------
) w# E4 j! |4 R/ N7 V8 D-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |
2 X. o/ P  G- N3 I2 A) l|   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 |
1 l1 {0 g% h1 P- U% z-------------------------------------------------------------------------------------
7 N+ J7 A/ Q. e-------------------------------------------------------------------------------------
* H( ]. j8 g$ ^. C) x) ^　　——>自适应游标共享的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%'；
0 M: M* `* ?) A/ y4 i, ~9 ?　　SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I
------------- --------------------------------------------- ------------ ---------- - - -
　　7qcp6urqh7d2j select sum（object_id） from t where id select sum（object_id） from t where id再次执行变量为900值的SQL语句
　　SUM（OBJECT_ID）
% G- c1 |+ ~* [! W  O--------------
8 ?3 M: V, N$ s% p& J5 V2 V　　1826561
　　SQL> select * from table（dbms_xplan.display_cursor（null，null，'typical -predicate'））；
9 c# X$ }/ a+ B$ {& E! N( Z* l　　SQL_ID  7qcp6urqh7d2j， child number 1
; ^7 _( Z+ W* l+ ~7 C
-------------------------------------

会计考友

</p>　　select sum（object_id） from t where id此时执行计划较上一次发生了变化，使用了全表扫描，Rows接近于实际值——>自适应游标共享特性得以体现Plan hash value： 2966233522
* V6 D1 d( {0 J7 A' _! W& A; R---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
* {; J$ u  |  e6 Y0 j! G! h|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
8 c; X* x' E/ F6 c7 |4 u|   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%'；
$ V; c2 A- Z. oSQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I
+ D3 M$ O7 q! J4 h% F9 N' B------------- --------------------------------------------- ------------ ---------- - - -
7qcp6urqh7d2j select sum(object_id) from t where id为变量赋于不同的值
　　SQL> select sum（object_id） from t where id利用新的变量值执行SQL语句
2 \$ k& J9 e) Y/ r　　SUM（OBJECT_ID）
( Q' h6 h. {- q4 Y9 g1 Z; W3 e--------------
　　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%'；
) M' ~' u2 @, I7 h　　SQL_ID        CHILD_NUMBER EXECUTIONS I I I
8 {( d0 @! K' ?7 F------------- ------------ ---------- - - -
7qcp6urqh7d2j            0          2 Y N Y
8 o1 G  b8 k& Z* F& k7qcp6urqh7d2j            1          1 Y Y N
7 o0 C6 g0 B& r$ ^; q" \, W+ h3 f7qcp6urqh7d2j            2          1 Y Y Y    -->生成了新的子游标号为2
" Y0 v0 }! u& ?　　SQL> exec ：v_id：=500；     ——>为变量赋于新值
% i  G# Z7 `& d& _$ {　　SQL> select sum（object_id） from t where id利用新的变量值执行SQL语句
+ Q) y8 M/ Q  m2 q! y  Z# Q　　SUM（OBJECT_ID）
--------------
' Y' p" G2 V4 Q5 w　　826694
/*************************************************     */
1 h7 K% a& x2 [5 R, U- Y/* Author: Robinson Cheng                                                           */
* n; |# A( h3 P8 p8 v# L. {/* Blog:   http://blog.csdn.net/robinson_0612                               */
/* MSN:    robinson_0612@hotmail.com                                    */
$ E( k6 t$ ~1 p8 {  Z+ a0 r4 O/* QQ:     645746311                                                                  */
/**************************************************/
SQL_ID        CHILD_NUMBER EXECUTIONS I I I
. x  ?) i0 v3 A4 B0 f; _------------- ------------ ---------- - - -
1 V6 C9 i) S  t# Z+ G7qcp6urqh7d2j            0          2 Y N Y
' O5 R# i5 f( n' V. D0 D2 P7qcp6urqh7d2j            1          1 Y Y N
7qcp6urqh7d2j            2          1 Y Y N       -->注意看子游标1,2的is_shareable值为N，表示不可共享
7qcp6urqh7d2j            3          1 Y Y Y       -->生成了新的子游标号为3，
7 h0 |: v( m9 C6 P2 O% ~8 O& M5 e1 B-->查看最终该SQL语句的不同子游标的所有执行计划
SQL> select * from table(dbms_xplan.display_cursor('7qcp6urqh7d2j',null,'typical -predicate'));
PLAN_TABLE_OUTPUT
+ Z+ h; {4 y) M4 m6 P6 |8 e7 y8 b------------------------------------------------------------------------------------------------------------------
& D+ z" G  Y, T# w, ^3 u" ASQL_ID  7qcp6urqh7d2j, child number 0
2 ]' Z4 d; l* h7 X# ^5 E5 B-------------------------------------
" ]7 B! j8 a- r. E7 Q" v$ G) [4 c' aselect sum(object_id) from t where id0号子游标为索引范围扫描
Plan hash value: 4270555908
4 |8 F- e6 F/ S7 q& t" t-------------------------------------------------------------------------------------
1 d( K  x; z4 o" E0 H  U6 z& l| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
! z/ S. _* }! e% A: z|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |
+ t8 o1 a6 w6 C|   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |
1 N0 L+ G4 J! p8 a5 n-------------------------------------------------------------------------------------
SQL_ID  7qcp6urqh7d2j, child number 1
. g7 F8 R& l3 M$ @; q-------------------------------------
$ J2 G! k) @) \' w( uselect sum(object_id) from t where id1号子游标为全表扫描，其预估的行数接近实际影响行数的值为900
Plan hash value: 2966233522
6 \+ b8 O# x7 c+ z1 j3 N---------------------------------------------------------------------------
; O1 C- t) B8 @( d. J# o$ R| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
6 _( k: n) Q+ J  [: U---------------------------------------------------------------------------
5 U3 e2 l0 T/ R& z|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |
" r6 ]% D, I; i---------------------------------------------------------------------------
( \9 K/ O) O" ?6 a3 `2 z. CSQL_ID  7qcp6urqh7d2j, child number 2
-------------------------------------
select sum(object_id) from t where id2号子游标为全表扫描，但其预估的行数接近实际影响行数的值为800
4 F0 M% H; R; N+ V; M  ePlan hash value: 2966233522
5 t4 f( F& R2 u9 p( S( U; e---------------------------------------------------------------------------
6 H/ ]: n4 D; [3 c1 Q* q9 k+ ~| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
* [( D% q3 ]7 F/ q: r2 A, F) F---------------------------------------------------------------------------
7 F# _; n  a+ _3 s. t. V|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
8 ?9 q3 }* B6 c|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
& N# u0 Z- ^8 U, A|   2 |   TABLE ACCESS FULL| T    |   800 |  6400 |     3   (0)| 00:00:01 |
, a9 L. |; [3 W9 \$ Z( b---------------------------------------------------------------------------
SQL_ID  7qcp6urqh7d2j, child number 3
% \0 J% K+ M% X6 O3 L) |& x$ |9 a; {! Q-------------------------------------
select sum(object_id) from t where id3号子游标为全表扫描，但其预估的行数等于实际影响行数的值499
: a1 t1 k6 L3 M5 `Plan hash value: 2966233522
---------------------------------------------------------------------------
) s, |3 ?3 a) _| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
8 _/ B. T8 M4 l---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
& @; Z$ S3 ?% l|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   499 |  3992 |     3   (0)| 00:00:01 |
2 G! P5 e$ U/ R* y---------------------------------------------------------------------------
二、自适应游标共享的几个相关视图
2 T3 x! J  V4 z: [. A6 d* q1 f 1、v$sql_cs_statistics
2 R  Q" u' j# _# n        用于监控自适应游标共享的相关统计信息.下面的查询中列出了每个子游标的peeking情况,以及执行次数,预处理行数,BUFFER_GETS等
! ]8 Y5 X. o/ _$ n7 P4 c( ISQL> 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;
2 t& Z$ Z" K9 o$ eCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ ------------------- - ---------- -------------- -----------
5 U# f, z' H( P           0          1706589901 Y          1             17          69     -->17行，索引范围扫描
% d- I6 ~1 d$ {. Y! V9 T           1          3116944019 Y          1            900           5     -->900行，全表扫描
           2          1328865654 Y          1            800           5     -->800行，全表扫描
           3          1624350242 Y          1            500           5     -->500行，全表扫描
% V3 }- ~/ t4 z# r' {2 j    2、v$sql_cs_selectivity
        显示每个子游标的游标的选择性范围。下面的查询中列出了谓词，选择性范围，列上的选择性的值
SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity
; @- t9 z+ u3 }5 p* l; ?  2  where sql_id='7qcp6urqh7d2j' order by 1;
( Y) t7 ?  |5 K6 D  r
: E% ?' h% y7 Y5 s7 N5 oCHILD_NUMBER PREDICATE            RANGE_ID LOW        HIGH
------------ ------------------ ---------- ---------- ----------
" ]. ~) E; W* q# G* E. F2 w           1