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

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

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

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