Oracle 12C优化器的巨大变化,上生产必读(上)

Oracle 12C优化器的巨大变化，上生产必读（上）序言优化器是Oracle数据库最吸引人的部件之一，因为它对每一个SQL语句的处理都必不可少。

优化器为每个SQL语句确定最有效的执行计划，这是基于给定的查询的结构，可用的关于底层对象的统计信息，以及所有与优化器和执行相关的特性。

随着每个新版本的发布，优化器都会进化，利用新功能以及新的统计信息来生成更好的执行计划。

随着对查询优化的新的自适应方法的引入，Oracle 12c数据库把这种进化更推上了一个台阶。

这份白皮书介绍了在Oracle 12c数据库中与优化器和统计相关的所有新特性并且提供了简单的，可再现的例子，使得你能够更容易地熟悉它们。

它还概括了已有的功能是如何被增强以改善性能和易管理性。

优化器和统计信息新特性1、自适应查询优化到目前为止，Oracle 12c数据库中最大的变化是自适应查询优化。

自适应查询优化是这样的一组功能，它使得优化器能够对执行计划进行实时调整，并且发现能够导致更佳的统计信息的额外信息。

当现有的统计信息不足以产生一个优化的计划，这种新方法是极其有用的。

自适应查询优化包括两个方面：自适应计划，它着重于改善一个查询的初次执行；自适应统计信息，它为后续的执行提供了额外的信息。

(图1. 自适应查询优化功能的组件)2、自适应计划自适应计划使得优化器能够延迟产生一个语句的最终计划，直到执行的时候才决定。

优化器在它所选择的计划（缺省计划）中植入统计收集器，从而在运行的时候，它能够判断自己的基数估算与计划的操作所实际看到的行数是否有很大的偏差。

如果有显著的区别，那么这个计划或者计划的一部分在SQL语句的首次执行就能够被自动调整来避免不理想的性能。

3、自适应的连接方式通过为计划中的某些分支预先确定多个子计划，优化器能够实时调整连接方式。

例如，在图2中优化器的初始计划（缺省计划）为order_items 和 product_info 之间的连接选定的是嵌套循环连接，通过对product_info表的索引读取。

另一个可选的子计划也同时被确定，它允许优化器将连接方式切换到哈希连接。

在候选计划中product_info是通过全表扫描来读取的。

在执行的时候，统计收集器收集了关于这次执行的信息，并且将一部分进入到子计划的数据行缓存起来。

在这个例子中，统计收集器监控并缓存了对order_items的全表扫描。

基于它在统计收集器中看到的信息，优化器会最终确定采用哪个子计划。

在这个例子中，哈希连接被选为最终计划，因为来自order_items表的行数大于优化器最初的估计。

在优化器选择了最终计划之后，统计收集器停止收集统计信息以及对数据行的缓存，而仅仅是传递数据。

在子游标随后的执行中，优化器禁止了数据缓存，并且选择了同一个最终计划。

目前的优化器能够从嵌套连接切换到哈希连接，反之亦然。

可是，如果初始选中的连接方法是排序合并连接，则自适应不会发生。

(图2. 自适应执行计划确定Order_items 和 Prod_info 表之间的连接)在缺省情况下，explain plan命令只会显示优化器选定的初始（缺省）计划。

而DBMS_XPLAN.DISPLAY_CURSOR只显示查询所用的最终计划。

(图3. Explain plan 和 DBMS_XPLAN.DISPLAY_CURSOR 为图2的查询例子所输出的计划)为了看到自适应计划中所有的操作，包括统计收集器的位置，你必须在DBMS_XPLAN函数中指定额外的格式参数’+adaptive’。

在这个模式下，id栏会出现一个额外的(-)记号，指明在计划中未被采用（非激活）的操作。

在ORACLE企业管理器(OEM)中的SQL监控工具总是显示完整的自适应计划，但是并没有指明在计划中的哪些操作是非激活的。

(图4. 在DBMS_XPLAN.DISPLAY_CURSOR中使用’+adaptive’格式参数得到的完整自适应计划)V$SQL中增加了一个新的列(IS_RESOLVED_ADAPTIVE_PLAN)来指明一个SQL语句是否有自适应计划，以及该计划是否已经完全被确定。

如果IS_RESOLVED_ADAPTIVE_PLAN被设置为’Y’, 这意味着计划不仅是自适应的，而且最终计划已被选定。

可是，如果IS_RESOLVED_ADAPTIVE_PLAN 被设置为’N’, 这指明了选定的计划是自适应的，但是最终计划仍未被确认。

’N’值仅仅在一个查询的初始执行阶段中可见，在此之后，自适应计划的这个值总是为’Y’。

对于非自适应计划这个列被设置为NULL。

你也可以通过将初始化参数OPTIMIZER_ADAPTIVE_REPORTING_ONLY设置为TRUE(缺省值是FALSE)，从而把自适应连接方式置于报告模式。

在这个模式下，开启自适应连接方式所需的信息会被收集，但是改变计划的任何动作都不会发生。

这意味着缺省计划总是会被采用，但是关于计划在“非报告”模式下会如何调整的信息将被收集。

这个信息可以在自适应计划的报告中被查看，当你用额外的格式参数’+report’显示计划的时候就可以看到。

(图5. 在DBMS_XPLAN.DISPLAY_CURSOR中使用’+report’格式参数所显示的完整自适应报告)4、自适应并行分配方法当一个SQL语句以并行模式运行时，某些特定操作，例如排序，聚合和连接，它们要求在执行语句的并行服务进程之间重新分配数据。

