Oracle综合辅导何时使用绑定变量性能反而差

会计考友

扫描成本和OPTIMIZER_INDEX_COST_ADJ
　　我们知道，在CBO模式下，Oracle会计算各个访问路径的代价，采用最小代价的访问路径作为语句的执行计划。而对于索引的访问代价的计算，需要根据一个系统参数OPTIMIZER_INDEX_COST_ADJ来转换为与全表扫描代价等价的一个值。这是什么意思呢?我们先稍微解释一下这个参数：OPTIMIZER_INDEX_COST_ADJ。它的值是一个百分比，默认是100，取值范围是1～10000。当估算索引扫描代价时，会将索引的原始代价值乘以这个百分比，将换算后的值作为与全表扫描代价比较的值。也就是说，当这个值为100时，计算出的索引扫描代价就是它的原始代价： COST_COM = COST_ORG * OPTIMIZER_INDEX_COST_ADJ/100
　　看以下例子：
2 L2 h+ N$ v/ @4 N4 w/ i% h以下是引用片段：
　　SQL> create table T_PEEKING (a NUMBER, b char(1), c char(2000));
　　Table created.
　　SQL>
7 M7 \" ~- ?. T7 {6 @) f0 k　　SQL> create index T_PEEKING_IDX1 on T_PEEKING(b);
　　Index created.
　　SQL> begin
　　2 for i in 1..1000 loop
　　3 insert into T_PEEKING values (i, 'A', i);
( j! D" n# F6 Y) U1 A' H* ^& X　　4 end loop;
# w. w' s) V7 ]/ ~+ h5 ?/ E　　5
　　6 insert into T_PEEKING values (1001, 'B', 1001);
　　7 insert into T_PEEKING values (1002, 'B', 1002);
　　8 insert into T_PEEKING values (1003, 'C', 1003);
　　9
　　10 commit;
　　11 end;
　　12 /
　　PL/SQL PRocedure successfully completed.

　　注意，我们给索引字段B插入的值中只有3个distinct值，记录数是1003，它的集的势很高(1003/3)=334。
以下是引用片段：
　　SQL>
　　SQL> analyze table T_PEEKING compute
　　statistics for table for all indexes for all indexed columns;
# `& h; U# j" Q* n- Z　　Table analyzed.
　　SQL>

会计考友

　　我们看下索引扫描的代价是多少： SQL> show parameter OPTIMIZER_INDEX_COST_ADJ</p>以下是引用片段：
　　NAME TYPE VALUE
　　------------------------------------ ----------- ------
　　optimizer_index_cost_adj integer 100
4 C5 P5 m$ f" Y  j  [+ [　　SQL> delete from plan_table;
+ w0 n- v; ^; k) A8 N0 [% i' x: q0 U　　0 rows deleted.
　　SQL>
& j* x) F1 @6 p& Z　　SQL> explain plan for select
　　/*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V;
3 l% H" a5 }" `. ]4 B+ N. z6 k　　Explained.
　　SQL> select lpad(' ', 2*(level-1))||Operation||' '||options||' '||
: ~$ A7 l6 L( x　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
　　3 Plan_Table"
# m: ?# q; C! ~9 P$ x5 V　　4 from plan_table
" K3 w) u& O" i' E0 k9 Q! O3 x$ P　　5 start with id = 0
/ X5 o7 V: l4 N6 B　　6 connect by prior id = parent_id
　　7 ;
! g2 R, F' M( ~6 w- ~: _" Y　　Query
　　Plan_Table
# B8 y/ g+ C; T5 k　　-----------------------------------------------------
4 b0 g( |9 E  v+ _9 Z  y　　SELECT STATEMENT Cost=113
& I6 s) p3 q- A  ~+ P　　TABLE access BY INDEX ROWID T_PEEKING
6 {- E5 H9 w# }1 q3 X- I　　INDEX RANGE SCAN T_PEEKING_IDX1
& b4 r6 f. W6 k! N1 O: i　　SQL>
( p- m  z; y+ F- q
. @3 S$ \3 E0 T  i. ?. M0 u　　再看全表扫描的代价是多少： 以下是引用片段：
      SQL> delete from plan_table;
　　3 rows deleted.
　　SQL>
$ f! ?: o+ o8 {$ D' h! Z" O; C# f　　SQL> explain plan for select
　　/*+full(a)*/ * from T_PEEKING a where b = :V;
- [3 h: Y* @( b& }! J/ `* r- }　　Explained.
2 s9 `9 J4 T( M' a7 o　　SQL>
　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
, _# }$ t" q) _; H5 n　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
　　3 Plan_Table"
. Y4 b0 v& h. P5 X% U/ s% V　　4 from plan_table
　　5 start with id = 0
2 ^. G8 ^+ x2 J; E/ R9 U7 f　　6 connect by prior id = parent_id
4 I5 F. v3 M6 t( w7 y; h) k* {2 `' Y& ?　　7 ;
　　Query
0 Z% X! D2 c. x; T　　Plan_Table
- G& G) O0 @+ m8 A　　----------------------------------------------------
　　SELECT STATEMENT Cost=75
! ~* g* R7 \3 U  B. `　　TABLE ACCESS FULL T_PEEKING
　　SQL>

2 a- S1 a, e+ f0 C8 G! Y  s" @" h　　这时，我们可以计算得出让优化器使用索引(无提示强制)的OPTIMIZER_INDEX_COST_ADJ值应该< ROUND(COST_FTS/COST_IDX*100) = ROUND(75/113*100) = 66，而大于66则会使用全表扫描： SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=67;

会计考友

</p>以下是引用片段：
5 o3 Q; ^, S* O- f+ _3 X　　System altered.
　　SQL>
　　SQL> delete from plan_table;
　　2 rows deleted.
　　SQL>
　　SQL> explain plan for select * from T_PEEKING a where b = :V;
　　Explained.
# _& r/ f, A+ Q; o# ^+ C( m　　SQL>
8 M$ ?. R. n$ r6 M& U" ?: t　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
" Z6 y2 D1 t  Z" `* s( _　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
7 D" V" V9 [7 R' z! `　　3 Plan_Table"
　　4 from plan_table
　　5 start with id = 0
6 V, V' @( v' V- j7 u4 n: |- a6 [　　6 connect by prior id = parent_id;
$ W# l% @- M0 L! `4 K# I) y8 o& n　　Query
　　Plan_Table
8 t4 ?* Z* i0 f; J0 F' R　　-----------------------------------------------------------------
) a% T) s0 |, o5 B/ u- m　　SELECT STATEMENT Cost=75
( a' T* k" ^, S, W3 Z　　TABLE ACCESS FULL T_PEEKING
: |6 _1 I2 x% \/ B3 {, b6 q　　SQL>
  L7 X6 `' ?6 w5 {4 i8 X　　SQL>
　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66;
% o6 K! a+ \! |　　System altered.
　　SQL>
+ I# T" s" |- H　　SQL> delete from plan_table;
* ?! e; R+ C9 O& r, q. R0 x　　2 rows deleted.
　　SQL>
  N( f2 i6 T, w* |* Q5 V9 A# a% K　　SQL> explain plan for select * from T_PEEKING a where b = :V;
& j) t" i5 S8 ^! H2 Q7 Q$ a　　Explained.
/ T# E- `% v) z+ P& a0 v6 D" v　　SQL>
  K8 \# Y2 a; B; j　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
+ R* b4 g/ D$ I  D　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
& B" U& [4 X. f  r: w( J5 ]　　3 Plan_Table"
6 @! r/ K) J! F- e% `  L0 a+ u　　4 from plan_table
　　5 start with id = 0
　　6 connect by prior id = parent_id;
　　Query
　　Plan_Table
9 v4 a# G9 E- J; ]# s5 U: [2 `　　---------------------------------------------------------
　　SELECT STATEMENT Cost=75
9 k0 d% C' k7 z, P3 Y' z# x+ [　　TABLE ACCESS BY INDEX ROWID T_PEEKING
! K( m5 n- n! t6 r0 f% F　　INDEX RANGE SCAN T_PEEKING_IDX1
6 G$ Q6 N2 O6 m/ d4 m
　　可以看出，在使用绑定变量时，参数OPTIMIZER_INDEX_COST_ADJ对于是否选择索引会有重要的影响。
　　这里我们暂且不讨论索引扫描的原始成本是如何计算得出的。但是有一点很重要，在使用绑定变量时，计算出的成本是平均成本。在我们上面的例子中，字段B的值只有3个："A"、"B"、"C"，其中A最多，1003行中有1000行。因此，在索引上扫描值为A记录的成本为1000/1003 * 索引全扫描成本 ≈索引全扫描成本，我们看下它的成本是多少：

, z4 E. C2 i7 R( b7 u, g9 X以下是引用片段：
　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
2 M) r  B3 G; X- d: n8 C　　System altered.
/ x% I" }' @+ D+ ]5 A* N# o% d+ u　　SQL>
　　SQL> delete from plan_table;
　　2 rows deleted.
　　SQL>
　　SQL> explain plan for select
! X( E8 C' F8 [　　/*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'A';
1 _/ n, B' g) a! s  y7 W) L7 S　　Explained.
  T. R7 f, c2 U3 I& D2 A　　SQL>
　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
　　3 Plan_Table"
　　4 from plan_table
2 [* Y5 M) _: l/ o　　5 start with id = 0
8 q' ?- o9 i, E. H　　6 connect by prior id = parent_id;
0 g+ ~1 ?+ f1 S; s" e　　Query
( R; m8 m" W( N$ J6 ^8 E　　Plan_Table

会计考友

　　--------------------------------------------------------------
　　SELECT STATEMENT Cost=336
; M! o- w) e2 R3 e( t! e  q　　TABLE ACCESS BY INDEX ROWID T_PEEKING
( X- _) w0 J; p8 c- B　　INDEX RANGE SCAN T_PEEKING_IDX1 </p>
' V& `, n- e+ p( o. C　　可以看到，它的成本是336。因此索引的平均成本是(336 * 1003/1000) / 3 ≈ 113，也就是使用绑定变量使的成本。而扫描其它两个值"B"和"A"时代价就非常小。 以下是引用片段：
+ J) F) T( ^) N, _9 |      SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
9 O$ e) A* t( Q1 z5 `: f　　System altered.
: X0 y$ ?6 [: {* Y  q$ ^　　SQL>
0 V: I: t! K9 z; r6 k* y　　SQL> delete from plan_table;
　　3 rows deleted.
　　SQL>
; v2 L+ }  E9 z　　SQL> explain plan for select
2 s6 g" x7 `) z% |( F　　/*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'B';
　　Explained.
  v! {. B5 {) y* @1 i- c! z　　SQL>
　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
, u" Z2 T7 Y& o( h　　3 Plan_Table"
　　4 from plan_table
　　5 start with id = 0
　　6 connect by prior id = parent_id;
% n- V. v8 E! U　　Query
　　Plan_Table
: C( i! N5 h/ ], i* u7 v　　---------------------------------------------------------------
　　SELECT STATEMENT Cost=2
  T$ l: V& K* l* k- z　　TABLE ACCESS BY INDEX ROWID T_PEEKING
　　INDEX RANGE SCAN T_PEEKING_IDX1
; [+ Y1 k: v, |; \$ k/ j4 X( o　　因为计算的成本是平均成本(相对实际扫描某个值的成本，平均成本更接近全表扫描成本)，因此在创建查询计划时，使用绑定变量将更加容易受到参数OPTIMIZER_INDEX_COST_ADJ影响，特别是上面的这种情况(即索引字段的集的势非常高时)下，平均代价与实际扫描某个值代价相差非常远。这种情况下，OPTIMIZER_INDEX_COST_ADJ对不使用绑定变量查询影响就非常小(因为索引代价不是比全表扫描成本大很多就是小很多)，不管扫描哪个值，不使用绑定变量将更加容易选择到合理的查询计划。
　　绑定变量窥视
　　在了解了参数OPTIMIZER_INDEX_COST_ADJ的作用后。再了解一个对查询计划，特别是使用绑定变量时会产生重大影响的特性：绑定变量窥视(Bind Variables Peeking)。
　　绑定变量窥视是9i以后的一个新特性。它使CBO优化器在计算访问代价时，将绑定变量传入的值考虑进去，从而计算出更合理的成本(否则，将会计算平均成本)。看下面例子： 以下是引用片段：
      SQL> conn sys/sys as sysdba
　　Connected.
) U& I* a3 z  E　　SQL>
% o$ j8 n" V8 n$ K　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60;
　　System altered.
　　SQL> analyze table T_PEEKING compute
$ Z$ X9 h1 o) \$ ?$ I- ?9 e. c- W+ w　　statistics for table for all indexes for all indexed columns;
　　Table analyzed.
4 p5 g. M# H' r2 ?　　SQL>
) a) L" O  q0 M$ ?　　SQL> set autot trace
) ^4 j* [6 ~. T. R( ^　　SQL>
　　SQL> alter session set sql_trace = true;
! G4 E, u' D. {& u0 ^　　Session altered.
　　SQL>
　　SQL> var v char(1)
% X% Y8 Y; ]9 O  Q7 A7 m) P　　SQL>
　　SQL> exec :v := 'A';
8 ]2 G, G# [. Q# _6 z  }- c　　PL/SQL procedure successfully completed.
, F5 D- e8 ]6 a+ n　　SQL>
7 x3 ]+ S2 |8 y& q4 c  u( u　　SQL> select * from T_PEEKING a where b = :V;
　　1000 rows selected.
0 A4 }) L) Q, G: G* l# B　　SQL>
1 g2 Z3 x5 q& N6 ?# i　　SQL> alter session set sql_trace = false;
　　Session altered.
! s' Y/ P, d) Y0 b8 k. C
! F: w; W9 G$ Z0 E3 w5 \9 y　　用Tkprof处理生成的trace文件。因为在存在绑定变量窥视时，autotrace或者explain plan可能不会显示正确的查询计划，需要Tkprof来处理sql trace。 tkprof fuyuncat_ora_5352.trc aaa.txt
　　此时OPTIMIZER_INDEX_COST_ADJ是60，根据上面的结论，似乎查询计划应该选择扫描索引。但是，这里给绑定变量赋了值"A"，这时，优化器会“窥视”到这个值，并且在计算扫描成本时按照这个值的成本来计算。因此，得出的查询计划是全表扫描，而不是扫描索引，靠Tkprof分析的结果： 以下是引用片段：
      select * from T_PEEKING a where b = :V
　　call count cpu elapsed disk query current rows
　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
9 H( z* s0 B: ]　　Parse 1 0.00 0.00 0 0 0 0
　　Execute 1 0.00 0.00 0 0 0 0
6 G7 @$ J. l8 [3 J) i+ G2 i8 g, {　　Fetch 68 0.01 0.07 0 406 0 1000
　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
0 `& E! t, o( N  d( B　　total 70 0.01 0.08 0 406 0 1000
　　Misses in library cache during parse: 1
# \$ ?# \" ^1 O　　Optimizer mode: CHOOSE
4 E" @( n/ J8 G; p- @/ _2 v' O& N　　Parsing user id: SYS
　　Rows Row Source Operation
$ ?8 y: }, K! h% y　　------- ---------------------------------------------------
　　1000 TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)

会计考友

　　但是，绑定变量窥视对一条语句只会使用一次。就是说，在第一次解析语句时，将绑定变量值考虑进去计算成本生成查询计划。以后在执行该语句时都采用这个查询计划，而不再考虑以后绑定变量的值是什么了。 SQL> conn sys/sys as sysdba</p>以下是引用片段：
　　Connected.
! n3 H! i8 ?( {/ R+ l. W　　SQL>
　　SQL>
" c% L$ `0 o; k　　SQL> set autot trace
　　SQL>
　　SQL> alter session set sql_trace = true;
　　Session altered.
　　SQL>
　　SQL> var v char(1)
* a* \& F5 X  C. Z8 b; E　　SQL>
/ }. }0 C  N5 S( T4 Q' M3 `　　SQL> exec :v := 'B';
　　PL/SQL procedure successfully completed.
2 t( x; Q" n- C: I　　SQL>
' |: X6 u" F9 S0 s) s- _9 M7 z5 u　　SQL> select * from T_PEEKING a where b = :V;
7 D: Y; |2 X& I- k0 Q0 ]1 h6 M1 P$ u　　1000 rows selected.
$ J3 l0 R' _( X! r　　SQL>
　　SQL> alter session set sql_trace = false;
4 u" @' u9 u' [% a* G+ {　　Session altered.
( h  O3 e3 [) {2 G# h" f
　　再用Tkprof分析生成的trace文件，看到尽管这里的值是"B"，选择索引扫描会更优，但分析结果中查询计划还是使用全表扫描： select *
0 @! U' Q) w2 h& s( d2 }  D8 l: E以下是引用片段：
# m9 q$ s! C5 m* A. N　　from
　　T_PEEKING a where b = :V
* m0 ^0 ]0 m4 Z0 s5 ]　　call count cpu elapsed disk query current rows
　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
$ E: R: N# \! A. d" ?" ~　　Parse 1 0.00 0.00 0 0 0 0
) b  o( T0 _% U7 J　　Execute 1 0.00 0.00 0 0 0 0
3 y; j- ?' K1 i( i　　Fetch 2 0.00 0.00 0 340 0 2
0 G+ |$ p7 ^: s; Z　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
　　total 4 0.00 0.00 0 340 0 2
9 H) J7 V) i; l# `) o& z　　Misses in library cache during parse: 0
　　Optimizer mode: CHOOSE
　　Parsing user id: SYS
　　Rows Row Source Operation
: ^, F3 r9 d6 w+ T! X! u　　------- ---------------------------------------------------
　　2 TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)
2 {" t3 p$ O8 X& N6 `0 J$ c* x! }* _
　　因此，这种情况下使用绑定变量也会导致无法选择最优的查询计划。
" X/ R0 w. c* d' W　　综上所述，我们可以得出一个结论：在对建有索引的字段(包括字段集)，且字段(集)的集的势非常大时，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。