Link:
http://www.eygle.com/sql/OPTIMIZER_INDEX_COST_ADJ.htm

OPTIMIZER_INDEX_COST_ADJ
这个初始化参数代表一个百分比,取值范围在1到10000之间.
该参数表示索引扫描 和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。

这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右
对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，
可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值.
更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算.

本文通过实验对该参数的使用作出探讨和说明.

我们看到optimizer_index_cost_adj的缺省值为100.

PHP code:



[oracle@jumper udump]$ sqlplus eygle/eygle





SQL*Plus: Release 9.2.0.3.0 - Production on Mon Jun 28 17:11:15 2004





Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.








Connected to:


Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production


With the Partitioning, OLAP and Oracle Data Mining options


JServer Release 9.2.0.3.0 - Production





SQL> show parameter optimizer_index_cost_adj





NAME TYPE VALUE


------------------------------------ ----------- ------------------------------


optimizer_index_cost_adj integer 100


SQL> 





.

创建测试表:

PHP code:



SQL> create table t as select * from dba_objects;





Table created.





SQL> create index ind_owner on t(owner);





Index created.





SQL> analyze table t compute statistics;





Table analyzed.





.

我们分别观察一下全表扫描和索引访问的成本:

PHP code:



SQL> set autotrace traceonly








SQL> select * from t where owner='EYGLE';





Execution Plan


----------------------------------------------------------


0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)


1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)











SQL> select /*+ index(t ind_owner) */ * from t where owner='EYGLE';





Execution Plan


----------------------------------------------------------


0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=476 Bytes=36652)


1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16 Card=476 Bytes=36652)


2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估.

在比较的时候，Oracle会把索引扫描的成本转换为全表扫描的成本，和全表扫描的COST进行比较.这个转换需要一个转换因子.
就是optimizer_index_cost_adj:

optimizer_index_cost_adj * (Index Scan Cost) = 等价的 Full Scan Cost

这个 等价的 Full Scan Cost 就是来和全表扫描成本进行比较的.

而这个转换因子的临界值实际上就是Full Scan Cost 和 Index Scan Cost的比值.

即:


optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost

PHP code:



SQL> set autotrace off


SQL> select (14/16)*100 from dual;





(14/16)*100


-----------


87.5





1 row selected.


.

我们通过调整optimizer_index_cost_adj来看一下执行计划的变化:

PHP code:



SQL> set autotrace traceonly





SQL> alter session set optimizer_index_cost_adj = 87;





Session altered.





SQL> select * from t where owner='EYGLE';





Execution Plan


----------------------------------------------------------


0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)


1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=14 Card=476 Bytes=36652)


2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)





'此时使用索引成本较低.等价全表扫描成本为:





87% * (Index Scan Cost) < Full Scan Cost





此时Oracle选择了索引.'





SQL> alter session set optimizer_index_cost_adj = 88;





Session altered.





SQL> select * from t where owner='EYGLE';





Execution Plan


----------------------------------------------------------


0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)


1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)





'此时使用索引成本较高.等价全表扫描成本为:





88% * (Index Scan Cost) > Full Scan Cost





所以Oracle选择了全表扫描.'

参考文档:

http://www.evdbt.com/SearchIntelligenceCBO.doc

我前几天也刚刚研读了这篇文章，并写了读书笔记（水平一般）。
http://www.itpub.net/showthread. ... 15&pagenumber=1
可是对这个参数一直还是模棱两可。
其中biti给了我很详细的解释。这下eygle的例证又让我清楚了很多。

只介绍这个参数而不提db_file_multiblock_read_count

有些可惜阿

这麽巧，Maillists中也说到这几个参数　

QUOTE:

---

最初由 Fenng 发布
只介绍这个参数而不提db_file_multiblock_read_count

有些可惜阿

---

是呀是呀，我也觉得主要是跟这个参数有关，到是没有只针对一个参数做过调整。

QUOTE:

---

最初由 eygle 发布
Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估.

在比较的时候，Oracle会把索引扫描的成本转换为全表扫描的成本，和全表扫描的COST进行比较.这个转换需要一个转换因子.
就是optimizer_index_cost_adj:

optimizer_index_cost_adj * (Index Scan Cost) = 等价的 Full Scan Cost

这个 等价的 Full Scan Cost 就是来和全表扫描成本进行比较的.

而这个转换因子的临界值实际上就是Full Scan Cost 和 Index Scan Cost的比值.

即:


optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost

PHP code:



SQL> set autotrace off


SQL> select (14/16)*100 from dual;





(14/16)*100


-----------


87.5





1 row selected.


.

---

另，其实关于这个参数，兴许大多情况下 根据 system  event  数据来调整 可能是合理的(这个没检验过，但单个例子一定是不合理的)，但，这一定是可能有偏差的

因为  sql 总是复杂多样的  ， 并且 sql  选取的 数据的百分比 也是变化的，还有就是  FTS 的成本跟很多因素有关，综合起来总是很困难的