基于成本的Oracle优化法则

当我们讨论SQL语句的成本时，需要倍加谨慎，这是因为“成本”一词同时存在两方面的含义。一方面，成本可以指由诸如解释计划(explain plan)等工具导致的大量的执行次数；另一方面，成本可以指执行SQL语句导致的实际资源消耗。当然，从理论上讲，这两方面含义之间的关系应该比较简单明了，因此也就不必过多地纠缠这两种含义。

本书中的“成本”一词是指优化器执行的计算导致的消耗。本书的目的在于解释优化器执行计算过程中的主要特性，以此分析计算成本，并确定SQL语句的执行计划(execution plan)。

与此同时，本书还介绍了一些其他问题，这些问题能够使得优化器产生的成本似乎与实际资源消耗无关。当您阅读这本书时，感觉到“这个查询一定要这样做的原因原来是这样啊”，我们将倍感欣慰。

1.1 优化器选项

最常见的SQL语句类型是select语句—— 虽然本书的其他部分都将集中讨论select语句，但此处需要强调的是，无论是哪种语句，只要是执行查询操作，DML(比如更新)或DDL(比如索引重建)都需要通过基于成本的优化器(CBO)进行分析。

Oracle为基于成本的优化器提供了3个变体。这3个变体的代码中构建不同的约束，但是它们都遵循同一策略—— 即针对某个SQL语句，寻找能够最少的资源消耗来达到目标的执行机制。这3个变体可以通过参数optimizer_mode标识：

（评：v$sql 字段optimizer_mode）

●       all_rows：优化器将寻找能够在最短的时间内完成语句的执行计划(通常表示“返回所有行”)。该变体没有在代码中构建特别的约束。

●       first_rows_N：N可以为1、10、100或1000(如果需要进行进一步优化，可以采用first_rows(n)提示的形式，其中n可以是任意正整数)。优化器首先通过彻底分析第一个连接顺序(join order)来估计返回行的总数目。这亲就可以知道查询可能获得的整个数据集的片断，并重新启动整个优化进程，其目标在于找到能够以最小的资源消耗返回整个数据片断的执行计划。该选项是在Oracle 9i中引入的。

●       first_rows：在Oracle 9i中这一选项已经过时，但是出于向后兼容的原因，仍然保留了这一选项。该选项的作用在于寻找能够在最短的时间内返回结果集的第一行的执行计划。该变体的代码中构建了几个高层次约束。例如，有一个约束就是“避免归并连接和散列连接，除非除此以外只能对内部(第二个)表进行全表扫描的嵌套循环”。这一规则倾向于促使优化器使用索引访问路径，偶尔会出现非常不恰当的访问路径。针对这一特殊问题的示例和相关内容可以在本章的联机代码包中的脚本文件first_rows.sql中找到，从Apress网站(www.apress.com)和www.tupwk.com.cn中都可以找到这个代码包。

参数optimizer_mode还存在其他两个选项(即使在Oracle 10g中也是如此)，分别为rule和choose。由于基于规则的优化(Rule Based Optimization)在多年前就已过时(只有某些内部SQL仍在使用/*+ rule */提示)，而且Oracle 10g中最终不再支持RBO，所以本书将完全忽略RBO。

对于choose模式来说，它为优化器提供了一种运行时选择方式，可以在基于规则的优化和all_rows之间进行选择。既然本书忽略基于规则的优化，那么choose模式就无需多言了。在此仅仅申明以下问题，在Oracle 10g中，如果使用数据库配置助手(Database Configuration Assistant，DBCA)来建立数据库，或者通过调用脚本catproc.sql来手动创建数据库，那么将自动安装一个作业(由脚本catmwin.sql创建，在视图dba_scheduler_jobs中可见)，它将对缺少统计信息或统计信息失效的任何表每24小时生成一次统计信息。因此，如果将optimizer_mode设置为choose，可能将触发all_rows优化，但是您可能会发现所有缺少最新统计信息的表将采用动态采样。这是因为在Oracle 10g中，参数optimizer_dynamic_sampling的默认值为2(意味着针对没有统计信息的任何表都采用动态采样)，而在Oracle 9i中该参数的默认值为1。

另一个与optimizer_mode有关的参数为optimizer_goal。虽然optimizer_goal只能在会话中动态地设定，而且在spfile(init.ora)中不可用，但是就优化策略而言，这两个参数似乎没有什么区别。

1.2 成本的定义

