该初始化参数也表示一个百分比,0~10000,表示索引访问的I/O开销相对于全表扫描的I/O开销。缺省值100 ,对cbo来说,表示索引访问的开销和全表扫描是等效的。(索引访问花费的时间和全表扫描花费的时间几乎是相等的。) The default value of 100 indicates to the cost-based optimizer that indexed access is 100% as costly (i.e., equally costly) as FULL table scan access.
对于大多数的OLTP系统 OPTIMIZER_INDEX_COST_ADJ 的范围应该在10到50之间;而对于数据仓库或其他DSS, 就需要小心的设置成50。a valid value for OPTIMIZER_INDEX_COST_ADJ can easily be retrieved from the Oracle database itself. The answers lie in the V$SYSTEM_EVENT view, in the column AVERAGE_WAIT.After the database has been up and running normally for a sufficient period of time (i.e., a couple hours or more)下面有一个好方法用来确定如何设置该参数:
SQL>SELECT EVENT,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE ‘db file s%’;
This query will retrieve information on the two I/O wait events db file scattered reads (a.k.a. FULL table scans) and db file sequential reads (a.k.a. indexed scans). The AVERAGE_WAIT column contains the average timing, in 1/100ths of a second, of these events:
EVENT AVERAGE_WAITS ========================= ============== db file sequential reads .33178629 db file scattered reads 2.190087 In this example, indexed scan I/O requests takes only 15% as long as each FULL table scan I/O request. So, set OPTIMIZER_INDEX_COST_ADJ to 15.