设为首页收藏本站
开启辅助访问 切换到窄版

a我考网

 找回密码
 立即注册

QQ登录

只需一步，快速开始

扫一扫，访问微社区

我考网»我考网社区 › 计算机考试 › Oracle认证 › Oracle自适应共享游标
返回列表 发新帖
查看: 139|回复: 1

[考试辅导] Oracle自适应共享游标

[复制链接]
会计考友
发表于 2012-8-4 14:06:19 | 显示全部楼层 |阅读模式
　自适应游标共享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）；7 w, m1 x0 o/ W- p
　　SQL> exec dbms_stats.gather_table_stats（'SCOTT'，'T'，cascade=>true）；6 G% r8 E4 ?) U. e2 e. p6 l5 K
　　SQL> select count（id），count（distinct id），min（id），max（id） from t；
. Y% R( @5 i# S1 }5 }% g# D　　COUNT（ID） COUNT（DISTINCTID）    MIN（ID）    MAX（ID）& W$ g* Q% V& w0 |0 q# U: Z, H
---------- ----------------- ---------- ----------9 z+ n, n) f. I) f9 s4 K4 o
　　1000              1000          1       1000
8 P* `$ L+ _: j. \4 L- i3 |: G* f% L       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 f  l- T: Y: Q-------------------------------------
1 N  K# ]" X9 y" ~4 r　　select sum（object_id） from t where id变量值为9时，使用了正确的执行计划，且预估的行数也准确
; i4 b- }/ d2 w: z5 b4 f9 l3 k　　Plan hash value： 42705559086 I* ?7 d) M1 O* }- \
-------------------------------------------------------------------------------------
' A% j9 P0 P) [. [+ D| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time |
& i  `6 N$ W6 L; }-------------------------------------------------------------------------------------
; g% ~0 `) V1 g, T* N, u|   0 | SELECT STATEMENT         |    |      |       |     3 (100)|       |8 u( Q5 v4 ^9 r
|   1 |  SORT AGGREGATE            |   |   1 |     8 |           |       |& ^+ `, K7 C/ i6 J: e/ g
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |
1 e( w0 P( c6 d+ y|   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |7 p( ^- J. f3 d1 F4 b6 O2 E3 V, R; k
-------------------------------------------------------------------------------------
5 J7 K& A/ m* l% x( `- e2 x9 C2 ^+ a  Q0 w; d6 ^
　　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%'；
: \! ~$ m+ {" I) D' u4 U, [8 o0 _　　SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I3 T, S8 u6 N& N8 ~$ I' |! f
------------- --------------------------------------------- ------------ ---------- - - -
* a( I/ U  W0 D# \7 F8 v) g( d0 M9 f　　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'））；1 f7 r' |/ I: b) i
　　SQL_ID  7qcp6urqh7d2j， child number 01 q) ]+ C2 b9 `: Z; P
-------------------------------------
) U+ P# D3 B& P3 Z4 C. ^0 i' c" B7 `　　select sum（object_id） from t where id此次执行的变量值为900，执行计划位上次变量为9的执行计划——>此时为非正确的执行计划，等同于Oracle 9i，10g中的情形Plan hash value： 4270555908
  o+ d: k  w/ L: q+ w- Z9 R-------------------------------------------------------------------------------------
4 C$ @0 u; c& T+ ?, P　　| Id  | Operation                    | Name | Rows  | Bytes | Cost （%CPU）| Time     |# D, u6 |+ U8 g9 r3 U& o+ E5 j
-------------------------------------------------------------------------------------
2 \1 G- i' p1 H# _-------------------------------------------------------------------------------------; t, S4 u6 R+ k. s( \4 l
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |' M) {9 _4 q7 }; i, w+ t
-------------------------------------------------------------------------------------0 N3 N$ i  J: ]
|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |
7 x  J& a, k) a8 @& N|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |) ?3 b# ?( L3 R6 r% }# ]
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |
% P. r$ L+ D0 j) o6 J2 W|   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |
8 `* K) p$ k7 j' h* G-------------------------------------------------------------------------------------
3 [$ P5 G" L, h5 ?2 s' P1 N- ~-------------------------------------------------------------------------------------4 z2 ^, m5 M  Z& L5 f  p
　　——>自适应游标共享的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%'；* F2 e  ~. J. }/ ~* n
　　SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I, i1 \+ u2 o2 [( E1 w
------------- --------------------------------------------- ------------ ---------- - - -
; v3 R! i6 v% v: I　　7qcp6urqh7d2j select sum（object_id） from t where id select sum（object_id） from t where id再次执行变量为900值的SQL语句
- N8 Q3 f5 ^% U8 |/ k9 `4 @　　SUM（OBJECT_ID）1 j" d, o2 d: I9 I# P4 ^/ C! {+ l2 k
--------------
$ B1 P1 S4 w# B* w　　1826561  S; f) a/ Z# u$ B" ?
　　SQL> select * from table（dbms_xplan.display_cursor（null，null，'typical -predicate'））；
1 E& f, {4 K. }1 w8 N6 K　　SQL_ID  7qcp6urqh7d2j， child number 17 x( ?* z- J$ C  q3 \( M2 B* v
0 U0 m, d2 V/ s+ }  k0 Z
-------------------------------------
回复

使用道具 举报

会计考友
 楼主| 发表于 2012-8-4 14:06:20 | 显示全部楼层

Oracle自适应共享游标

</p>　　select sum（object_id） from t where id此时执行计划较上一次发生了变化，使用了全表扫描，Rows接近于实际值——>自适应游标共享特性得以体现Plan hash value： 29662335223 z* q6 u* ?" f3 f
---------------------------------------------------------------------------
1 y0 O7 d5 P4 L6 b' v' }- T& j| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |4 N9 c: i7 U. `0 j! L0 T8 t
---------------------------------------------------------------------------
: ~# G! ?6 {7 n$ E2 l5 T. m|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
9 {5 p/ j  k5 U7 Y3 d|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
1 r3 q/ N* |% A- S& e: X|   2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |
9 L9 N: {. ~" F* Y& R& E---------------------------------------------------------------------------- J6 S" r& T4 W' l4 d8 n
　　——>自适应游标共享特性的几个值发生了变化，生成了新的子游标，其子游标号为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%'；, ~; E# c  Y7 E6 M
SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I/ }& `6 m: |2 Y4 [
------------- --------------------------------------------- ------------ ---------- - - -
- m9 h. x- ?& D# _7qcp6urqh7d2j select sum(object_id) from t where id为变量赋于不同的值
5 D3 ~( o7 C' F5 ^+ O/ k9 i) ?  Z　　SQL> select sum（object_id） from t where id利用新的变量值执行SQL语句8 _7 {# Y* w6 E/ k. j1 {$ }
　　SUM（OBJECT_ID）
$ _; E& H3 j3 {. p2 E& N--------------
; n3 I+ l% O* F* S2 x  _' _$ Q. E　　1548431
1 p6 @* a* j7 f% v; r　　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%'；- Z" j7 Z3 C& D( M4 E
　　SQL_ID        CHILD_NUMBER EXECUTIONS I I I
% \6 |7 b8 p8 \8 f  W1 U------------- ------------ ---------- - - -
! W' _3 q  u. N/ d+ E; A) X: Z7qcp6urqh7d2j            0          2 Y N Y
% a% L+ W* o5 k& I4 b: S7qcp6urqh7d2j            1          1 Y Y N) s0 q  k1 M8 _6 c& s
7qcp6urqh7d2j            2          1 Y Y Y    -->生成了新的子游标号为2+ U& q/ n. I3 s( c7 g8 f
　　SQL> exec ：v_id：=500；     ——>为变量赋于新值
$ [  l1 @) X" l  y8 _　　SQL> select sum（object_id） from t where id利用新的变量值执行SQL语句2 N& i# y! [( y) f' B
　　SUM（OBJECT_ID）
' O, ?! A3 _; B5 h--------------" h! [2 ]( k$ G5 F+ N) ]
　　826694
5 S6 i$ o0 x& }2 R7 P/*************************************************     */7 o4 [$ b9 {3 `7 Q
/* Author: Robinson Cheng                                                           */
, h& j$ P7 |. \7 n/* Blog:   http://blog.csdn.net/robinson_0612                               */3 K3 |2 _$ `3 w* i" G3 Y
/* MSN:    robinson_0612@hotmail.com                                    */" t# T" y4 r9 q
/* QQ:     645746311                                                                  */% ^3 h$ `% `' _* V9 W
/**************************************************/
0 X) m/ C+ s3 o( @; {SQL_ID        CHILD_NUMBER EXECUTIONS I I I4 Y. f9 Y1 r" Z) T( V' u: ^& W
------------- ------------ ---------- - - -* b4 C7 k" P' w0 |7 ^  X
7qcp6urqh7d2j            0          2 Y N Y( d, T2 O7 O/ E5 Q+ {
7qcp6urqh7d2j            1          1 Y Y N
/ x) R) r2 G8 J6 ]! ^7qcp6urqh7d2j            2          1 Y Y N       -->注意看子游标1,2的is_shareable值为N，表示不可共享# |8 d  e. |3 a' ]' H
7qcp6urqh7d2j            3          1 Y Y Y       -->生成了新的子游标号为3，* ?1 n- y: ]5 `4 Q4 ^
-->查看最终该SQL语句的不同子游标的所有执行计划3 w- N# a* k3 h- C: M, Y
SQL> select * from table(dbms_xplan.display_cursor('7qcp6urqh7d2j',null,'typical -predicate'));
3 j5 b5 W3 y' ]# m6 _+ {  W* X. APLAN_TABLE_OUTPUT
7 U9 W* x4 X8 B8 p# h) y: d2 r------------------------------------------------------------------------------------------------------------------. G2 _) m6 z3 n( J3 R
SQL_ID  7qcp6urqh7d2j, child number 00 P8 v4 k" @3 x3 E2 c- U+ h& f
-------------------------------------; x( C2 N) I* Z: U+ o$ o
select sum(object_id) from t where id0号子游标为索引范围扫描" @; |, h/ W7 w7 `+ z3 L
Plan hash value: 4270555908
' |2 W* [, l) Z9 Q7 L8 @! V& U-------------------------------------------------------------------------------------
% d0 k- z6 ^' z+ h| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |& u( N4 m* R' l/ o  k
-------------------------------------------------------------------------------------
9 C  Z9 J  n5 [, b! h# _8 ~|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |+ F% E9 m# [. n; M
|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |6 f! i4 r4 }1 s( u$ M) E% W
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |8 b4 x& T& a0 I# k; V  W4 k
|   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |9 `" e& |. a% [* y  |3 X' T9 d* T+ n
-------------------------------------------------------------------------------------
6 [0 s! u% l5 d( v6 k6 ?; {8 a  p9 NSQL_ID  7qcp6urqh7d2j, child number 1
: [. P5 E7 T1 @3 l1 }$ s4 @-------------------------------------
  M" ?- Q. f- w+ pselect sum(object_id) from t where id1号子游标为全表扫描，其预估的行数接近实际影响行数的值为9002 T$ ?! P. c1 f) G* w/ L
Plan hash value: 2966233522
% p' o" R; f+ L" V2 y1 z" p---------------------------------------------------------------------------, ~4 {3 m; E  D% u" D5 {0 L
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
7 c* |- o8 K  L  I8 t---------------------------------------------------------------------------) r' j: `% ?+ q9 |3 d
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |# _) R) U/ L% N- Z; Q6 l7 b6 A
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |. t* ^2 o" m3 s* g* z' ]
|   2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |! H9 J8 \! W. k# L/ H' d- n/ V+ j
---------------------------------------------------------------------------
7 V) l- F  T! H; \  P' YSQL_ID  7qcp6urqh7d2j, child number 2
7 j5 O" p  \5 ~( `2 J* D7 Z-------------------------------------+ u' M* u- G6 E" ]  v0 A& W
select sum(object_id) from t where id2号子游标为全表扫描，但其预估的行数接近实际影响行数的值为8005 a& ?& n+ w: \; D  T9 q& c3 E/ l
Plan hash value: 2966233522
4 p( t, D  d8 ~0 `8 C---------------------------------------------------------------------------
/ Y" c: a# p& b% A| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |0 A; |, f2 P$ N. V, C# z; c
---------------------------------------------------------------------------, s: H' z0 F9 L/ M
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
$ {4 L* D/ n; L0 l|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |: X% C0 g+ Q5 u: k1 [  ?8 w
|   2 |   TABLE ACCESS FULL| T    |   800 |  6400 |     3   (0)| 00:00:01 |4 p6 Q2 W. m! r+ }. K0 f% m# p; _
---------------------------------------------------------------------------; Y5 }" L; m: o
SQL_ID  7qcp6urqh7d2j, child number 3* n+ n! l' A# ?
-------------------------------------
5 o% ^+ o/ B$ h* b9 ]) \/ Iselect sum(object_id) from t where id3号子游标为全表扫描，但其预估的行数等于实际影响行数的值499
, c: c0 M3 @5 D8 O* C# @- s5 Y0 VPlan hash value: 2966233522. y  R% v/ N* H% B
---------------------------------------------------------------------------
5 {3 q" o% Q5 {$ p3 M( o, F| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
9 N# a, Y% L6 v) T5 J0 N/ R---------------------------------------------------------------------------; ~- _/ x- C/ o
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
3 `9 u5 m- E, L- c& _|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |+ ~3 Z4 U* c3 ~* ]/ e) K1 t
|   2 |   TABLE ACCESS FULL| T    |   499 |  3992 |     3   (0)| 00:00:01 |
6 K8 U1 B; v! L& R0 e) V---------------------------------------------------------------------------& g: M2 e5 S$ X3 j: D
二、自适应游标共享的几个相关视图
' B3 b7 J6 v4 B- Y/ K* o 1、v$sql_cs_statistics/ }1 W$ o- d" O, |  V6 |, ]4 m' E
        用于监控自适应游标共享的相关统计信息.下面的查询中列出了每个子游标的peeking情况,以及执行次数,预处理行数,BUFFER_GETS等