网络上有关CBO的一个最常见的问题就是“成本究竟表示什么？”针对这一问题的评论通常为“根据执行计划，针对一个查询的散列连接的成本是700万，嵌套循环的成本是42—— 但是散列连接只需3秒就可以完成，而嵌套循环却需要14个小时。”

答案很简单：成本表示(也总是表示)优化器对执行语句所用时间的最优估计。但是面对上面所示的散列连接/嵌套循环连接示例时，怎样才能得到正确的时间估计呢？答案可以在缩写词GIGO中找到，GIGO(Garbage In，Garbage Out)表示无用信息输入，无用信息输出。

出现如下问题时，CBO将产生错误：

●       建立成本模型所做的某些假设不合适。

●       数据分布的相关统计信息可用，但是容易误导。

●       数据分布的相关统计信息不可用。

●       不了解硬件的性能特征。

●       不了解当前的工作负荷。

●       代码中有bug。

本书将分析这些问题，并以此研究优化器的演化。但是，对于这些问题产生的影响，本书仅简要介绍CBO在Oracle的最近版本中(也可能在以后的版本中)出现的一些变化，这些变化导致优化器的含义解释困难得多。

在Oracle 8i中，优化器只是简单地计算期望对I/O子系统所作的请求的数目，并选择一个所需请求数目最小的执行计划。这并不能说明表扫描比索引访问路径会消耗多得多的CPU资源，也不能说明读128个数据块(128-block)时间比读单个数据块的时间要长。也不能说明理论上读128个数据块实际上可以精确地转换为25个单独的请求，这是因为在Oracle缓冲区中已经保存了所需数据块的一个随机散布的子集。同样它也无法说明I/O请求可以通过中间缓存实现而不是通过实际读取物理磁盘实现。

在Oracle 9i中，优化器引入了一个新的功能，称为CPU成本计算(CPU costing)。您可以将对单数据块和多数据块I/O请求的典型反应时间存储到数据库中，同时存储一个关于多数据块请求的典型大小的指示器，优化器将自动地把这些因子引入到成本方程中。优化器还可以将CPU操作的数目(比如将日期列与常量进行比较)转换为CPU时间，并将其引入到成本方程中。这些改进将确保优化器能够更好地估计表扫描的成本，并产生更加符合实际的执行计划，以及减少像本节前面提到的散列连接和嵌套循环连接那样的异常。

在Oracle 10g中，引入了一个新的功能，称为脱机优化器(Offline Optimizer)。基于脱机优化器可以产生并存储关键的统计信息(以profile的形式)，这些信息能够帮助联机优化器(Online Optimizer)处理有关数据分布的问题。实际上，可以通过添加一个提示，如“此处，您获得的信息将是您期望的15倍”，来加强某个查询。Oracle 9i和Oracle 10g都在对象层次上收集缓存统计信息，但是，从未来的角度看，Oracle 10g拥有一对隐含参数，看起来似乎能够引发高精度和高速缓存识别(cache-aware)的计算。如果这一功能能够实现，则优化器基于最近的缓存成功而产生的计划可能会更好地反映实际需要的I/O请求数目。

此外，Oracle 9i和Oracle 10g都通过视图v$sql_plan_statistics和v$sql_plan_statistics_all收集运行时统计信息。理论上讲，这些统计信息能够反馈给优化器，以保证当实际的统计信息和优化器假定的信息区别很大时，优化器能够有第二次机会对查询进行优化。

如果未来的某一天推出Oracle 9i和Oracle 10g的次要版本，您可以直接看到查询的成本并非常自信地将其转换为近似的运行时间，这是因为优化器能够针对您的数据恰如其时地得出在本机上的准确执行计划。(当然，那种因进行中的行为而导致意图每隔5分钟就发生变化的优化器带来的更可能是危险而非助益—— 也就是说，我们更强调优化器的预测性和稳定性，而不是常有失败的“完美”。)

与此同时，为什么我们如此确信成本应该可以用等价的时间表示呢？想知道答案，请参见Oracle 9i Database Performance Funing Guide and Reference Release 2(9.2)(Part A96533)，第9-22页：

According to the CPU costing model:

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处理的时间，然后将总和除以单块读取所花费的时间。也就是说，成本是语句的预计执行时间的总和，以单块读取时间单元的形式来表示。

CPU速度报告

尽管手册表明CPU速度cpuspeed以每秒的周期数进行报告，但在语句中可能会出现两个错误。

其中一个较为简单的错误就是显示的变量表明度量单位可能是按每秒百万个周期进行(也就是说，CPU的速度以MHz为单位)。即使这样，数字总是达不到期望值—— 我们在各种不同的机器上进行测试，因子在5~30之间，结果都是如此。

