ORACLE 12C 优化器的一些新特性总结（二）

Oracle 12c 数据库在优化器方面确实做出了很大进步。在 Oracle 12c 数据库众多特性中，自适应查询优化是较大的功能变化了。它使优化器能够对执行计划进行实时调整。当现有的统计信息不足以产生一个优化的计划，它能够及时的找到导致更佳的统计信息的额外信息。自适应查询优化包括两个方面：

自适应计划，它着重于改善一个查询的初次执行；

自适应统计信息，它为后续的执行提供了额外的信息。

( 自适应查询优化功能的组件 )

以下对自适应统计信息部分进行总结介绍。

自适应统计信息

在 oracle 中，优化器所选择的执行计划的质量主要取决于可用的统计信息的质量。然而，但有些查询谓词变得过于复杂导致无法单独依赖于基表的统计信息，这就是为什么我们常常遇见一些复杂的 SQL 在统计信息没有问题的情况下而仍会选择较差的执行计划的原因，而现在在 oracle  12c 中优化器能够用自适应统计信息来进行增补。

自适应统计信息主要包含三个方面：动态采样（动态统计信息），自动重优化和 SQL 计划指令。

1 、动态统计信息

当一个 SQL 语句在执行编译时， 统计信息缺失、过期或者不足时， CBO 优化器选择的执行计划就有可能不是最优的甚至是最差 的， 动 态取样正是为了补偿不充足的统计信息，如果不这么做，这样的信息可能导致非常糟糕的计划。

一表的统计信息都缺失的情况下，优化器会先在这些表上使用动态取样来收集基本的统计信息。这种情况下收集的统计信息在质量和完整性上都不如使用 DBMS_STATS 包收集到的信息。数据库中默认动态采样的级别为 2 ，便是以上这种情况。

动态采样的级别

在 Oracle 12c 数据库中 , 动态取样被进行加强改造为动态统计信息，动态统计信息使优化器在现有的统计信息上得到更加精准的基数估算。这不仅仅是针对某个单表的访问，而且也包括针对连接和分组计算。

初始化参数 OPTIMIZER_DYNAMIC_SAMPLING 引入了新的取样级别 11 。

11 级表示让 oracle CBO 能够自行决定是否为 SQL 语句选择使用动态统计信息，即使相关的表已存在统计信息。 CBO 是基于现有的统计信息， SQL 所用谓词的复杂度以及预期的执行时间来作出是否使用动态统计信息的决定。

在以前， CBO 在某些情况下有时在生成执行计划时会只是做出猜测评估，而现在可以通过动态统计信息的得到更精准的计算。

动态的统计信息让执行计划的评估更加准确，在没有动态采样的情况下，优化器只能通过猜测：

动态统计 信息 最大的优点是，在优化器选择执行计划时，对统计信息缺失或者统计不够准确的对象，能够动态地收集统计信息，从而获得相对好的执行计划， 并且 12c 中优化器还能够自行 将查询的结果将会作为动态统计信息保留在缓存中 ，供其他 SQL 来共享这些统计信息。

但是， 我们知道任何新功能都是一把双刃剑，有优点也有缺点，对于动态统计也一样。 当级别设置为 11 时，动态取样启用的频率很可能超过以往。并且 使硬解析时候的解析时间可能变得更长，而且如果大量的动态收集操作发生时可能影响到数据库全体性能。

2 、自动重优化

自动重优化和之前所介绍的自适应计划不同的是，自适应计划是在初次执行之前给出正确的执行计划，而自动重优化是在初次执行之后，在以后的执行中去修改执行计划。

在一个 SQL 语句的初次执行结束之时， CBO 会利用初次执行期间收集到的信息来决定是否需要自动重优化。如果执行的结果和优化器原有的估计值相差太大，则 CBO 会在下次执行寻求替换的计划。 CBO 会利用前一次执行收集到的信息来帮助确定这个替换计划。 CBO 可能将一个查询重新优化好几次，每次都学习并且进一步改善计划。

Oracle 12c 数据库只要有以下几种重优化的方式。

2.1 、统计信息反馈

统计信息反馈，它自动为那些反复执行的具有基数估算误差的查询改善计划。在一个 SQL 语句的首次执行期间 CBO 生成执行计划时决定是否应该为游标启动统计信息反馈监视器。

统计信息反馈在以下情况下被启用：缺失统计信息的表，表上有多个合取或者析取谓词 ( 指 AND 或者 OR 连接的谓词 ), 谓词包含有复杂操作，使得 CBO 不能准确估算基数。

在查询结束之时， CBO 将它原来的基数估算和在执行期间观测到的实际基数进行比较，如果估算值和实际值有较大的差异，它会将正确的值存储起来供后续使用。如果查询再次执行，优化器会使用纠正过的基数估算值，而不是它原先的估算值，来确定执行计划。如果它发现初始的估算值是正确的，则不会采取任何额外的措施。

以下通过例子来演示统计信息反馈：

   可以看出首次生成的执行计划中 计算出的基数（ E-Rows ）与 实际 的基数（ A-Rows ）的因数 为 8 （ 63 ： 500） ，这是存在差 误 的 ，这说明这里执行计划还不是最准确的。