: N8 r; w- q. Y: @9 GSQL> select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets
7 J4 z- N3 V2 b; f  2  from v$sql_cs_statistics where sql_id='7qcp6urqh7d2j'' K0 c8 m4 ?" j: n! }) E/ F. j0 b: s6 G
  3  order by 1;% b, C+ m# }/ {  ^8 e: j) i0 R
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
1 B- Z/ @+ h) q------------ ------------------- - ---------- -------------- -----------
& @0 ]* |: F/ S8 Y+ q           0          1706589901 Y          1             17          69     -->17行，索引范围扫描8 M% p. {0 [( C& {. [% m3 ^
           1          3116944019 Y          1            900           5     -->900行，全表扫描: s* B+ \8 h6 O5 c8 q4 V
           2          1328865654 Y          1            800           5     -->800行，全表扫描4 [8 f: u. W+ d
           3          1624350242 Y          1            500           5     -->500行，全表扫描" y- W0 a8 ]- s
    2、v$sql_cs_selectivity
, d" z6 G, x$ a4 W" B        显示每个子游标的游标的选择性范围。下面的查询中列出了谓词，选择性范围，列上的选择性的值% _3 n2 Z. w, ~) W
SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity' ]& W* \8 w+ N' r. N; t
  2  where sql_id='7qcp6urqh7d2j' order by 1;
4 s( z4 x9 z7 w3 b" l& l) F- r' D+ R# m# r8 k# Y
CHILD_NUMBER PREDICATE            RANGE_ID LOW        HIGH$ q- K- A, r  J- {' ^  K5 a
------------ ------------------ ---------- ---------- ----------% ]6 _+ K/ J9 S
           1
回复 支持 反对

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|Woexam.Com ( 湘ICP备18023104号 )

GMT+8, 2024-9-28 06:37 , Processed in 0.301673 second(s), 24 queries .

Powered by Discuz! X3.4 Licensed

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表