另外一个比较微妙的错误就是实际上按每秒进行几百万个标准Oracle操作进行值的度量，其中，标准Oracle操作是一些特殊的子程序，可能会烧掉CPU。(10.2中的10053 trace file为此提供了一个验证示例)。

无论数字表示每秒的周期数还是每秒的操作数，它们之间的差异仅仅是简单的比例因子。使用cpuspeed的机制并没有改变。

为什么Oracle会为成本计算选择这样一种古怪的时间单位，而不是简单地以厘秒(1/100秒)为单位进行计算？这纯粹是为了满足向后兼容性。在Oracle 8i(包括9i，如果不启用CPU成本计算)中，成本仅仅是对I/O请求数目进行计算，这种计算方式在单数据块和多数据块I/O之间并没有区别。因此，为了向后兼容，从Oracle　8i升级到Oracle 9i时，如果新的代码以单块读取时间为单位来报告时间，那么典型的(轻量级的，基于索引的)OLTP查询的成本所产生的数字并不会改变太多。

由这个公式还能够得出，如果启用了CPU成本计算，全表扫描的成本大约会上升到一个倍数(mreadtim/sreadtim)。因此具有CPU 成本计算的Oracle 9i将会比Oracle 8i更加倾向于使用索引访问路径，这是因为Oracle 9i能够(准确地)意识到多块读取花费的时间要比单块读取花费的时间长。如果准备将Oracle 8i升级到Oracle 9i(或从Oracle 8i升级到Oracle 10g)，则从回归测试那天开始就一定要确认是否启用了CPU成本计算—— 这可能会给您带来惊喜。

当分析这个公式时的最后一个考虑是，并没有明确地提到与花费在I/O上的时间有关的任何组件，这些I/O可能由归并连接、散列连接或排序产生。在以上3种情况中，Oracle用直接路径进行写入和读取，大小通常与正常的多块读取大小无关—— 因此，使用mreadtim或sreadtim似乎根本不合适。

1.3 变换和成本计算

在优化中有一个非常重要的方面常常被忽略，从而容易导致混乱，特别是当同时使用不同版本的Oracle时。在进行任何成本计算之前，Oracle可能会将SQL语句变换为等价的语句—— 可能并不是合法的SQL语句—— 并为等价的语句进行成本计算。

根据Oracle版本的不同，变换可以分为3类，分别为a)不可以进行，b)如果可能的话总是可以进行，和c)可以进行，但由于成本过高而放弃。例如，参见如下的SQL语句块(全部脚本view_merge_01.sql可以在本章的联机代码包中找到)：

create or replace view avg_val_view

as

select

id_par,

avg(val) avg_val_t1

from t2

group by

      id_par

;

select

       t1.vc1,

avg_val_t1

from

t1,

avg_val_view

where

t1.vc2 = lpad(18,32)

and avg_val_view.id_par = t1.id_par

;

需要注意的是，avg_val_view是表t2的一个聚集视图(aggregate view)。然后查询将t1与产生该聚集视图的列上的t2相连接。在这种情况下，Oracle可以使用以下两种机制之一来得到正确的结果集：实例化这个聚集视图然后将其与表t1进行连接；将视图定义合并到查询中并变换为其他形式。对于Oracle 9i，存在如下两种可能的执行计划：

Execution Plan (9.2.0.6 instantiated view)

----------------------------------------------------------

SELECT STATEMENT ptimizer=CHOOSE (Cost=15 Card=1 Bytes=95)

HASH JOIN (Cost=15 Card=1 Bytes=95)

TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)

VIEW OF 'AVG_VAL_VIEW' (Cost=12 Card=32 Bytes=832)

SORT (GROUP BY) (Cost=12 Card=32 Bytes=224)

TABLE ACCESS (FULL) OF 'T2' (Cost=5 Card=1024 Bytes=7168)

Execution Plan (9.2.0.6 merged view)

----------------------------------------------------------

SELECT STATEMENT ptimizer=CHOOSE (Cost=14 Card=23 Bytes=1909)

SORT (GROUP BY) (Cost=14 Card=23 Bytes=1909)

HASH JOIN (Cost=8 Card=32 Bytes=2656)

TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=76)

TABLE ACCESS (FULL) OF 'T2' (Cost=5 Card=1024 Bytes=7168)

从以上这两个执行计划中可以看出，上面的示例首先令Oracle对表t2进行聚集并将其与表t1连接在一起，但是它也允许Oracle首先连接这两个表然后进行聚集。合并视图的“等价”代码类似如下所示：

