从事IT基础架构多年，发现自己原来更合适去当老师……喜欢关注新鲜事物，不仅限于IT领域。
分类： Oracle
2007-02-05 15:16:40
在本系列的中，我们讨论了 Oracle 的基于成本的 SQL 优化器的基本机制，作出关于数据访问路径的最佳决策。在这一结尾部分中，我们将重点讨论为更改执行计划而使用柱状图、外部成本核算功能、SQL 提示，以及用于查找和调整未达到最优化的 SQL 的技术。
使用柱状图
在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。
只有当存在数据偏差或怀疑有数据偏差时，才应该创建列的柱状图。在现实中这种情况很少见，而对优化器最常见的误解之一是将不必要的柱状图引入到优化器统计中。柱状图向优化器表明该列没有线性地分布，而优化器将深入查看 SQL where 子句中的文字型数值，并将该值与柱状图统计中的柱状图存储记录相比较（参见）。
 |
许多 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 的分析阶段增加了额外的开销，您应该避免使用它们，除非需要它们用于更快速的优化器执行计划。但是在有些情况下建议创建柱状图：
那么我们如何找到那些适合于柱状图的列呢？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，则索引扫描通常是最快的执行方法（参见）。
 |
如果多数 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（参见）；全表扫描则会高效得多。
 |
总而言之，clustering_factor、db_block_size 和 avg_row_len 全都影响优化器有关执行全表扫描或是索引范围扫描的决策，理解优化器如何使用这些统计信息非常重要。
我们已经注意到，每个新版本的优化器都有改进，并且 Oracle Database 10g 提供的最新增强特性在确定执行计划时会考虑外部的影响。Oracle 称此特性为外部成本核算，并包括对 CPU 和 I/O 成本的评估。
优化器的外部成本核算
从 开始直到 ，已经对优化器进行了增强，在确定最佳执行计划时考虑外部的影响。由于 Oracle 数据库并不是在真空环境中运行，优化器必须能够将每个 SQL 操作的外部磁盘 I/O 成本和 CPU 周期成本计算在内。该过程对于运行 all_rows 优化的查询尤其重要，这种情况下对服务器资源的最小化是主要目标。
根据 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 不建议更改这些参数中的任何参数，除非作为最后的手段使用。但是，观察这些参数如何改变优化器确定执行计划的方式非常有趣。
ALL_ROWS AND_EQUAL ANTIJOIN APPEND BITMAP BUFFER BYPASS_RECURSIVE_CHECK BYPASS_UJVC CACHE CACHE_CB CACHE_TEMP_TABLE CARDINALITY CHOOSE CIV_GB COLLECTIONS_GET_REFS CPU_COSTING CUBE_GB CURSOR_SHARING_EXACT DEREF_NO_REWRITE DML_UPDATE DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT DRIVING_SITE DYNAMIC_SAMPLING DYNAMIC_SAMPLING_EST_CDN EXPAND_GSET_TO_UNION FACT FIRST_ROWS FORCE_SAMPLE_BLOCK FULL GBY_CONC_ROLLUP GLOBAL_TABLE_HINTS HASH HASH_AJ HASH_SJ HWM_BROKERED IGNORE_ON_CLAUSE IGNORE_WHERE_CLAUSE INDEX_ASC INDEX_COMBINE INDEX_DESC INDEX_FFS INDEX_JOIN INDEX_RRS INDEX_SS INDEX_SS_ASC INDEX_SS_DESC INLINE LEADING LIKE_EXPAND LOCAL_INDEXES MATERIALIZE MERGE MERGE_AJ MERGE_SJ MV_MERGE NESTED_TABLE_GET_REFS NESTED_TABLE_SET_REFS NESTED_TABLE_SET_SETID NL_AJ NL_SJ NO_ACCESS NO_BUFFER NO_EXPAND NO_EXPAND_GSET_TO_UNION NO_FACT NO_FILTERING NO_INDEX NO_MERGE NO_MONITORING NO_ORDER_ROLLUPS NO_PRUNE_GSETS NO_PUSH_PRED NO_PUSH_SUBQ NO_QKN_BUFF NO_SEMIJOIN NO_STATS_GSETS NO_UNNEST NOAPPEND NOCACHE NOCPU_COSTING NOPARALLEL NOPARALLEL_INDEX NOREWRITE OR_EXPAND ORDERED ORDERED_PREDICATES OVERFLOW_NOMOVE PARALLEL PARALLEL_INDEX PIV_GB PIV_SSF PQ_DISTRIBUTE PQ_MAP PQ_NOMAP PUSH_PRED PUSH_SUBQ REMOTE_MAPPED RESTORE_AS_INTERVALS REWRITE RULE SAVE_AS_INTERVALS SCN_ASCENDING SELECTIVITY SEMIJOIN SEMIJOIN_DRIVER SKIP_EXT_OPTIMIZER SQLLDR STAR STAR_TRANSFORMATION SWAP_JOIN_INPUTS SYS_DL_CURSOR SYS_PARALLEL_TXN SYS_RID_ORDER TIV_GB TIV_SSF UNNEST USE_ANTI USE_CONCAT USE_HASH USE_MERGE USE_NL USE_SEMI USE_TTT_FOR_GSETS
Undocumented (under-documented) hints:
BYPASS_RECURSIVE_CHECK BYPASS_UJVC CACHE_CB CACHE_TEMP_TABLE CIV_GB COLLECTIONS_GET_REFS CUBE_GB CURSOR_SHARING_EXACT DEREF_NO_REWRITE DML_UPDATE DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT DYNAMIC_SAMPLING DYNAMIC_SAMPLING_EST_CDN EXPAND_GSET_TO_UNION FORCE_SAMPLE_BLOCK GBY_CONC_ROLLUP GLOBAL_TABLE_HINTS HWM_BROKERED IGNORE_ON_CLAUSE IGNORE_WHERE_CLAUSE INDEX_RRS INDEX_SS INDEX_SS_ASC INDEX_SS_DESC LIKE_EXPAND LOCAL_INDEXES MV_MERGE NESTED_TABLE_GET_REFS NESTED_TABLE_SET_REFS NESTED_TABLE_SET_SETID NO_EXPAND_GSET_TO_UNION NO_FACT NO_FILTERING NO_ORDER_ROLLUPS NO_PRUNE_GSETS NO_STATS_GSETS NO_UNNEST NOCPU_COSTING OVERFLOW_NOMOVE PIV_GB PIV_SSF PQ_MAP PQ_NOMAP REMOTE_MAPPED RESTORE_AS_INTERVALS SAVE_AS_INTERVALS SCN_ASCENDING SKIP_EXT_OPTIMIZER SQLLDR SYS_DL_CURSOR SYS_PARALLEL_TXN SYS_RID_ORDER TIV_GB TIV_SSF UNNEST USE_TTT_FOR_GSETS