TOP 第五章 之 CBO配置策略

继续贴出 Troubleshooting Oracle Performance 一书第五章《配置查询优化器》的翻译稿的部分节录。本章初稿译者胡怡文.

配置还是不配置

在此套用一句肯尼亚谚语 ，”配置查询优化器的代价是昂贵的，但值得为此付出。”_(注1)实际上，我曾见过许多低估良好配置重要性的站点。有时，我甚至和一些人进行激烈的讨论，他们认为”没有必要费心为每一个数据库单独配置查询优化器，我们已经有一套初始化参数，而且在所有数据库上屡试不爽。”通常，我首先会这样回答这个问题：”如果一个配置集能适用于所有数据库，为什么Oracle要介绍二十多个专用于查询优化器的初始化参数？甲骨文公司知道自己在干什么。如果存在这样一个万能的神奇配置，他们会以默认的方式提供，还可以省去一大堆初始化参数的正式说明文档。”接下来我会详细解释这个神奇配置不存在的原因：

• 每个应用都有自身的需求和负载概要，并且
• 每一个由不同的软硬件构成的系统，都有其自身的特点。

如果遇到麻烦的是顾客，通常我也会提醒他们”您找我是因为你遇到性能上的问题，对吧？由于某种原因，应用没有达到最佳表现，但数据库也得为目前的情况负一部分责任…所以，让我们着手处理这个问题吧。”

据说，至少从Oracle9iR2开始，查询优化器就能良好运转了，这意味着它能够为大多数_注2SQL语句生成高效的执行计划。然而更准确的说，仅在查询优化器配置正确且数据库的设计能够发挥其所有特性时才是如此。这件事我已再三强调，同时也要记住查询优化器的配置不仅包括初始化参数的设置，也包括系统统计和对象统计。

配置路线图

既然没有这样的神奇配置，就需要一个可靠的步骤来帮助我们。图5-1汇总了我采用的主要步骤。

图5-1. 配置路线图的主要步骤
下面是对图中标有数字的步骤的描述

• 1. 总是需要调整的两个初始化参数：optimizer_mode和db_file_multiblock_read_count。 就如你将在本章后面看到的那样，后者对查询优化器本身来说已经不再那么重要。然而，个别操作的性能还将严重依赖它。
• 2. 这步要调整的几个初始化参数通常默认已经被设置为比较合适的值，所以这步显得不是十分重要。然而无论如何，这一步的目的是要启用或禁用查询优化器的某些特性。
• 3. 既然系统统计和对象统计为查询优化器提供了至关重要的信息，那么它们必须被收集。
• 4. 根据初始化参数workarea_size_policy的不同，在内存中存储数据时选择是手动还是自动调整内存的使用量。做出选择后，其它初始化参数值在第五步或第六步设置。
• 5. 如果上面选择的是自动设置工作使用的内存量，那么需要设置初始化参数pga_aggregate_target。
• 6. 如果选择手工设置的话，实际使用的内存量分别取决于对每一种不同操作所设置的阀值。基本上，对每一种不同的操作都要设置一个特殊的初始化参数。
• 7. 当刚才的设置生效后，就开始测试应用。在测试期间，对没有表现出期望性能的那部分收集执行计划。通过分析执行计划，你应该能够推测出问题所在。本阶段需要注意，关键是要识别出一般的、非特殊的现象。比如说，需要注意查询优化器是否使用了过多或过少的索引，又或是否没有正确识别约束。
• 8. 如果查询优化器能够为大多数SQL语句提供高效的执行计划，说明配置成功。否则，需要进行第九步。
• 9. 假如查询优化器倾向于使用过多或过少的索引，又或是嵌套循环的话。通常需要调整初始化参数optimizer_index_caching和optimizer_index_cost_adj修正这个问题。如果查询优化器错误地估计了基数（cardinality）。有可能是某些直方图丢失或需要调整。在Oracle 11g中，扩展的统计（extended statistic）也能够提供一些帮助。

根据图5-1，从第1步到第6步设置的初始化参数不宜改变。当然，这也不是铁板钉钉的。如果在第九步调节了索引相关的初始化参数和直方图后，还没有达到预期的效果。就有必要从头再来了。还有一点要提一下，既然有些初始化参数的设置对系统统计有影响，在调整它们以后，必须重新计算一下系统统计值。

• 注1：那句肯尼亚谚语是”和平的代价是昂贵的，但值得为此付出。” 可以在http://www.quotationspage.com/quote/38863.html. 找到这个引用。
• 注2：和我们在其它可想像的活动中一样，在软件开发中完美也是不可信任的。即使你和Oracle都不希望这样，查询优化器也是如此。因此，应当期盼只有少数的SQL语句需要手动调整的介入（这个话题放在第七章）。

–EOF–