select

t1.vc1,

avg(t2.val)

from

t1, t2

where

t1.vc2 = lpad(18,32)

and t2.id_par = t1.id_par

group by

t1.vc1, t1.id_par

;

那么，这两种方式哪一种更好，优化器又会选择哪种执行计划呢？这两种方式哪一种更好的答案取决于数据的分布。

●       如果存在一种非常有效的方式能够从t1中读取数据并传给t2，且t2中针对t1的每一行都对应着两行，向t2中的每一行所添加的额外数据量又非常小，那么首先进行连接然后进行聚集可能是一种较好的方法。

●       如果不存在一种有效的方式能够从t1中读取数据并传给t2，而且t2中针对t1的每一行都对应着很多行，向t2中所添加的额外数据量又非常大，那么首先进行聚集然后进行连接则是一种较好的方法。

●       我们只能介绍在两种极端情况下应该选择哪一种方式，并不能给出当介于这两种情况之间时究竟该选择哪种方式。但是优化器却能够给出一个相当好的选择。

优化器会选择哪种执行计划的答案取决于Oracle的版本。如果运行的是Oracle 8i，那么Oracle将直接对视图进行聚集然后执行连接—— 不考虑其他方式。如果运行的是Oracle 9i，Oracle将打开视图并连接然后进行聚集—— 也不考虑其他方式。如果运行的是Oracle 10g，Oracle将首先分别计算这两种方式的成本，然后选择成本较低的方式。可以通过执行脚本文件view_merge_01.sql，并设置event 10053(后面的章节将会介绍)来产生一个关于优化器成本计算的追踪文件(trace file)。

优化代码的演化

当使用不同版本的Oracle时，您会注意到某些机制的示例，这些机制能够通过隐含参数在系统或者会话层次上启用或者禁用；此外还存在许多机制，可以通过SQL层次上的提示来启用或者禁用。

优化器代码演化的通用路径如下所示：在首次发布时禁用，通过未公开的参数进行隐藏；在第二次发布时悄悄地启用但并没有计算成本；第三次发布时正式启用并计算成本。

在上面的示例中，优化器根据隐含参数_complex_view_merging的值来确定打开聚集视图和将其合并到查询的其余部分的先后顺序，在Oracle 8i中，该参数的默认值为false，但是在Oracle 9i以后的版本中，该参数的默认值为true。可以通过修改该参数的值来强迫Oracle 8i执行复杂视图合并—— 尽管您可能会发现在某些情况下还需要使用merge()提示来执行合并操作。同样，也可以通过修改该参数的值以使得Oracle 9i和Oracle 10g不执行复杂视图合并操作，但是，在某些情况下使用no_merge()提示来完成这一目的更为合理—— 前面演示的获取较优的执行计划的过程中就使用了该提示。

很长时间以来，优化器中还有很多功能能够在对查询进行优化之前执行某些其他的操作—— 谓词推进(predicate pushing)、非嵌套子查询(subquery unnesting)以及星型变换(star transformation)(这也许是查询变换最生动的示例)。通过传递闭包(transitive closure)产生谓词已经有多年的历史了，从约束中产生谓词也是多个不同Oracle版本中都存在的功能。所有这些(此处并没有提及显式的查询重写功能)，还有其他一些没有注意到的操作，使得我们如果不对SQL进行深入细致的分析，要准确地理解复杂SQL的含义将更加困难—— 可以通过10053 trace对SQL进行分析。

幸运的是，由于explain plan所提供的信息已经足以表明变换已经发生，因此通常无需对SQL进行详细分析。通常情况下可以采用一对提示或者通过检查与优化器相关的参数来分析变换是强制的还是可选的，是否计算了成本以及可以进行何种程度上的控制。

1.4 所见未必即所得

当我们倾力分析基于成本的优化器的工作原理时，会遇到一个问题，即所见到的与所得到的并非总能保持一致。

操作复杂度可以分为以下3种不同的层次：

●       首先，执行计划能够在运行时实现优化器的意图，并基于该模型计算成本。

●       其次，执行引擎启动并执行优化器指定的模型—— 但是实际的机制并不总是与模型相一致(有时候模型并没有很好地描述实际发生的事情)。

●       最后，在某些情况下，随着输入数据分布的不同，执行模型所需的资源也有很大程度上的不同。

换句话说，优化器的执行计划可能并不完全是运行时的执行路径，不当的数据选择可能会影响到运行时执行路径的速度。