oracle认证：何时使用绑定变量性能反而差_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

　　看以下例子：

以下是引用片段：
　　SQL> create table T_PEEKING (a NUMBER, b char(1), c char(2000));
　　Table created.
　　SQL>
　　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);
　　4 end loop;
　　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;
　　Table analyzed.
　　SQL>

　　我们看下索引扫描的代价是多少： SQL> show parameter OPTIMIZER_INDEX_COST_ADJ

以下是引用片段：
　　NAME TYPE VALUE
　　———————————— ———– ——
　　optimizer_index_cost_adj integer 100
　　SQL> delete from plan_table;
　　0 rows deleted.
　　SQL>
　　SQL> explain plan for select
　　/*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V;
　　Explained.
　　SQL> select lpad( , 2*(level-1))||operation|| ||options|| ||
　　2 object_name|| ||decode(id, 0, Cost=||position) “Query
　　3 Plan_Table”
　　4 from plan_table
　　5 start with id = 0
　　6 connect by prior id = parent_id
　　7 ;
　　Query
　　Plan_Table
　　—————————————————–
　　SELECT STATEMENT Cost=113
　　TABLE ACCESS BY INDEX ROWID T_PEEKING
　　INDEX RANGE SCAN T_PEEKING_IDX1
　　SQL>

　　再看全表扫描的代价是多少： 以下是引用片段：
      SQL> delete from plan_table;
　　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|| ||
　　2 object_name|| ||decode(id, 0, Cost=||position) “Query
　　3 Plan_Table”
　　4 from plan_table
　　5 start with id = 0
　　6 connect by prior id = parent_id
　　7 ;
　　Query
　　Plan_Table
　　—————————————————-
　　SELECT STATEMENT Cost=75
　　TABLE ACCESS FULL T_PEEKING
　　SQL>

　　这时，我们可以计算得出让优化器使用索引(无提示强制)的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;

以下是引用片段：
　　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.
　　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
　　5 start with id = 0
　　6 connect by prior id = parent_id;
　　Query
　　Plan_Table
　　—————————————————————–
　　SELECT STATEMENT Cost=75
　　TABLE ACCESS FULL T_PEEKING
　　SQL>
　　SQL>
　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66;
　　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.
　　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
　　5 start with id = 0
　　6 connect by prior id = parent_id;
　　Query
　　Plan_Table
　　———————————————————
　　SELECT STATEMENT Cost=75
　　TABLE ACCESS BY INDEX ROWID T_PEEKING
　　INDEX RANGE SCAN T_PEEKING_IDX1

　　可以看出，在使用绑定变量时，参数OPTIMIZER_INDEX_COST_ADJ对于是否选择索引会有重要的影响。

　　这里我们暂且不讨论索引扫描的原始成本是如何计算得出的。但是有一点很重要，在使用绑定变量时，计算出的成本是平均成本。在我们上面的例子中，字段B的值只有3个：”A”、”B”、”C”，其中A最多，1003行中有1000行。因此，在索引上扫描值为A记录的成本为1000/1003 * 索引全扫描成本 ≈索引全扫描成本，我们看下它的成本是多少：

以下是引用片段：
　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
　　System altered.
　　SQL>
　　SQL> delete from plan_table;
　　2 rows deleted.
　　SQL>
　　SQL> explain plan for select
　　/*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = A;
　　Explained.
　　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
　　5 start with id = 0
　　6 connect by prior id = parent_id;
　　Query
　　Plan_Table
　　————————————————————–
　　SELECT STATEMENT Cost=336
　　TABLE ACCESS BY INDEX ROWID T_PEEKING
　　INDEX RANGE SCAN T_PEEKING_IDX1

　　可以看到，它的成本是336。因此索引的平均成本是(336 * 1003/1000) / 3 ≈ 113，也就是使用绑定变量使的成本。而扫描其它两个值”B”和”A”时代价就非常小。 以下是引用片段：
      SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;
　　System altered.
　　SQL>
　　SQL> delete from plan_table;
　　3 rows deleted.
　　SQL>
　　SQL> explain plan for select
　　/*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = B;
　　Explained.
　　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
　　5 start with id = 0
　　6 connect by prior id = parent_id;
　　Query
　　Plan_Table
　　—————————————————————
　　SELECT STATEMENT Cost=2
　　TABLE ACCESS BY INDEX ROWID T_PEEKING
　　INDEX RANGE SCAN T_PEEKING_IDX1

　　因为计算的成本是平均成本(相对实际扫描某个值的成本，平均成本更接近全表扫描成本)，因此在创建查询计划时，使用绑定变量将更加容易受到参数OPTIMIZER_INDEX_COST_ADJ影响，特别是上面的这种情况(即索引字段的集的势非常高时)下，平均代价与实际扫描某个值代价相差非常远。这种情况下，OPTIMIZER_INDEX_COST_ADJ对不使用绑定变量查询影响就非常小(因为索引代价不是比全表扫描成本大很多就是小很多)，不管扫描哪个值，不使用绑定变量将更加容易选择到合理的查询计划。

　　绑定变量窥视

　　在了解了参数OPTIMIZER_INDEX_COST_ADJ的作用后。再了解一个对查询计划，特别是使用绑定变量时会产生重大影响的特性：绑定变量窥视(Bind Variables Peeking)。

　　绑定变量窥视是9i以后的一个新特性。它使CBO优化器在计算访问代价时，将绑定变量传入的值考虑进去，从而计算出更合理的成本(否则，将会计算平均成本)。看下面例子： 以下是引用片段：
      SQL> conn sys/sys as sysdba
　　Connected.
　　SQL>
　　SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60;
　　System altered.
　　SQL> analyze table T_PEEKING compute
　　statistics for table for all indexes for all indexed columns;
　　Table analyzed.
　　SQL>
　　SQL> set autot trace
　　SQL>
　　SQL> alter session set sql_trace = true;
　　Session altered.
　　SQL>
　　SQL> var v char(1)
　　SQL>
　　SQL> exec :v := A;
　　PL/SQL procedure successfully completed.
　　SQL>
　　SQL> select * from T_PEEKING a where b = :V;
　　1000 rows selected.
　　SQL>
　　SQL> alter session set sql_trace = false;
　　Session altered.

　　用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
　　——- —— ——– ———- ———- ———- ———- ———-
　　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
　　——- —— ——– ———- ———- ———- ———- ———-
　　total 70 0.01 0.08 0 406 0 1000
　　Misses in library cache during parse: 1
　　Optimizer mode: CHOOSE
　　Parsing user id: SYS
　　Rows Row Source Operation
　　——- —————————————————
　　1000 TABLE ACCESS FULL T_PEEKING (cr=406 pr=0 pw=0 time=5052 us)

　　但是，绑定变量窥视对一条语句只会使用一次。就是说，在第一次解析语句时，将绑定变量值考虑进去计算成本生成查询计划。以后在执行该语句时都采用这个查询计划，而不再考虑以后绑定变量的值是什么了。 SQL> conn sys/sys as sysdba

以下是引用片段：
　　Connected.
　　SQL>
　　SQL>
　　SQL> set autot trace
　　SQL>
　　SQL> alter session set sql_trace = true;
　　Session altered.
　　SQL>
　　SQL> var v char(1)
　　SQL>
　　SQL> exec :v := B;
　　PL/SQL procedure successfully completed.
　　SQL>
　　SQL> select * from T_PEEKING a where b = :V;
　　1000 rows selected.
　　SQL>
　　SQL> alter session set sql_trace = false;
　　Session altered.

　　再用Tkprof分析生成的trace文件，看到尽管这里的值是”B”，选择索引扫描会更优，但分析结果中查询计划还是使用全表扫描： select *

以下是引用片段：
　　from
　　T_PEEKING a where b = :V
　　call count cpu elapsed disk query current rows
　　——- —— ——– ———- ———- ———- ———- ———-
　　Parse 1 0.00 0.00 0 0 0 0
　　Execute 1 0.00 0.00 0 0 0 0
　　Fetch 2 0.00 0.00 0 340 0 2
　　——- —— ——– ———- ———- ———- ———- ———-
　　total 4 0.00 0.00 0 340 0 2
　　Misses in library cache during parse: 0
　　Optimizer mode: CHOOSE
　　Parsing user id: SYS
　　Rows Row Source Operation
　　——- —————————————————
　　2 TABLE ACCESS FULL T_PEEKING (cr=340 pr=0 pw=0 time=1005 us)

　　因此，这种情况下使用绑定变量也会导致无法选择最优的查询计划。

　　综上所述，我们可以得出一个结论：在对建有索引的字段(包括字段集)，且字段(集)的集的势非常大时，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。