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

在对 oracle 优化器的学习认知过程中，我逐渐发现优化器是 Oracle 数据库非常神奇的组件，它直接决定着一个数据库产品是否工作是否能高效。优化器为每个 SQL 语句确定最有效的执行计划。 ORACLE 随着每个新版本的发布，优化器都会进化，利用新功能以及新的统计信息来生成更好的执行计划。 Oracle 12c 数据库在优化器方面确实做出了很大进步。

本文将 Oracle 12c 数据库中优化器和统计相关的新特性进行总结，以便能够更容易地熟悉它们。

一、自适应查询优化

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

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

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

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

1 ．自适应计划

自适应计划让 oracle 优化器在解析 SQL 语句后延迟去产生一个最终的执行计划，直到语句执行的时候才决定最终的执行计划。优化器会预先生成多个执行计划并默认分配一个默认的执行计划，并在这个执行计划中植入统计收集器，从而在语句执行的时候，通过统计收集器来判断预先给出的基数估算与执行的实际结果的行数是否存在很大的偏差，如果这个偏差很大，那么，这个执行计划在 SQL 语句的首次执行时就能被自动调整，从而避免错误的执行计划造成的性能问题。

1.1   自适应的连接方式

O racle 12c 优化器在生成执行计划时，在某些执行路径上预先分配多个子的执行计划，即可能是不同的连接方式或不同的访问表的方式，从而使优化器能够实时地去调整连接的方式。

以下通过实验例子来解析这一过程：

分别创建两个表，并插入一定的数据。

执行一条关联查询该两个表的语句，并生成相应执行计划：

在执行计划的提示中会说明这是一条自适应的执行计划，且这是当前情况下所选择的最终执行计划，为了看到自适应计划中所有的操作，包括统计收集器的位置，我们在 DBMS_XPLAN 函数中指定额外的格式参数 '+adaptive' 。在这个模式下， id 栏会出现一个额外的 (-) 记号，指明在计划中未被采用（非**）的操作。

( 在 DBMS_XPLAN.DISPLAY_CURSOR 中使用 '+adaptive' 格式参数得到的完整自适应计划 )

在以上执行计划中显示了非活动的执行计划访问路径，非活动的的执行计划时候 tab 1 表与 tab 2 表进行 hash  join ，而依照目前的统计情况以及实际执行情况来看， tab 1 表与 tab 2 表选择 nest  loop 的方式看来更优，因此优化器选了 nest  loop 的连接方式。

现在我们继续往该两个表中插入更多数据，使当前的执行计划并不符合实际的执行情况：

现在表中有更多的行匹配初始过滤条件，从 1 行的驱动集到 10001 行的驱动集。此 时 ，嵌套循环听起来不那么 优 越。而此 时 ，我 们 依旧使用旧的 统计 信息，我 们 再来看自适 应计 划的工作成果：

SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

在以上我 们 可以看到基于统计信息的基数估计创建了相同的自适应计划，但最终计划利用了 hash 连接，    因 为优 化器检测到不正确的基数估计 在运行 语 句时使用 hash 连接的子计划来代替了 nest  loop   连 接。

并且在以上例子中，统计收集器监控并缓存表的信息。基于在统计收集器中看到的信息，优化器会最终确定采用哪个子计划。在这个例子中，在数据骤然增加的情况下，哈希连接被选为最终计划，因为表的行数大于优化器最初的估计。但是，如果初始选中的连接方法是排序合并连接，则自适应不会发生。

以下将过程使用示意图形式表现：

在 oracle 12c   的 V$SQL 视图中中增加了一个新的列 (IS_RESOLVED_ADAPTIVE_PLAN) 来指明一个 SQL 语句是否有自适应计划，以及该计划是否已经完全被确定。

如果 IS_RESOLVED_ADAPTIVE_PLAN 被设置为 'Y', 这意味着计划不仅是自适应的，而且最终计划已被选定。可是，如果 IS_RESOLVED_ADAPTIVE_PLAN 被设置为 'N', 这指明了选定的计划是自适应的，但是最终计划仍未被确认。 'N' 仅仅在一个查询的初始执行阶段中可见，在此之后，自适应计划的这个值总是为 'Y' 。对于非自适应计划这个列被设置为 NULL 。

同时 oracle  12c 提供了初始化参数 OPTIMIZER_ADAPTIVE_REPORTING_ONLY ，当设置为 TRUE ，把自适应连接方式置于报告模式。在这个模式下，开启自适应连接方式所需的信息会被收集，但是改变计划的任何动作都不会发生。默认情况下该参数是 FALSE 。这个信息可以在自适应计划的报告中被查看，可以在查看执行计划时参数 '+report' 来查看 .

1.2 自适应并行分配方法

当一个 SQL 语句以并行模式运行时，排序，聚合和连接等这些操作要在在执行语句的并行服务进程之间重新分配数据。此时优化器所用的分配方法取决于操作类型，涉及到的并行服务进程数，以及预期的行数。在以往其他版本的 oracle   数据库中，如果优化器对行数估算不准确，那么选中的分配方法就可能不理想，结果某些并行服务进程就可能得不到充分利用。这样无疑是给数据库性能造成不必要的浪费。

