作者 Donald K. Burleson
自 2001 年起成为 OTN 成员

是否正在将应用程序移植到 Oracle 基于成本的优化器环境（或开发新的应用程序）？本文是有关使用柱状图、外部成本核算功能、SQL 提示等等的最新信息。另外，还有 Oracle Database 10g 增强特性集锦。

在本系列的第 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 文档中找不到。（参见列表 1）

让我们快速浏览如何使用提示来改变优化器执行计划：优化器提示是放置在 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 不建议更改这些参数中的任何参数，除非作为最后的手段使用。但是，观察这些参数如何改变优化器确定执行计划的方式非常有趣。