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 成本模型，我将在后面讨论。