成本控制：Oracle 优化器内幕

【原文：http://www.oracle.com/technology/global/cn/oramag/webcolumns/2003/techarticles/burleson_cbo_pt2_pt1.html】

在本系列的第 1 部分中，我们讨论了 Oracle 的基于成本的 SQL 优化器的基本机制，作出关于数据访问路径的最佳决策。在这一结尾部分中，我们将重点讨论为更改执行计划而使用柱状图、外部成本核算功能、SQL 提示，以及用于查找和调整未达到最优化的 SQL 的技术。

使用柱状图

在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。

只有当存在数据偏差或怀疑有数据偏差时，才应该创建列的柱状图。在现实中这种情况很少见，而对优化器最常见的误解之一是将不必要的柱状图引入到优化器统计中。柱状图向优化器表明该列没有线性地分布，而优化器将深入查看 SQL where 子句中的文字型数值，并将该值与柱状图统计中的柱状图存储记录相比较（参见图 1）。

许多 Oracle 专业人员误解了柱状图的目的。尽管柱状图用于作出是否使用索引来访问表的决策，但它们最常用于预测多项表联接的中间结果集的大小。

例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有柱状图将会极大地帮助优化器作出正确的决策。（记住，即使列没有索引并且不能作为联接键，您也可以创建柱状图。）

由于复杂的模式可能具有数万个列，对每列进行偏差评估是不切实际的，因此 Oracle 提供了一种自动化的方法，将柱状图构建为 dbms_stats 实用程序的一部分。通过使用 dbms_stats 的 method_opt=>'for all columns size skewonly' 选项，您可以指导 Oracle 为那些列值出现严重偏差的列自动创建柱状图。我们将在以后对该选项作更加详细的研究。

作为一项基本规则，柱状图用于预测集合基数和结果集中返回的行数。例如，假设我们有一个 product_type 索引，70% 的值为 HARDWARE 类型。只要指定带有 where product_type='HARDWARE' 的 SQL，全表扫描就是最快的执行计划，而带有 where product_type='SOFTWARE' 的查询则使用索引访问是最快速的。

由于柱状图为 SQL 的分析阶段增加了额外的开销，您应该避免使用它们，除非需要它们用于更快速的优化器执行计划。但是在有些情况下建议创建柱状图：

当在查询中引用该列时 — 记住，如果查询不引用该列，则创建柱状图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。

当列值的分布存在明显的偏差时 — 这种偏差应该相当明显，以至于 WHERE 子句中的值将会使优化器选择不同的执行计划。

当列值导致不正确的判断时 — 如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。向该列添加柱状图经常会向优化器提供使用最佳联接方法所需的信息。

那么我们如何找到那些适合于柱状图的列呢？dbms_stats 的一个激动人心的特性是能够自动寻找那些应该具有柱状图的列，并能创建柱状图。还要记住，多存储记录的柱状图为 SQL 语句增加了庞大的分析开销，只有当 SQL 将基于列值选择不同的执行计划时，才应该使用柱状图。

为帮助进行明智的柱状图生成，Oracle 使用 dbms_stats 的 method_opt 参数。在 method_opt 子句中还有重要的新选项，即 skewonly、repeat 和 auto。

      method_opt=>'for all columns size skewonly'
      method_opt=>'for all columns size repeat'
      method_opt=>'for all columns size auto'

让我们来详细查看每种方法选项。

第一种是 “skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列，它将为该索引创建柱状图，以帮助基于成本的 SQL 优化器决定是使用索引还是全表扫描访问。例如，如果一个索引具有一列，在 50% 的行中具有该列值，则全表扫描检索这些行比索引扫描快。

柱状图还用于具有绑定变量的 SQL 和启用 cursor_sharing 的 SQL。在这些情况中，优化器确定列是否会影响计划的执行，如果有影响，则用文字代替绑定变量并执行硬分析：

begin
 dbms_stats.gather_schema_stats(
   ownname          => 'SCOTT', 
   estimate_percent => dbms_stats.auto_sample_size, 
   method_opt       => 'for all columns size skewonly', 
   degree           => 7
 );
end;
/

在实施监视 (alter table xxx monitoring;) 时使用 auto 选项，它基于数据的分布以及应用程序访问列的方式（例如由监视所确定的列上的负载）来创建柱状图。使用 method_opt=>'auto' 的方式与 dbms_stats 的 option 参数中 gather auto 的方式相似：

begin
   dbms_stats.gather_schema_stats(
      ownname          => 'SCOTT', 
      estimate_percent => dbms_stats.auto_sample_size, 
      method_opt       => 'for all columns size auto', 
      degree           => 7
   );
end;
/

选择性、集簇和柱状图

切记，优化器了解表中列数据的许多重要特征，最显著的是列值的选择性和列的集簇因子。