优化器所用的分配方法取决于操作类型，涉及到的并行服务进程数，以及预期的行数。

如果优化器对行数估算不准确，那么选中的分配方法就可能不理想，结果某些并行服务进程就可能得不到充分利用。

随着新的自适应分配方法”混合型哈希”(HYBRID HASH)的引入，优化器可以将分配方法的确定延迟到执行的时候才确定，此时它对于涉及到的数据行数就有了更多的信息。

一个统计收集器被插入到操作的前面，如果缓存的数据的实际行数比阈值小，则分配方法将从哈希(HASH)切换到广播(BROADCAST)。

然而，如果缓冲的行数达到了阈值，则分配方法将会是哈希(HASH)。

阈值的定义为并行度的两倍。

图6显示了SQL监控工具中的一个执行计划的例子，它是一个以并行模式执行的EMP和DEPT表之间的连接。

一组并行服务进程（生产者，即粉红色图标）扫描两个表并且将数据送给另一组并行服务进程（消费者，即蓝色图标），该组进程是连接的真正执行者。

优化器决定采用混合型哈希(HYBRID HASH)的分配方法。

在这个连接中访问的第一个表是DEPT表。

来自DEPT表的数据行被缓存在统计收集器中，见计划的第六行，直至阈值被超越，或者最后一行被获取。

在那时优化器将会决定采用何种分配方法。

(图6. SQL监控工具中的一个EMP和DEPT表之间的连接的执行计划，它使用了自适应分配方法)我们假定这个例子中的并行度被设置为6, 从DEPT表扫描返回的行数是4, 阈值则是12行（2X6）。

既然还未达到阈值，从DEPT表返回的4行将会被广播到负责完成连接的6个并行服务进程，结果计划中的分配步骤所处理的行数是是24行（4X6），见图7。

既然对于来自DEPT表的数据行采用了广播(BROADCAST)的分配方法，来自EMP表的数据行将会通过循环制(ROUND-ROBIN)的方法进行分配。

这意味着来自EMP表的一行数据将会轮流发送给6个并行服务进程中的一个，直至所有的数据行都分配完毕。

(图7. 混合型哈希分配法使用广播的分配方式，因为未达到阈值)可是，如果这个例子的并行度被设置为2, 而扫描DEPT表返回的行数为4, 则阈值为4行(2X2)。

既然已经达到了阈值，从DEPT表返回的4行数据将会以哈希(HASH)的方式分配到负责完成连接的2个并行服务进程, 结果计划中的分配步骤所处理的行数是是4行（见图8）。

既然来自DEPT表的数据行采用了哈希(HASH)分配法，来自EMP表的数据也会以哈希(HASH)方法进行分配。

(图8. 混合型哈希分配法使用哈希的分配方式，因为已达到阈值)5、自适应统计信息优化器所确定的执行计划的质量取决于可用的统计信息的质量。

然而，有些查询谓词变得过于复杂，以至于无法单独依赖于基表的统计信息，而现在优化器能够用自适应统计信息来进行增补。

在一个SQL语句的编译过程中，优化器会判断已有的统计信息是否足以产生一个好的执行计划，或者它该考虑使用动态取样。

动态取样是为了补偿缺失或者不充足的统计信息，如果不这么做，这样的信息可能导致非常糟糕的计划。

在查询中的一个或者多个表的统计信息都缺失的情况下，优化器在优化语句之前就会在这些表上使用动态取样来收集基本的统计信息。

这种情况下收集的统计信息在质量（因为是取样）和完整性上都不如使用DBMS_STATS包收集到的信息。

在Oracle 12c数据库中, 动态取样被强化为动态统计信息。

动态统计信息允许优化器强化现有的统计信息以获取更加精确的基数估算，不仅仅是为单表的访问，而且也包含连接和分组(GROUP BY)谓词。

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

11级使得优化器能够自动为任何SQL语句使用动态统计信息，即使所有基本的表统计信息都已经存在。

优化器做出使用动态统计的决定，是基于所用谓词的复杂性，和已经存在的基础统计信息，以及预期的SQL语句总执行时间。

例如，之前的优化器在某些情况下会使用猜测的方法，比如带有LIKE谓词和模糊匹配的查询，而现在则会启用动态统计信息。

(图9. 当 OPTIMIZER_DYNAMIC_SAMPLING 被设为级别 11，动态取样会被使用，而不是猜测)在这些新的条件下，当级别设置为11时，动态取样启用的频率很可能超过以往。

这会增加语句的解析时间。

为了将对性能的影响减到最低，动态取样查询的结果将会作为动态统计信息保留在缓存中，允许其他SQL语句来共享这些统计信息。

SQL计划指令(下面将会更详细地讨论)也会利用这种级别的动态取样。

7、自动重优化和自适应计划不同的是，在初次执行之后，自动重优化在随后的执行中修改计划。

在一个SQL语句的初次执行结束之时，优化器利用初次执行期间收集到的信息来决定自动重优化是否值得。

如果执行的信息和优化器原有的估计值有显著区别，则优化器会在下次执行寻求替换的计划。

优化器会利用前一次执行收集到的信息来帮助确定这个替换计划。

优化器可能将一个查询重新优化好几次，每次都学习并且进一步改善计划。

Oracle 12c数据库支持多种不同形式的重优化。

统计信息反馈(以前称为基数反馈，cardinality feedback)是重优化的一种形式，它自动为那些反复执行的具有基数估算误差的查询改善计划。

在一个SQL语句的首次执行期间，优化器生成了一个执行计划，并且决定是否应该为游标启动统计信息反馈监视器。