OPTIMIZER_INDEX_CACHING Parameter

如前所述，CBO 假定與所有 I/O 關聯的實際成本和開銷相同，而與 I/O 的類型無關，除非通過 optimizer_index_cost_adj 參數另有說明。

CBO預設做出的另一個關鍵假設是，所有I/O都将是實體I/O（PIO），是以執行成本相對較高，并且“值得”被計算成本。

但是，當然情況并非總是如此，因為Oracle請求和通路的許多塊已經緩存在緩沖區緩存中。在特定情況下，CBO 可以考慮索引的可能緩存特征，并相應地降低與索引相關的執行路徑的成本。但請注意，這僅适用于重複通路同一索引的特定情況下與索引特定塊關聯的 I/O。

例如，在嵌套循環聯接的情況下，通常通過索引查找通路内部表，同一索引可能會在循環中多次重複通路該表。是以，當索引結構不斷被通路時，與此索引關聯的許多塊很可能被緩存。與索引查找過程作為 IN 清單條件的結果的方案相同。對于 IN 清單中的每個元素，通常使用索引在表中查找相應的值，進而一次又一次地通路 IN 清單中每個元素的特定索引。随着索引的不斷被通路，其許多關聯的塊可能已經緩存在記憶體中。

optimizer_index_caching 參數的目的是告訴 CBO 在這些類型的操作期間，與索引相關的塊可能已緩存在緩沖區緩存中的百分比，是以不應在與索引相關執行路徑關聯的總體成本中考慮這些塊。預設值為 0，這意味着預設情況下 Oracle 不會考慮緩存任何索引塊，并且在索引通路路徑期間與索引關聯的所有 I/O 都需要被視為 PIO 并相應地進行成本核算。但是，如果optimizer_index_caching參數設定為 25，則意味着 CBO 将認為與索引塊直接關聯的所有 I/O 中有 25% 可能已經緩存，是以索引塊 I/O 的總體成本将降低 25%。

如前所述，基于CBO I/O的成本計算公式為：

基本指數範圍掃描成本=指數水準+ceil（指數選擇性x葉塊）+ceil（表選擇性x聚類因子）

optimizer_index_caching 參數通過僅減少公式的索引通路部分，按以下方式調整公式：

基本指數範圍掃描成本 = ceil（（指數水準 + ceil（索引選擇性 x 葉塊）） x （1- optimizer_index_caching）） + ceil（表選擇性 x 聚類因子）

但僅适用于特定的索引掃描，例如嵌套循環連接配接和 IN 清單條件，其中索引可能在同一執行路徑内連續通路。

是以，如果我們要回到我在第一篇CBO和索引介紹文章中的單個謂詞示範中介紹的示例，如下所示：

SQL>從 id = 420 bowie_stuff2中選擇 *;

已選擇 2000 行。

執行計劃

———————————————————-

計劃哈希值：134336835

——————————————————————————

|編号|操作|名稱|行|位元組|成本|

——————————————————————————

|0|選擇對賬單| |2000|36000 |18 |

|1|按索引 ROWID 進行表通路|BOWIE_STUFF2 |2000|36000 |18 |

|×2|索引範圍掃描|BOWIE_STUFF2_I |2000| |9|

——————————————————————————

我們注意到執行計劃的成本是18。

如果我們現在将optimizer_index_caching參數更改為75，這意味着所有索引塊的75%現在可能被緩存并重新運作查詢：

SQL>更改系統集optimizer_index_caching= 75;

系統已更改。

SQL>從 id = 420 bowie_stuff2中選擇 *;

已選擇 2000 行。

執行計劃

———————————————————-

計劃哈希值：134336835

——————————————————————————

|編号|操作|名稱|行|位元組|成本|

——————————————————————————

|0|選擇對賬單| |2000|36000 |18 |

|1|按索引 ROWID 進行表通路|BOWIE_STUFF2 |2000|36000 |18 |

|×2|索引範圍掃描|BOWIE_STUFF2_I |2000| |9|

——————————————————————————

我們注意到開銷在 18 時保持不變，參數沒有影響，因為查詢基于單個表相等謂詞，并且沒有涉及嵌套循環或 IN 清單條件的處理。

但是，如果我們運作第二個IN清單謂詞示範，涉及如下所示的IN清單條件（首先将optimizer_index_caching參數重置為0）：

SQL>更改系統設定optimizer_index_caching = 0;

系統已更改。

SQL> 從bowie_stuff2位置中選擇 * ID （20， 30， 420）;

已選擇 6000 行。

執行計劃

———————————————————-

計劃哈希值：2964430066

——————————————————————————-

|編号|操作|名稱|行|位元組|成本|

——————————————————————————-

|0|選擇對賬單| |6000 |10.5萬|49 |

|1|清單疊代器| | | | |

|2|按索引 ROWID 進行表通路|BOWIE_STUFF2 |6000 |10.5萬|49 |

|*3|索引範圍掃描|BOWIE_STUFF2_I |6000 | |23|

——————————————————————————-

我們注意到我們的成本為49。請記住，49 的成本是按以下方式計算的，因為我們在 IN 清單條件中有 3 個元素：

成本 = 指數水準 + ceil（指數選擇性 x 葉塊） + ceil（表選擇性 x 聚類因子）

= 2 + 3 x 頭鷹（0.01 x 602） + 頭皮（0.03 x 852）

= 2 + 3×7 + 26

= 2 + 21 + 26

= 23 + 26 = 49

