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
　　看以下例子：
以下是引用片段：
4 s# g, X' `& D　　SQL> create table T_PEEKING (a NUMBER, b char(1), c char(2000));
　　Table created.
　　SQL>
9 X; Q9 s4 M  H! E　　SQL> create index T_PEEKING_IDX1 on T_PEEKING(b);
　　Index created.
　　SQL> begin
$ l8 _* v- b: B3 D& I2 K7 y0 c5 E* b　　2 for i in 1..1000 loop
　　3 insert into T_PEEKING values (i, 'A', i);
　　4 end loop;
/ Q* f) W4 @& X  ^1 v　　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
8 c& j$ i  D- \. a2 s0 A. o　　10 commit;
　　11 end;
- a3 u- \4 H8 }9 S3 c$ f* e6 |% \　　12 /
　　PL/SQL PRocedure successfully completed.
. ~) E9 q, C' M8 Z
7 _3 g1 \. ]8 k$ o2 }: m1 X7 Z& F" C　　注意，我们给索引字段B插入的值中只有3个distinct值，记录数是1003，它的集的势很高(1003/3)=334。
9 r3 L$ u3 G/ ~; n: `! M9 V以下是引用片段：
9 T" v+ S7 Y5 @+ S/ s4 C& q8 ^　　SQL>
　　SQL> analyze table T_PEEKING compute
) F' g$ P  f/ ]8 i7 P0 E* R　　statistics for table for all indexes for all indexed columns;
: c1 k; }* M' \' L0 c　　Table analyzed.
　　SQL>

会计考友

　　我们看下索引扫描的代价是多少： SQL> show parameter OPTIMIZER_INDEX_COST_ADJ</p>以下是引用片段：
　　NAME TYPE VALUE
, u9 N, [* e7 v( u% |: ~* @　　------------------------------------ ----------- ------
6 b' s2 @+ F- J% `- b　　optimizer_index_cost_adj integer 100
　　SQL> delete from plan_table;
　　0 rows deleted.
　　SQL>
: a7 l: p3 N5 d2 i1 C: ?　　SQL> explain plan for select
/ l; Z1 e- ?! X5 K# [　　/*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V;
& H% r# i0 O7 n; k/ Z8 K+ h9 ~　　Explained.
　　SQL> select lpad(' ', 2*(level-1))||Operation||' '||options||' '||
& l; W7 _& Q6 M　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
　　3 Plan_Table"
3 J0 F- Q$ {" D! b, [* |) v　　4 from plan_table
　　5 start with id = 0
　　6 connect by prior id = parent_id
! B+ |" @& ]5 P4 u　　7 ;
　　Query
　　Plan_Table
5 E  J2 t. z+ A6 f/ n* w( ~　　-----------------------------------------------------
　　SELECT STATEMENT Cost=113
　　TABLE access BY INDEX ROWID T_PEEKING
8 n: i8 \5 d2 Q8 {' O　　INDEX RANGE SCAN T_PEEKING_IDX1
0 Y$ j0 N2 Y2 G/ I　　SQL>

1 \# w$ i: {) Z1 x  _7 B: G' a　　再看全表扫描的代价是多少： 以下是引用片段：
      SQL> delete from plan_table;
, s" X7 b/ f7 K3 m" A# A6 M, V　　3 rows deleted.
　　SQL>
　　SQL> explain plan for select
　　/*+full(a)*/ * from T_PEEKING a where b = :V;
　　Explained.
　　SQL>
　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
% U4 R7 Q) ~/ g7 ]( B! L8 M9 J! x　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
　　3 Plan_Table"
　　4 from plan_table
0 w& q! Y5 o2 J, Q$ Z( Z$ K4 H$ l0 W$ l, z　　5 start with id = 0
$ u, f' ]5 ?+ z& X+ |, T　　6 connect by prior id = parent_id
4 @" d2 M4 x  l% e/ O/ B- @7 o　　7 ;
　　Query
　　Plan_Table
　　----------------------------------------------------
- b1 K! C* i0 G8 _7 g; r　　SELECT STATEMENT Cost=75
! }* G3 o; f2 a　　TABLE ACCESS FULL T_PEEKING
　　SQL>
  d  U! H) h. n2 @! F4 k& D
　　这时，我们可以计算得出让优化器使用索引(无提示强制)的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>以下是引用片段：
. M' u8 l; R; m* n7 A* a( n　　System altered.
/ w. r, `1 i: L" E; `/ I1 P　　SQL>
　　SQL> delete from plan_table;
　　2 rows deleted.
$ s0 Q% }2 K) p$ o　　SQL>
　　SQL> explain plan for select * from T_PEEKING a where b = :V;
　　Explained.
9 s- O' n  `6 H, [7 j& S0 t5 j( h. H　　SQL>
　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
( k! g& N, x, A! @　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
7 ?0 v( X) j( \7 h　　3 Plan_Table"
3 P$ p5 b; t+ t1 h* m' G　　4 from plan_table
7 X; q; b+ }0 m2 g: u　　5 start with id = 0
　　6 connect by prior id = parent_id;
　　Query
* B& i5 k! ^5 u1 K$ A　　Plan_Table
　　-----------------------------------------------------------------
, O8 U+ {1 D  ?' J# H0 e1 F　　SELECT STATEMENT Cost=75
　　TABLE ACCESS FULL T_PEEKING
　　SQL>
　　SQL>
  N& [. S! @, Q$ I$ U; k　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66;
% h6 i' _1 D$ K& q* c) ^　　System altered.
$ ?7 N- s( @2 p# o- [4 h　　SQL>
　　SQL> delete from plan_table;
　　2 rows deleted.
　　SQL>
  A2 }: F" v- m2 y1 o* Y) _6 t　　SQL> explain plan for select * from T_PEEKING a where b = :V;
　　Explained.
2 ^4 H& q4 a! u3 O" F% r　　SQL>
9 O9 E6 P% {  n7 B　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
. ^; \) |% a) ~6 a- J; k　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
# A$ G9 b' e: w& a/ Q5 L2 O7 b1 d　　3 Plan_Table"
, v: f% ?9 Q8 `, s　　4 from plan_table
　　5 start with id = 0
　　6 connect by prior id = parent_id;
" l4 j- e- E% ]* q' r& x　　Query
6 d2 x2 n3 h+ m8 F: a$ h" Y& F　　Plan_Table
( J* ?* x! l, M. J; L　　---------------------------------------------------------
　　SELECT STATEMENT Cost=75
　　TABLE ACCESS BY INDEX ROWID T_PEEKING
　　INDEX RANGE SCAN T_PEEKING_IDX1

4 j/ G: P8 b' z　　可以看出，在使用绑定变量时，参数OPTIMIZER_INDEX_COST_ADJ对于是否选择索引会有重要的影响。
　　这里我们暂且不讨论索引扫描的原始成本是如何计算得出的。但是有一点很重要，在使用绑定变量时，计算出的成本是平均成本。在我们上面的例子中，字段B的值只有3个："A"、"B"、"C"，其中A最多，1003行中有1000行。因此，在索引上扫描值为A记录的成本为1000/1003 * 索引全扫描成本 ≈索引全扫描成本，我们看下它的成本是多少：
1 `0 j1 L8 l& m6 y$ w
8 t# M1 ?9 b& q$ e0 S, Q; U以下是引用片段：
4 j( |& V! A/ v, G  Y. p　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
　　System altered.
8 }. ~; }  V7 y; u& F* y　　SQL>
3 Y( H# ~; r  a* ?/ Z* x% s　　SQL> delete from plan_table;
　　2 rows deleted.
; B) o" O7 x1 v# t; e# V( W6 C　　SQL>
　　SQL> explain plan for select
　　/*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'A';
6 [* e& k+ A" r' p; `  A　　Explained.
　　SQL>
8 o# O8 L- ?+ B6 @  m　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
! V* \; W& D1 Z" P9 e+ n　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
8 x5 }2 r) `, v4 Y& p, c' q! `　　3 Plan_Table"
　　4 from plan_table
! e( L: b. v, t( `& N　　5 start with id = 0
　　6 connect by prior id = parent_id;
& z6 k) w' w& B" |7 c5 z1 a+ T, p　　Query
　　Plan_Table

会计考友

　　--------------------------------------------------------------
8 A* x9 U! k; e# T& f4 \' B　　SELECT STATEMENT Cost=336
1 t: t+ }% O% Y; D6 D! d　　TABLE ACCESS BY INDEX ROWID T_PEEKING
$ j) S  S; P4 x　　INDEX RANGE SCAN T_PEEKING_IDX1 </p>
8 t& y+ l& [6 j/ e3 d　　可以看到，它的成本是336。因此索引的平均成本是(336 * 1003/1000) / 3 ≈ 113，也就是使用绑定变量使的成本。而扫描其它两个值"B"和"A"时代价就非常小。 以下是引用片段：
2 J1 i6 B4 c  h# G* P      SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
: m: u/ Z1 D& k( E" d" E' P　　System altered.
　　SQL>
　　SQL> delete from plan_table;
　　3 rows deleted.
* i: c, z3 N5 m+ Z2 O' [$ n　　SQL>
" _  l" d5 ?) H+ X　　SQL> explain plan for select
4 _7 R$ S- c9 Z8 A; V　　/*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'B';
; m" y9 f0 L3 E$ \' s　　Explained.
2 u. v8 D6 A5 f　　SQL>
　　SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
　　2 object_name||' '||decode(id, 0, 'Cost='||position) "Query
2 m0 @: T& p) C& }0 d) q+ a6 h　　3 Plan_Table"
　　4 from plan_table
　　5 start with id = 0
0 d6 U' m3 \, E# b0 E  d　　6 connect by prior id = parent_id;
9 j  m1 P- @2 V3 X4 n5 W　　Query
　　Plan_Table
* _  p/ M. E/ \0 g　　---------------------------------------------------------------
% E) K! ]' p5 a- g: t$ w3 ~/ I　　SELECT STATEMENT Cost=2
& b: \' ^. U$ V　　TABLE ACCESS BY INDEX ROWID T_PEEKING
# F1 g% A5 }- U$ [5 R  n! S- p+ q　　INDEX RANGE SCAN T_PEEKING_IDX1
　　因为计算的成本是平均成本(相对实际扫描某个值的成本，平均成本更接近全表扫描成本)，因此在创建查询计划时，使用绑定变量将更加容易受到参数OPTIMIZER_INDEX_COST_ADJ影响，特别是上面的这种情况(即索引字段的集的势非常高时)下，平均代价与实际扫描某个值代价相差非常远。这种情况下，OPTIMIZER_INDEX_COST_ADJ对不使用绑定变量查询影响就非常小(因为索引代价不是比全表扫描成本大很多就是小很多)，不管扫描哪个值，不使用绑定变量将更加容易选择到合理的查询计划。
　　绑定变量窥视
　　在了解了参数OPTIMIZER_INDEX_COST_ADJ的作用后。再了解一个对查询计划，特别是使用绑定变量时会产生重大影响的特性：绑定变量窥视(Bind Variables Peeking)。
　　绑定变量窥视是9i以后的一个新特性。它使CBO优化器在计算访问代价时，将绑定变量传入的值考虑进去，从而计算出更合理的成本(否则，将会计算平均成本)。看下面例子： 以下是引用片段：
, W" e" \9 U+ C+ C! l+ `: e      SQL> conn sys/sys as sysdba
: V: D! p$ M$ R+ a+ T: ~( R& v4 j9 p　　Connected.
" F( C8 o& Y$ G- F　　SQL>
　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60;
5 x; q, o6 s. T# f　　System altered.
7 |4 t6 T0 F0 G5 t& }) o& M9 ~, q　　SQL> analyze table T_PEEKING compute
　　statistics for table for all indexes for all indexed columns;
2 |) f0 O- v8 Q" b' b　　Table analyzed.
　　SQL>
　　SQL> set autot trace
1 h! {. b8 H/ Y/ x( w　　SQL>
　　SQL> alter session set sql_trace = true;
　　Session altered.
　　SQL>
　　SQL> var v char(1)
　　SQL>
" ?( P6 y! ]' @, d" L$ K　　SQL> exec :v := 'A';
　　PL/SQL procedure successfully completed.
　　SQL>
1 |3 ?1 E6 q' }. S* t8 f　　SQL> select * from T_PEEKING a where b = :V;
　　1000 rows selected.
　　SQL>
2 m2 s  u7 U! o4 ~4 g$ W4 ?+ R　　SQL> alter session set sql_trace = false;
# R7 q& E9 N; |1 ]. n! H- O5 d1 P8 ^: L　　Session altered.
, p: P6 j2 }7 U2 {; n4 f% a
　　用Tkprof处理生成的trace文件。因为在存在绑定变量窥视时，autotrace或者explain plan可能不会显示正确的查询计划，需要Tkprof来处理sql trace。 tkprof fuyuncat_ora_5352.trc aaa.txt
% W  H3 C9 m0 g　　此时OPTIMIZER_INDEX_COST_ADJ是60，根据上面的结论，似乎查询计划应该选择扫描索引。但是，这里给绑定变量赋了值"A"，这时，优化器会“窥视”到这个值，并且在计算扫描成本时按照这个值的成本来计算。因此，得出的查询计划是全表扫描，而不是扫描索引，靠Tkprof分析的结果： 以下是引用片段：
3 B  @, _  e6 ]% P, y1 Q9 G      select * from T_PEEKING a where b = :V
　　call count cpu elapsed disk query current rows
　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
5 _5 i" o6 r* m: Q1 }+ X　　Parse 1 0.00 0.00 0 0 0 0
　　Execute 1 0.00 0.00 0 0 0 0
　　Fetch 68 0.01 0.07 0 406 0 1000
  g, T% p( [) j, J7 k$ N　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
　　total 70 0.01 0.08 0 406 0 1000
/ G( v# w# O: t5 S& D8 t　　Misses in library cache during parse: 1
+ T* G# U$ K+ @; t: l　　Optimizer mode: CHOOSE
% \/ l. C9 N' u' r' Z6 K- A! ~" W　　Parsing user id: SYS
  Y2 e. V7 s& \3 ~9 l. J　　Rows Row Source Operation
' Y  M5 w0 a" f) Z4 l3 l: u　　------- ---------------------------------------------------
　　1000 TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)

会计考友

　　但是，绑定变量窥视对一条语句只会使用一次。就是说，在第一次解析语句时，将绑定变量值考虑进去计算成本生成查询计划。以后在执行该语句时都采用这个查询计划，而不再考虑以后绑定变量的值是什么了。 SQL> conn sys/sys as sysdba</p>以下是引用片段：
, Y: v& y, }. s$ M  k& D! \3 a　　Connected.
　　SQL>
& s( X7 G1 b7 _! g! K　　SQL>
　　SQL> set autot trace
　　SQL>
　　SQL> alter session set sql_trace = true;
　　Session altered.
5 p$ k* p, s: w0 t9 x　　SQL>
　　SQL> var v char(1)
　　SQL>
2 e8 Y  G  X8 r& N0 ^　　SQL> exec :v := 'B';
　　PL/SQL procedure successfully completed.
7 i+ [( E0 a( e3 D' w: r! \　　SQL>
, Z1 ]6 u& F1 M) U; g) k* r3 P. @　　SQL> select * from T_PEEKING a where b = :V;
9 T7 @! i( r( Z5 y  P2 V% I. c　　1000 rows selected.
　　SQL>
7 \9 Z* J$ a: o+ Y　　SQL> alter session set sql_trace = false;
, I' e6 R/ Z! z' p2 }% D' ^　　Session altered.
4 u% w4 N$ S: s2 d- Z
9 M$ r* G5 }& \7 f　　再用Tkprof分析生成的trace文件，看到尽管这里的值是"B"，选择索引扫描会更优，但分析结果中查询计划还是使用全表扫描： select *
以下是引用片段：
　　from
　　T_PEEKING a where b = :V
　　call count cpu elapsed disk query current rows
3 n; |! H; e/ e7 V5 ^' h　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
# l4 p8 V' w% H　　Parse 1 0.00 0.00 0 0 0 0
* E( T1 z2 s. j2 f　　Execute 1 0.00 0.00 0 0 0 0
　　Fetch 2 0.00 0.00 0 340 0 2
+ j( r; U5 S6 @. v1 _: D5 E( z1 X　　------- ------ -------- ---------- ---------- ---------- ---------- ----------
　　total 4 0.00 0.00 0 340 0 2
; I0 B: Z' l. ?' o$ P$ `, D7 K) @. f　　Misses in library cache during parse: 0
　　Optimizer mode: CHOOSE
( {4 i/ }$ Q# I" I　　Parsing user id: SYS
& _5 g! j3 C3 L: y, S1 Y　　Rows Row Source Operation
　　------- ---------------------------------------------------
  P" B- _- e7 ?; l; h　　2 TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)
& i0 R+ r7 q& G/ x- U3 U9 x
) P% M; m9 U7 u　　因此，这种情况下使用绑定变量也会导致无法选择最优的查询计划。
8 h& u# H2 k. K% c- N' H( a4 N　　综上所述，我们可以得出一个结论：在对建有索引的字段(包括字段集)，且字段(集)的集的势非常大时，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。