在初次执行之后，优化器将它原来的基数估算和计划中的操作实际返回的行数进行比较。估计值和实际返回的行数有很大的差别，所以这个游标被标记为 IS_REOPTIMIZIBLE （可重优化）并且不会被再次使用。 IS_REOPTIMIZIBLE 属性指明这个 SQL 语句应该在下一次执行的时候被硬解析，所以优化器能够使用在初次执行时记录下来的统计信息来确定一个更佳的执行计划。

在第二次执行，优化器使用了来自初次执行的统计信息来确定一个具有不同连接顺序的新计划。在生成执行计划的过程中对统计信息反馈的使用情况被注明于执行计划下面的备注部分。

在第二次使用基数反馈后，新计划没有标识为 IS_REOPTIMIZIBLE ，所以它将被这个 SQL 语句的所有后续执行所使用。之后，优化器关闭了统计信息反馈的监视。

2.2 、性能反馈

oracle  12 c 自动重优化的另一种形式为性能反馈，性能反馈主要是在自适应模式下开启自动并行度选择功能，从而改善重复执行的 SQL 的效率 。

当自动并行度被启用，在一个 SQL 语句的首次执行过程中，优化器会决定语句是否应该在并行模式下执行；如果是，应该使用什么并行度。在初次执行结束时，优化器选择的并行度，和根据语句初次执行期间的实际性能统计信息（例如 CPU 时间）计算出来的并行度，被加以比较。 如果两个值有显著差别，那么语句被标识为可重优化， 初次执行的性能统计信息被作为反馈存储起来，以帮助为后续的执行计算出一个更加合适的并行度。

Oracle 在 11gR2 引进了 PARALLEL_DEGREE_POLICY 初始化参数 时 当被设置为 AUTO 时，能使并行度 选择 自动化。 该 参数默认值为 MANUAL 。

Oracle 12cR1 为该参数增加了 ADAPTIVE 设置 , 该值类似于 AUTO ， 但 包括了性能反馈。

在 PARALLEL_DEGREE_POLICY 调整为 ADAPTIVE 后，故意将一个并不需要那么大并行的语句开启到 128 并行度。

第二次执行时性能反馈机制已经失效， CBO 自动为该语句选择并行度为 1 .

3 、 SQL 计划指令

SQL 计划指令是根据通过自动重优化学习到的信息所创建出来的。在 oracle 12C 的版本开始， oracle 推出了 sql 计划指令（ SQL Plan directives 简称 SPD ）功能，保存为了以后生成最优执行计划的一些指令和附加信息到字典表中，达到持久化的目的。 SQL 计划指令针对 SQL 的一些额外的信息 , 优化器可用来生成一个更优的执行计划。

关于 SQL 计划指令，我在文章中《 ORACLE 12C 优化器的新利器 - SQL Plan Directives （ SQL 执行计划指令）介绍》已做详细的介绍。

http://note.youdao.com/noteshare?id=c30735781a4f69041bcd3c62537cee98

4 、自适应查询优化有关初始化参数

有几个新的初始化参数可以管理 Oracle 12c 数据库的优化器及其新特性。下面是关于这些新参数的详细介绍。

OPTIMIZER_ADAPTIVE_FEATURES

对于新的自适应查询优化功能的使用，包括自适应连接， SQL 计划指令的创建和使用，是受 OPTIMIZER_ADAPTIVE_FEATURES 参数控制的。这个参数的缺省值和 OPTIMIZER_FEATURES_ENABLE(OFE) 相关。如果该参数被设置为 12.1.0.1 或者更高，那么 OPTIMIZER_ADAPTIVE_FEATURES 被设为 TRUE, 所有的自适应查询优化功能将会启用。如果 OFE 被设为比 12.1.0.1 更低，那么 OPTIMIZER_ADAPTIVE_FEATURES 将会被设为 FALSE, 所有的自适应查询优化功能都不会启用。

OPTIMIZER_ADAPTIVE_REPORTING_ONLY

在开启自适应查询优化后，执行计划的自适应或者中途变动会让人担心是否比较危险。为了更好地理解多少 SQL 语句会受到新的自适应计划的影响，你可以将自适应计划开启为只报告模式，方法是将 OPTIMIZER_ADAPTIVE_REPORTING_ONLY 设置为 TRUE( 缺省值是 FALSE) 。在这个模式下，启用自适应连接方法所需的信息被收集，但是不会有任何修改计划的行动。这意味着缺省的计划总是会被使用，但计划在非报告模式下会如何调整的信息也会被收集。

OPTIMIZER_DYNAMIC_SAMPLING

OPTIMIZER_DYNAMIC_SAMPLING 参数从 9i 就开始有了，在 12c 它有了新的级别 11, 这个级别控制动态统计信息的生成。当设置为级别 11 时，优化器会自动确定哪些语句会受益于动态统计信息，即使所有的对象已经有了统计信息。

5 、自适应查询优化的各种坑

还是那句话， 任何新功能都是一把双刃剑 ，给我们带来便利的同时会挖下不少坑来慢慢填。

结论

优化器是 Oracle 数据库最吸引人的部件之一，这是因为它的复杂性。它的目的是为每个 SQL 语句确定最高效的执行计划。在 oracle  12c 的优化器自适应查询优化的新特性中我们更可以看到 oracle 在优化器的质量追求上巨大努力。越来越智能的优化器能减少更多的 SQL 性能问题，但是正因为优化器是个很复杂的组件，在初期总会遭遇各种隐患。因此在升级更新换代之前更需要我们了解深入，防范于未然。