optimizer_index_caching和optimizer_index_cost_adj兩個引數說明

OPTIMIZER_INDEX_COST_ADJ

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_CACHING

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. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

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. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.

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.

建立測試表:

我們分別觀察一下全表掃描和索引訪問的成本:

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

我們透過調整optimizer_index_cost_adj來看一下執行計劃的變化:

參考文件:

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店 .............................................................................................................................................