[zt] 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)