扫描成本和optimizer_index_cost_adj
我们知道，在cbo模式下，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对于是否选择索引会有重要的影响。