例如，以下我们看到一个使用列值来形成结果集的查询：

select
   customer_name
from
   customer
where
   customer_state = 'Rhode Island';

在此示例中，选择使用索引还是全表扫描受到罗得岛客户比例的影响。如果罗得岛客户的比例非常小，并且数值集簇在数据块中，则对于此查询而言索引扫描可能是最快的执行计划。

许多 Oracle 开发人员对于当他们只检索很少量的行时优化器选择全表扫描而感到困惑，而没有意识到优化器考虑了表中列值的集簇。

Oracle 在 dba_indexes 视图中提供一个名为 clustering_factor 的列，通知优化器关于表的行与索引的同步情况。当集簇因子接近数据块的数量时，表的行与索引同步。

列值的选择性、db_block_size、avg_row_len 以及集合基数全都协同工作，帮助优化器决定是使用索引还是使用全表扫描。如果数据列具有高度的选择性和低的 clustering_factor，则索引扫描通常是最快的执行方法（参见图 2）。

如果多数 SQL 引用了具有高 clustering_factor、大 db_block_size 和小 avg_row_len 的列，则 DBA 有时会周期性地对表的行进行重排序或使用单表集簇来维持行的顺序。这种方法将所有相邻的行放置在同一数据块中，消除了全表扫描，使查询速度的增加高达 30 倍。

相反，高 clustering_factor 的数值达到表中的行数 (num_rows)，表明这些行的顺序与索引中的顺序不同，索引范围扫描将会需要额外的 I/O。由于 clustering_factor 达到表中的行数，这些行与索引不同步。

但是，即使列具有高度的选择性，高 clustering_factor 和小 avg_row_len 也会表示列值在表中随机分布，而获取这些行需要额外的 I/O。在此情况下，索引范围扫描会导致大量不必要的 I/O（参见图 3）；全表扫描则会高效得多。

总而言之，clustering_factor、db_block_size 和 avg_row_len 全都影响优化器有关执行全表扫描或是索引范围扫描的决策，理解优化器如何使用这些统计信息非常重要。

我们已经注意到，每个新版本的优化器都有改进，并且 Oracle Database 10g 提供的最新增强特性在确定执行计划时会考虑外部的影响。Oracle 称此特性为外部成本核算，并包括对 CPU 和 I/O 成本的评估。

优化器的外部成本核算

从 Oracle9i Database 开始直到 Oracle Database 10g，已经对优化器进行了增强，在确定最佳执行计划时考虑外部的影响。由于 Oracle 数据库并不是在真空环境中运行，优化器必须够将每个 SQL 操作的外部磁盘 I/O 成本和 CPU 周期成本计算在内。该过程对于运行 all_rows 优化的查询尤其重要，这种情况下对服务器资源的最小化是主要目标。

CPU_COST — 优化器现在可以对操作所需的机器周期数量进行评估，并将这一成本归入执行计划的计算中。为 Oracle 查询提供服务的相关 CPU 成本依赖于当前的服务器配置（Oracle 看不到这些信息）。在 Oracle Database 10g 中，CPU 成本核算是默认的行为，因为考虑与每个 SQL 执行阶段相关的 CPU 成本十分重要 — 因此，明智的 Oracle 专业人员应该利用 dbms_stats.get_system_stats 启用 CPU 成本核算。CPU 成本通常并不重要，除非整个 Oracle 实例正在使用过多的 CPU 资源。

IO_COST — 优化器已获得增强，可以对操作所需的物理块读取数量进行评估。I/O 成本与操作所读取的物理数据块数量成正比。但是，优化器并不能预先了解数据缓冲区的内容，不能区分逻辑读取（缓冲区内）与物理读取。由于这一缺点，优化器不能了解数据块是否已经处于 RAM 数据缓冲区中。

根据 Oracle 文档，按以下方式评估 I/O 和 CPU 成本：