如果我們現在将optimizer_index_caching參數更改為 75 并重新運作相同的 IN 清單查詢：

SQL>更改系統集optimizer_index_caching = 75;

系統已更改。

SQL> 從bowie_stuff2位置選擇 * ID （20， 30， 420）;

已選擇 6000 行。

執行計劃

———————————————————-

計劃哈希值：2964430066

——————————————————————————-

|編号|操作|名稱|行|位元組|成本|

——————————————————————————-

|0|選擇對賬單| |6000 |10.5萬|32|

|1|清單疊代器| | | | |

|2|按索引 ROWID 進行表通路|BOWIE_STUFF2 |6000 |10.5萬|32|

|*3|索引範圍掃描|BOWIE_STUFF2_I |6000 | |6 |

——————————————————————————-

 49下降到32。optimizer_index_caching設定為 75 如何更改成本：

基本指數範圍掃描成本 = ceil（（指數水準 + ceil（索引選擇性 x 葉塊）） x （1- optimizer_index_caching）） + ceil（表選擇性 x 聚類因子）

= 頭頸鹿（（2 + 3 x 頭頸鹿（0.01 x 602）） x （1-0.75）） + 頭鷹（0.03 x 852）

= 頭鷹（（2 + （3×7）） x 0.25） + 26

= 頭頸鹿（（2 + 21） x 0.25）+ 26

= 6 + 26 = 32

是以，以前有 23 個索引塊 I/O，而現在已經減少到隻有 6 個。請注意，在索引掃描中通路實際表塊的 I/O 和相關成本保持不變。

那麼如何在資料庫中設定此參數呢？好吧，這一切都存在許多問題。

首先，與optimizer_index_cost_adj參數一樣，隻有一個“全局”參數（用于系統或會話），這意味着需要對所有索引以及此參數可能産生影響的所有情況平均任何值。然而，例如，一些索引可能非常小并且被大量通路，是以很可能在任何時間點緩存大多數索引塊（包括葉塊），而其他索引可能非常大，很少和随機通路，這意味着也許隻有在（比如）IN清單操作期間也可能緩存分支級塊。如前所述，對于所有平均值，可能會有一些示例，其中值是合适的，太高或太低，具體取決于特定指數的特征。

其次，與較差的表相關的塊沒有等效的參數，是以Oracle始終假設索引掃描中的表塊不僅是PIO，而且FTS将僅由PIO組成，相反，情況可能并非總是如此。是以，雖然我們可能對索引塊被緩存的可能性進行合理的猜測（例如通過緩沖區緩存命中率，v$bh視圖的研究等），但CBO對表相關塊的可能緩存特征進行了這樣的考慮。是的，索引塊更有可能被緩存，尤其是在optimizer_index_caching參數有影響的特定情況下，但這并不意味着表塊将始終是 PIO。是以，簡單地将此參數設定為看似合理的通用索引緩存值可能仍存在過度偏袒索引的風險，即使它僅影響成本計算公式中索引通路的塊，因為 CBO 不會為可能緩存在 FTS 中的表塊提供任何此類成本預留。

在理想情況下，我們會對所有單個索引和表的緩存特征有所了解，并根據正在通路的段及其關聯的緩存特征，讓 CBO 對執行路徑中的成本估算進行必要的調整。在我們到達這樣一個理想的世界（BTW可能不會那麼遙遠）之前，我基本上建議根本不設定此參數，而隻是簡單地確定您使用準确的系統統計資訊并具有足夠準确的段統計資訊。

我建議設定此參數，如果您發現CBO通常沒有為上述情況選擇合适的索引，而也許應該這樣做，并且朝着正确的方向稍微“推動”成本就足以解決問題。如果設定不正确，optimizer_index_caching參數并不像optimizer_index_cost_adj參數那樣過于“危險”，因為它隻影響與索引塊I / O直接相關的公式的“一半”，而不是表塊I / O，這通常構成許多索引範圍掃描操作中整體I / O的更大比例（盡管正如我上面的例子所示， 這也取決于）。

但是，通過設定這兩個optimizer_index參數，它們都可以幫助降低與索引相關的執行計劃的總體成本。如上所示，optimizer_index_caching參數首先僅影響與索引塊 I/O 相關的公式的一半的成本，然後optimizer_index_cost_adj參數進一步影響總體結果成本。是以，如果我們要再次運作 IN 清單查詢，但這次還将optimizer_index_cost_adj設定為 25，并将optimizer_index_caching保留為 75：

SQL>更改系統集optimizer_index_cost_adj = 25;

系統已更改。

SQL>從bowie_stuff2位置中選擇 * ID 在 （20， 30， 420）;

已選擇 6000 行。

執行計劃

———————————————————-

計劃哈希值：2964430066

——————————————————————————-

|編号|操作|名稱|行|位元組|成本|

——————————————————————————-

|0|選擇對賬單| |6000 |10.5萬|8 |

|1|清單疊代器| | | | |

|2|按索引 ROWID 進行表通路|BOWIE_STUFF2 |6000 |10.5萬|8 |

|*3|索引範圍掃描|BOWIE_STUFF2_I |6000 | |2 |

——————————————————————————-

我們注意到執行計劃的成本從32進一步降低到隻有8。基本上，這隻是之前的32 x optimizer_index_cost_adj = 32 x 0.25 = 8的成本。

但是，我不會設定這些參數中的任何一個，而隻是建議适當地使用系統統計資訊和 CPU 成本模型，我将在後面讨論。