oracle使用绑定变量性能反而更差？

2008-04-14 16:38:08　来源:中国自学编程网　作者:佚名点击:

当我在做培训时，在解释绑定变量的好处时，大家都比较容易理解。但是，对于并不是任何时候绑定变量都是最优的。这一点很多人不是和理解。

　　可以看到，它的成本是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分析的结果：