OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache.You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache
可以理解為在buffer cache裡面找到索引塊的百分比，預設0

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal
optimizer_index_cost_adj可以理解為把索引掃描的成本轉換為全表掃描的成本百分比，預設100%表示索引掃描成本等價轉換為全表掃描成本。
Oracle在選擇不同的訪問路徑時，會對全表掃描和索引掃描進行比較評估，在比較的時候，Oracle會把索引掃描的成本轉換為全表掃描的成本，和全表掃描的COST進行比較。這個轉換需要一個轉換因子，就是Optimizer_index_cost_adj;
Optimizer_index_cost_adj*(index scan cost)=等價的Full Scan cost

比如oracle算出全表掃描成本為20，索引掃描為30
如果設定optimizer_index_cost_adj等於50，則索引掃描*50%=15=等價的全表掃描為15，這個時候比算出的全表掃描要低，所以走索引掃描。
如果設定optimizer_index_cost_adj等於100，則索引掃描*100%=30=等價的全表掃描為30，這個時候比算出的全表掃描要高，所以走全表掃描
20/30=66.7%,說明臨界值是67%，當大於67%比如100%則走全表掃描，當小於67%比如50%則走索引掃描

10g開始，optimizer_mode替代了以上兩個引數的作用
optimizer_mode=ALL_ROWS更容易走全表掃描
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

first_rows_n:The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
all_rows:The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

OPTIMIZER_MODE：告訴CBO在生成執行計劃的時候的一個方向。
- 以響應時間優先：first_rows_N
要求不考慮代價，最快的返回前N條記錄，N為1,10,100,1000
通常會產生索引掃描，或者nested loop join的執行計劃。
適用於OLTP系統。

- 以吞吐量優先：all_rows
要求以最小資源開銷為目的，返回所有記錄。
通常會產生全表掃描，或者hash join的執行計劃。
適用於DW或者混合系統。

OPTIMIZER_MODE、optimizer_index_cost_adj

相關文章