Cost =  (#SRds * sreadtim + 
         #MRds * mreadtim + 
         #CPUCycles / cpuspeed ) / sreadtim

where:

#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles *)

sreadtim  - single block read time
mreadtim - multi block read time
cpuspeed  -  CPU cycles per second

注意，成本是读取数量以及相关取时间的函数，加上查询的 CPU 成本估计值。还要注意，外部成本核算不考虑处于 RAM 数据缓冲区中的数据块数量，但将来版本的优化器很可能会考虑这一因素。

在这里我们看到，Oracle 在评估执行计划时使用了 CPU 和 I/O 成本评估。当我们在有许多并发进程服务于查询的情况下将并行查询因素包括在内时，这一等式变得甚至更加复杂。

使用 CPU 成本核算的最大益处在于 all_rows 执行计划，此时的成本比 first_rows 优化的成本更重要。

接着，让我们来看优化器如何受到统计量的影响。为了作出有关最佳执行计划的明智决策，优化器必须使用关于查询中涉及的所有数据对象的信息。由于您控制着如何收集统计量，所以优化器调整的这一方面非常关键。

使用提示来更改执行计划

由于每一版本的优化器都变得更为完善，Oracle 为更改您的 SQL 执行计划提供了不断增多的方法。Oracle 提示的最常见用处是作为调试工具。您可以使用提示来确定最优的执行计划，然后向回执行，调节统计量，使调整后的 SQL 模拟所提示的查询。

使用 Oracle 提示可能非常复杂，而 Oracle 开发人员只是将提示用作最后的手段，首先应改变统计量来更改执行计划。Oracle 包含 124 种以上的提示，其中许多提示在 Oracle 文档中找不到。

让我们快速浏览如何使用提来改变优化器执行计划：优化器提示是放置在 SQL 语句的注释内的优化器指示，用于那些不常出现的情况，即优化器作出了关于执行计划的不正确决策。由于提示处于注释内，因此确保提示名称拼写正确并确保提示适用于该查询十分重要。

例如，以下提示是无效的，因为 first_rows 访问与并行访问相互排斥。这是因为 parallel 始终假定进行全表扫描，而 first_rows 支持进行索引访问。

-- An invalid hint
select /*+ first_rows parallel(emp,8)*/ 
   emp_name 
from 
   emp 
order by 
   ename;

某些 Oracle 专业人员会将提示集合在一起，以强化他们的期望。例如，如果我们有一台具有八个或更多 CPU 的 SMP 服务器，则我们可能希望使用 Oracle 并行查询来加速合法的全表扫描。在使用并行查询时，我们很少会希望在表一级启用并行机制 (alter table customer parallel 35;)，因为表的并行机制设置会影响优化器，导致优化器认为全表扫描的代价并不高。因此，多数 Oracle 专业人员在逐个查询的基础上指定并行查询，将完全提示与 parallel 提示联合使用，以确保快速的并行全表扫描：

-- A valid hint
select /*+ full parallel(emp,35)*/ 
   emp_name 
from 
   emp 
order by 
   ename;

既然我们已经了解了提示的一般性概念，就让我们来观察优化器调整最重要的提示之一。

ordered 提示确定查询执行的驱动表，还指定将表联接在一起的顺序。ordered 提示要求应该以它们在 from 子句中所指定的顺序进行联接，from 子句中的第一个表指定为驱动表。使用 ordered 提示可以节省大量的分析时间，并加速 SQL 的执行，因为您告诉优化器联接表的最佳顺序。

例如，以下查询使用排序提示，将表以它们在 from 子句中指定的顺序联接起来。在该示例中，我们通过指定 emp 到 dept 的联接使用散列联接，sal 到 bonus 的联接使用嵌套循环联接，进一步改进了执行计划：

select 
/*+ ordered use_hash (emp, dept) use_nl (sal, bon) */
from
   emp,
   dept,
   sal,
   bon
where . . .

当然，ordered 提示最常用于数据仓库查询或联接超过五个表的 SQL 中。

接下来让我们观察另一种也是最后的手段，即 Oracle 参数的调整。Oracle 不建议更改这些参数中的任何参数，除非作为最后的手段使用。但是，观察这些参数如何改变优化器确定执行计划的方式非常有趣。

查找未最优化的 SQL

尽管复杂查询可能具有极为复杂的执行计划，大多数 Oracle 专业人员必须调整具有以下问题的 SQL：

对表进行未最优化的索引访问 — 这种问题在优化器无法找到索引或者 SQL 中最有限制性的 where 子句与索引不匹配时发生。当优化器无法找到访问表的行的适当索引时，优化器会始终调用全表扫描，读取表中的每一行。因此，大型表的全表扫描可能显示有未最优化的 SQL 语句，可以通过添加一个与查询的 where 子句相匹配的索引来调整该语句。

未最优化的联接方法 — 优化器具有许多可用的联接方法，包括合并联接、嵌套循环联接、散列联接和星型联接。为选择正确的联接方法，优化器必须猜测多项表联接的中间结果集的大小。要进行这种猜测，优化器拥有的信息不完整。即使提供了柱状图，优化器也不能确切了解联接所返回的确切行数。最常见的补救方法是使用提示来更改联接 (use_nl、use_hash) 或重新分析目标表的统计量。

让我们来分析 v$sql_plan 视图如何能够帮助我们寻找 SQL 调整的机会。在搜索调整机会时，我们从查询 v$sql_plan 视图开始，以便找出这些大型表的全表扫描，如列表 2 所示。然后，我们提取相应的 SQL 并查看全表扫描是否适当还是由于缺失索引而造成的。

我们如何找到适于进行全表扫描的小型表？一种方法是搜索当前处于库高速缓存中的 SQL。Oracle 随后可以生成一个报表，列出在该时刻数据库中所有的全表扫描。列表 2 中的脚本从 v$sql_plan 中检查执行计划，并以全表扫描的频率生成报表。

报表（参见列表 3）具有以下的列：

OWNER — 表的模式拥有者
NAME — dba_tables 中的表名
NUM_ROWS — dba_tables 最后计算统计信息的表中的行数
C（只在 Oracle7 中具有） — 一个 Oracle7 特有的列，如果表被高速缓存则显示 Y，如果没有被高速缓存则显示 N
K（只在 Oracle8 及以后版本中具有） — 如果表被分配到 KEEP 池中则显示“K”
BLOCKS — 在 dba_segments 中定义的表中的块数
NBR_FTS — 对表进行的全表扫描次数（用于当前处于库高速缓存中的 SQL）。

该报表提供关于两个调整方面的信息：

KEEP池的表和索引 — SQL 的速度可以得益于将频繁进行全表扫描的小型表（以及相关索引）放置在 KEEP 池中。以上的报表显示大型表和小型表上进行的全表扫描。分析该报表，我们可以通过选择具有少于 50 个块并且没有 “K” 标注的表，快速识别出 KEEP 池的可选对象。
将表、分区或索引分配到 KEEP 池很容易，利用 alter 系统命令可以随意添加或删除对象：
```
alter table CUSTOMER storage (buffer_pool KEEP);
```

可能缺失的索引 — 大型表的全表扫描有时可能指示缺失索引。Oracle 的基于函数的索引特别适用于这种目的，因为任何 where 子句都可以与基于函数的索引相匹配。
例如，以下是一个使用 substr 和 to_char BIF 的基于函数的索引：
```
create index
fbi_book
on book
(
substr(book_key,1,3)
||
to_char(book_retail_price)

);
```
总之，v$sql_plan 中包含的信息是一种极好的执行系统级 SQL 调整的方法。

在 Oracle Database 10g 中有什么新特性？

随着 Oracle Database 10g 的出现，我们现在看到基于成本的 SQL 优化器有了显著的内在提高，并且自动的 SQL 优化机制更加方便。对 Oracle Database 10g 的 SQL 优化器的重大更改包括以下激动人心的主题：

不支持基于规则的优化器 — 尽管在 Oracle Database 10g 中存在基于规则的优化器 (RBO)，Oracle 强烈建议那些使用基于规则的优化的客户不要再迟疑了。那些仍在使用 RBO 的网站可以切换到 first_rows optimizer_mode 并将参数 optimizer_index_cost_adj 调整到一个小的数字 (< 25)，使基于成本的优化器模拟 RBO 的行为。那些不希望在其 Oracle Database 10g 移植过程中更改其执行计划的商店可以使用 Oracle 的优化器计划稳定性功能，保留其移植前的基于规则的执行计划。

用户启动的缓冲区高速缓存刷新 — 现在您可以在运行测试查询过程之间手动地刷新缓冲区高速缓存，这样便于您诊断和测试 SQL 运行时执行过程。对于 SQL 单元测试，这种清除数据缓冲区的功能确保了唯一的 SQL 响应时间测试，并去除了与 RAM 数据高速缓存相关的性能易变性。

SQLAccess Advisor — SQLAccess Advisor 是 dbms_advisor 程序包内部的一个专家系统，可识别（并提供解决方案的建议）SQL 执行的性能问题。它从库高速缓存中分析 SQL 并建议哪些索引或物化视图需要创建、删除或保留。

成本的基本知识

尽管我们已经非常详细地了解了优化器内幕，还要不断学习更多内容，因为每个新版本的优化器都变得功能更加强大（和复杂）。本文的要点包括调整优化器行为的一般性原则：

在极个别情况下，索引列的值将会更改最优的执行计划，柱状图为优化器提供详细的列信息。因此，只应该在已证明是合适的时候使用柱状图。

编写出可获得正确数据的 SQL 是不够的。应该让开发人员负责调整他们的 SQL 并以最优的 SQL 格式化进行培训，他们应该了解如何使用 explain plan 和 TKPROF。

SQL 优化最常见的问题是缺失索引（或无选择性的索引）以及未最优化的表联接方法。

Oracle 提示用于更改查询的执行计划，但只应该当作最后的手段。

v$sql_plan 视图显示库高速缓存中所有 SQL 的执行计划，您可以查询该视图来获得对 SQL 执行过程内部的有用了解。

特别感谢 Oracle SQL 精英 Andrew Holdsworth 的帮助。

posted on 2009-01-22 23:19 CodingME! 阅读(812) 评论(0) 编辑收藏举报

刷新页面返回顶部

Developer

成本控制：Oracle 优化器内幕

公告

导航