Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ
Tom Kyte的新书Effective Oracle by Design的第6章 Getting the Most Out of the Cost-Based Optimizer中介绍了参数OPTIMIZER_INDEX_COST_ADJ，并认为可以理解为Oracle执行多块（MultiBlock）I/O（比如全表扫描）的代价与执行单块（Single-block）I/O代价的相对比例。Tom指出，9i下该参数缺省值为100，意即二者拥有相同的代价，若减小，则CBO倾向于使用索引（即单块I/O），反之则倾向于全表扫描（多块I/O）。Tom同样给出了一个不错的例子。
在自己的手提上试验了一下，硬盘大小原因，只装了一个Oracle 10g(10.1.0.2)，谁知就发现该参数在10g下取值发生了改变。我们先来看一下参照Tom的实验：
A. 创建数据表。
SQL> drop table t1;
表已删除。
SQL> drop table t2;
表已删除。
SQL> create table t1
2 as
3 select mod(rownum,1000) id,rpad('x',300,'x') data
4 from all_objects
5 where rownum<=5000;
表已创建。
SQL> ed
已写入 file afiedt.buf
1 create table t2
2 as
3 select rownum id,rpad('x',300,'x') data
4 from all_objects
5* where rownum<=1000
SQL> /
表已创建。
B.创建索引并分析。
SQL> create index idx_t1 on t1(id);
索引已创建。
SQL> create index idx_t2 on t2(id);
索引已创建。
SQL> ed
已写入 file afiedt.buf
1 begin
2 dbms_stats.gather_table_stats
3 (user,'T1',method_opt=>'for all indexed columns',cascade=>true);
4 dbms_stats.gather_table_stats
5 (user,'T2',method_opt=>'for all indexed columns',cascade=>true);
6* end;
SQL> /
PL/SQL 过程已成功完成。
C.查询缺省值，并设置好环境。
SQL> set autot off
SQL> show parameters optimizer_index_cost_adj;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL> set autot traceonly eXP stat;
D.在缺省值下查询的结果。
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000
)
1 0 TABLE Access (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card
=1 Bytes=100)
2 1 NESTED LOOPS (Cost=13 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3
Card=5 Bytes=500)
4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)
5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
367 recursive calls
0 db block gets
101 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
30 rows processed
E.修改参数值，注重，引用Tom的结论，9i中这个值在0-100之间，而10g呢？
SQL> alter session set optimizer_index_cost_adj=0;
ERROR:
ORA-00068: 值 0 对参数 optimizer_index_cost_adj 无效, 必须在 1 和 10000 之间
SQL> alter session set optimizer_index_cost_adj=1;
会话已更改。
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)
2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1
Card=5 Bytes=500)
4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)
5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
SQL> alter session set optimizer_index_cost_adj=50;
会话已更改。
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)
2 1 NESTED LOOPS (Cost=7 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=2
Card=5 Bytes=500)
4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)
5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
F.继续修改参数值，改大。
SQL> alter session set optimizer_index_cost_adj=200;
会话已更改。
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=5 Bytes=1000
)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card
=1 Bytes=100)
2 1 NESTED LOOPS (Cost=26 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=6
Card=5 Bytes=500)
4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)
5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
SQL> alter session set optimizer_index_cost_adj=500;
会话已更改。
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=5 Bytes=1000
)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=10 Car
d=1 Bytes=100)
2 1 NESTED LOOPS (Cost=63 Card=5 Bytes=1000)
3 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 By
tes=500)
4 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
90 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
SQL> alter session set optimizer_index_cost_adj=1000;
会话已更改。
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000
)
1 0 HASH JOIN (Cost=66 Card=5 Bytes=1000)
2 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte
s=500)
3 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt
es=2600)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
271 consistent gets
213 physical reads
0 redo size
1651 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
SQL> alter session set optimizer_index_cost_adj=10000;
会话已更改。
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000
)
1 0 HASH JOIN (Cost=66 Card=5 Bytes=1000)
2 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte
s=500)
3 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt
es=2600)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
1651 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
G.现在看看对性能的影响。
SQL> set timing on
SQL> alter session set optimizer_index_cost_adj=100;
会话已更改。
已用时间: 00: 00: 00.00
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000
)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card
=1 Bytes=100)
2 1 NESTED LOOPS (Cost=13 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3
Card=5 Bytes=500)
4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)
5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
SQL> alter session set optimizer_index_cost_adj=1;
会话已更改。
已用时间: 00: 00: 00.00
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)
2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1
Card=5 Bytes=500)
4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)
5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
SQL> alter session set optimizer_index_cost_adj=10000;
会话已更改。
已用时间: 00: 00: 00.00
SQL> ed
已写入 file afiedt.buf
1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /
已选择30行。
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000
)
1 0 HASH JOIN (Cost=66 Card=5 Bytes=1000)
2 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte
s=500)
3 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt
es=2600)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
1651 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
来具体分析一下。
首先，10g中OPTIMIZER_INDEX_COST_ADJ的取值范围发生了改变。9i中为0-100，而10g中为1-10000。
其次，由于测试环境的不同，Tom的测试结果是在缺省值（100）的环境下，就已经和上面取值500时一样了，即对T2全表扫描而T1使用索引。Tom试验中，减小取值直至0，访问路径就变成使用两个索引，而并不会出现均不使用索引的情况。除去系统的不同（可能导致取缺省值时访问路径是否一致），只看变化趋势，显然10g中灵活性更高，1-10000的取值使得CBO可以覆盖所有的访问路径。另一方面，正如Tom的结论所说，OPTIMIZER_INDEX_COST_ADJ的取值越大，优化器越倾向于使用全表扫描，取值越小，优化器越倾向于使用索引。
再次，我们对比相同访问路径下的不同点。在取值从1变化到200（1-50-100-200）的过程中，优化器计算出的代价是持续增长的，而从1000到10000则是不变的。这说明这个参数与索引I/O的代价有关，而和全表扫描并无关系，这与Tom所说的并不矛盾，不过显然更精确一点。
最后我们其实应该看到，虽然有如上所说的代价变化问题，同一访问路径下实际的运行性能并无区别，由于数据量比较小，上面的例子也许不能很好的说明这一点，不过想想Oracle用相同的路径去执行，也没有理由不同性能吧。
好，来看看官方文档吧。10G的官方文档（Reference）中对这个参数描述如下：
OPTIMIZER_INDEX_COST_ADJ
Parameter type Integer 参数类型为整数
&nb |