一般情况下 sql 执行并行时一般有两种方式：广播和 hash 分发，在 oracle  11g 及以前版本，这两种方法是两个独立的选项，优化器如果选择 a 就不能再执行 b 了。

在 oracle 11g 及以前的版本中， oracle 优化器在生成并行的执行计划时 优化器会根据 hash join 左边和右边估算的 cardinality ，并行度等信息，选择具体何种分发方式，一旦出现 以上所说的估算不准，分配错误，在 sql 开始执行后便无法改变，除非取消执行。

   Broadcast 分发：作为生产者的 PX 进程通过广播的方式，把 hash   join 左边的结果集分发给每 个作为消费者的 PX 进程。一般适用于 hashjoin 左边结果集比右边小得多的场景。

  Hash 分发：把 hashjoin 的左边和右边 ( 两个数据源 ) ，通过同样 hash 函数重新分发，切 分为 N 个工作单元 ( 假设 DoP=N) ，再进行 join ，目的是减少 PX 进程进行 join 操作时，需要连接的数据量。

Oracle 12c 引入了一种称为混合哈希 HYBRID HASH) 的自适应并行分发方法，根据统计收集器的结果，对分发方法的确定延迟到执行时间，此时它对于涉及到的数据行数就有了更多的信息。和自适应连接方法不同，自适应连接方法是在第一次语句第一次执行时，而自适应并行分配方法用于语句的每次执行。

一个统计收集器被插入到操作的前面，如果缓存的数据的实际行数比阈值小，则分配方法将从哈希 (HASH) 切换到广播 (BROADCAST) 。然而，如果缓冲的行数达到了阈值，则分配方法将会是哈希 (HASH) 。阈值的定义为并行度的两倍。

以下通过示例来进行说明：

在以上使用的 tab 1 和 tab2 表之间的连接的 查询 语句 ，我 们 添加一个并行提示：

    我们可以看到使用新的 PX SEND HYBRID HASH 分配方法，允许对分发方法的决定被延迟到执行时间。  

    在以上执行计划中 5-8 及 10-12 行以 PX SEND HYBRID HASH 开始的执行路径为 并行服务进程（生产者），其扫描两个表并且将数据送给另一组并行服务进程（消费者），由第 4 行及 9 行来 px  re ceive ，并由 1-3 行进行并行连接。

  优 化器决定采用混合型哈希 (HYBRID HASH) 的分配方法。在 这 个 连 接中 访问 的第一个表是 TAB1 表。来自 TAB1 表的数据行被 缓 存在 统计 收集器中， 见计 划的第 6 行，直到超 过阀值 或者取得最后一行。在那 时优 化器将会决定采用何种分配方法。

   （ 1 ） 使用哈希的分配方法 ( 缓存行数比阈值大 ) ：   

      在我们以上的例子中，我们的并行度设置成了 16 ，从执行计划话中看，从 TAB1 表扫描返回的行数是 10001 ,   阈值则是 32 行（ 2X16 ）。这里远远大于阀值，从 TAB1 表返回的 10001 行数据将会以哈希 (HASH) 的方式分配到负责完成连接的 16 个并行服务进程，既然来自 TAB1 表的数据行采用了哈希 (HASH) 分配法，来自 TAB2 表的数据也会以哈希 (HASH) 方法进行分配。

（ 2 ） 使用广播的分配方法 ( 缓存行数比阈值小 ) ：   

ORACLE 12C 比以前版本更聪明之处就在于原先给了多重选择，但在某些关键时机能做出最终决定，我们假定以上例子中的并行度被设置为 64, 从 TAB1 表扫描返回的行数是 25 行 , 阈值则是 128 行（ 2X64 ）。此时便未达到阈值，于是从 TAB1 表返回的 25 行将会被广播到负责完成连接的 64 个并行服务进程。

在来自 TAB1 表的数据行采用了广播 (BROADCAST) 的分配方法，来自 TAB2 表的数据行将会通过循环制 (ROUND-ROBIN) 的方法进行分配。这意味着来自 TAB1 表的一行数据将会轮流发送给 6 个并行服务进程中的一个进行连接，直至所有的数据行都分配完毕。

1. 3   小结

  通过总结的过程，我们可以更深入了解 oracle   优化器的某些工作过程， SQL 的性能问题是一个大型数据库永远的主题， oracle  12c   新的查询优化自适应方法使得优化器能够对执行计划作出实时调整，并且发现能够导致更佳的统计信息的额外信息。利用这些信息，和已有的统计信息一起，能够使得优化器对环境有更多的了解，并且允许它每次都选择一个优化的执行计划，这确实实现了一个巨大的飞跃。

以上主要对 oracle  12c   优化器新特性自适应查询优化中的自适应执行计划部分进行总结分析，在后面将对自适应统计信息特性部分以及其他新的特性进行研究总结。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29863023/viewspace-2200163/，如需转载，请注明出处，否则将追究法律责任。