OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ

这两个参数是在oracle 9i对执行计划的优化开始发挥影响。
OPTIMIZER_INDEX_CACHING
该初始化参数表示一个百分比，0％～99％，缺省值0 ，对cbo来说，意味着0％的数据块（使用索引访问）可以在oracle‘s SGA的buffer cache中发现。即所有的对索引的访问都将需要物理读（每一个对buffer cache的逻辑读都产生一个对I/o子系统的物理读），也可以看作是：对buffer cache 0％的hit ratio。该参数只影响CBO计算访问索引块时候的成本，和涉及的表无关。
This parameter applies only to the CBO’s calculations of accesses for blocks in an index, not for the blocks in the table related to the index.

OPTIMIZER_INDEX_COST_ADJ
该初始化参数也表示一个百分比，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.

OPTIMIZER_INDEX_CACHING 应该设成 90 ，对于大多数的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), perform the following query:
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.(SQL>select .33178629/2.190087 from dual;

.33178629/2.190087
------------------
.151494571)

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7916042/viewspace-915438/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7916042/viewspace-915438/