序言：优化器是Oracle数据库最引人入胜的部件之一，因为它对每一个SQL语句的处理都必不可少。优化器为每个SQL语句确定最有效的执行计划，这是基于给定的查询的结构，可用的关于底层对象的统计信息，以及所有与优化器和执行相关的特性。
本文来自Oracle 白皮书翻译（译者：苏旭辉 newkid），介绍了在Oracle数据库12c第二版中与优化器和统计信息相关的所有新特性并且提供了简单的，可再现的例子，使得你能够更容易地熟悉它们，尤其是当你从早先的版本进行迁移的时候。它还概括了已有的功能是如何被增强以改善性能和易管理性。
有些Oracle优化器特性已经被从本文移除，并在它们自己的文章中被讨论。具体来说，它们是：
- 优化统计信息和优化统计信息顾问
- SQL计划管理
- 近似查询处理
为了得知Oracle优化器的全貌，我们建议你结合参考文献部分列出的相关文件来阅读本文。
一、自适应查询优化
到目前为止，Oracle 12c数据库中最大的变化是自适应查询优化。自适应查询优化是这样的一组功能，它们使得优化器能够对执行计划进行实时调整，并且发现能够导致更佳的统计信息的额外信息。当现有的统计信息不足以产生一个最佳的计划，这种新方法是极其有用的。自适应查询优化包括两个方面：
自适应计划，它着重于改善一个查询的执行；
自适应统计信息，它利用额外的信息来改善查询执行计划。
(图1:新的自适应查询优化功能的构成组件)
Oracle数据库12c第二版默认启用的自适应功能与Oracle数据库12c第一版不同。详细信息请参见下面的“初始化参数”部分。
二、自适应计划
优化器在某些条件下会选择自适应计划; 例如，当查询包括连接和复杂谓词，使得准确地估计基数变得很困难。自适应计划使得优化器能够把一个语句的计划推迟到执行的时候才确定。优化器在它所选择的计划（缺省计划）中植入统计收集器，从而在运行的时候，它能够判断基数估算与计划的操作所实际看到的行数是否有很大的偏差。如果有显著的区别，那么这个计划或者计划的一部分就会被自动调整，以避免不理想的性能。
三、自适应的连接方式
通过为计划中的某些分支预先确定多个子计划，优化器能够实时调整连接方式。例如，在图2中优化器的缺省计划为orders(订单)和 products(产品)表之间的连接选定的是嵌套循环连接，通过对products表的索引读取。另一个可选的子计划也同时被确定，它允许优化器将连接方式切换到哈希连接。在候选计划中products是通过全表扫描来读取的。
在初次执行的时候，统计收集器收集了关于这次执行的信息，并且将一部分进入到子计划的数据行缓存起来。
优化器会确定要收集哪些统计信息，以及如何根据统计的不同值来确定计划。它会算出一个“拐点”，两个计划选项在这个值是一样好的。
例如，如果当orders表的扫描产生的行数少于10行，则嵌套循环连接是最佳，当 orders 表的扫描产生的行数多于10行，则哈希连接是最佳，那么这两个计划的拐点就是10。优化器会算出这个值，并且配置一个缓存统计收集器，使得它缓存并且计数至10行为止。如果扫描产生了至少10行，那么连接方式就被确定为哈希连接；否则，它就被确定为嵌套循环连接。在图2中，统计信息收集器正在监控和缓存来自orders表全扫描的数据行。基于从统计信息收集器中看到的信息，优化器会决定使用哪个子计划。
在这个例子中，哈希连接被选中，因为来自orders表的行数大于优化器最初的估计。
(图2:orders 和 products 表之间的连接的自适应执行计划。左边是缺省计划，右边是选中的计划)
优化器能够从嵌套连接切换到哈希连接，反之亦然。可是，如果初始选中的连接方法是排序合并连接，则自适应不会发生。
在缺省情况下，explain plan命令只会显示优化器选定的初始（缺省）计划。而DBMS_XPLAN.DISPLAY_CURSOR函数显示的是查询实际使用的计划。
(图3: Explain plan 和 DBMS_XPLAN.DISPLAY_CURSOR 为图2所示的情形所输出的计划)
为了看到自适应计划中所有的操作，包括统计收集器的位置，你必须在DBMS_XPLAN函数中指定额外的格式参数'adaptive'。在这个模式下，计划的id栏会出现一个额外的“-”记号，指明在计划中未被采用（非激活）的操作。
(图4: 在DBMS_XPLAN.DISPLAY_CURSOR中使用'ADAPTIVE'格式参数得到的完整自适应计划)
如果在“Plan”下拉框中选择“Full”，SQL监控工具(SQL Monitor)将显示所有的操作。计划的非活动部分变灰（参见图5）。如果点击“Plan Note”图标，一个弹出框会被显示，确认该计划是一个自适应计划。
(图5: SQL Monitor显示一个自适应计划)
四、自适应并行分配方法
当一个SQL语句以并行模式执行时，某些操作，例如排序，聚合和连接，它们要求在执行语句的并行服务进程之间重新分配数据。优化器所用的分配方法取决于操作方式，涉及到的并行服务进程数，以及预期的行数。如果优化器对行数估算不准确，那么选中的分配方法就可能不理想，并可能导致某些并行服务进程得不到充分利用。
随着新的自适应分配方法"混合型哈希"(HYBRID HASH)的引入，优化器可以将分配方法延迟到执行的时候才确定，此时它对于涉及到的数据行数就有了更多的信息。一个统计收集器被插入到操作之前，如果缓存的数据的实际行数比阈值小，则分配方法将从哈希(HASH)切换到广播(BROADCAST)。然而，如果缓冲的行数达到了阈值，则分配方法将会是哈希(HASH)。阈值的定义为并行度的两倍。
图6显示了SQL监控工具中的一个执行计划的例子，它是一个以并行模式执行的EMP和DEPT表之间的连接。一组并行服务进程（生产者，即粉红色图标）扫描两个表并且将数据行送给另一组并行服务进程（消费者，即蓝色图标），该组进程是连接的真正执行者。优化器决定采用混合型哈希(HYBRID HASH)的分配方法。在这个连接中访问的第一个表是DEPT表。来自DEPT表的数据行被缓存在统计收集器中，见计划的第六行，直至阈值被超越，或者最后一行被获取。在那时优化器将会决定采用何种分配方法。
(图6: SQL监控工具中的一个EMP和DEPT表之间的连接的执行计划，它使用了自适应分配方法)
要了解在运行时选择哪个分配方法，查找此信息的最简单方法是查看SQL监控工具中的OTHER列。 此列在PX SEND HYBRID HASH的行中显示一个望远镜图标。当你点击这个图标时，你可以看到运行时使用的分配方法。
(图7: 混合型哈希分配法)
这个对话框中显示的自适应分配方法有三种可能的值：6 = BROADCAST(广播), 5 = ROUND-ROBIN(循环制), 16 = HASH(哈希)。
五、自适应位图索引裁剪
当优化器生成了一个星型转换计划，它就必须选择正确的位图索引组合，以尽可能有效地减少相关的ROWID集合。如果有多个索引，其中的一些可能不会显著地减少ROWID集合，但是仍然会在查询执行期间引入可观的处理成本。自适应计划因此被用来裁剪索引，这些索引无法显著地降低过滤匹配的行数。
在带有adaptive关键字的SQL执行计划中，DBMS_XPLAN.DISPLAY_CURSOR 将会显示自适应的位图裁剪，其方式类似于图3所示的示例。
例如，考虑以下SQL执行计划，它显示出位图索引CAR_MODEL_IDX被裁剪掉：
(图8: 自适应位图索引裁剪的例子)
六、自适应统计信息
优化器所确定的执行计划的质量取决于可用的统计信息的质量。然而，有些查询谓词变得过于复杂，以至于无法单独依赖于基表的统计信息，而现在优化器能够用自适应统计信息来进行增补。
七、动态统计信息
在一个SQL语句的编译过程中，优化器会判断已有的统计信息是否足以产生一个好的执行计划，或者它该考虑使用动态取样。动态取样是为了补偿缺失或者不充足的统计信息，如果不这么做，这样的信息可能导致非常糟糕的计划。在查询中的一个或者多个表的统计信息都缺失的情况下，优化器在优化语句之前就会在这些表上使用动态取样来收集基本的统计信息。这种情况下收集的统计信息在质量（因为是取样）和完整性上都不如使用DBMS_STATS包收集到的信息。
从Oracle数据库 12c第一版开始, 动态取样被强化为动态统计信息。动态统计信息允许优化器强化现有的统计信息以获取更加精确的基数估算，不仅仅是为单表的访问，而且也包含连接和分组(GROUP BY)谓词。并且，从Oracle数据库12c第一版开始, 初始化参数OPTIMIZER_DYNAMIC_SAMPLING引入了新的取样级别11。11级使得优化器能够自动为任何SQL语句使用动态统计信息，即使所有基本的表统计信息都已经存在。优化器做出使用动态统计的决定，是基于所用谓词的复杂性，和已经存在的基础统计信息，以及预期的SQL语句总执行时间。例如，之前的优化器在某些情况下会使用猜测的方法，比如带有LIKE谓词和模糊匹配的查询，而现在则会启用动态统计信息。
缺省的动态取样级别是2, 因此，当级别设置为11时，动态取样启用的频率很可能大大超过以往。这会增加语句的解析时间。为了将对性能的影响减到最低，在Oracle数据库12c第一版中，动态取样查询的结果将会被保留在数据库的服务器结果缓存，从Oracle数据库12c第二版开始会保留在SQL计划指令的知识库中。这就允许多个SQL语句来共享一组由动态取样收集的统计信息。下面将要更详细讨论的SQL计划指令也会利用这种级别的动态取样。
八、自动重优化
在一个SQL语句的首次执行期间，一个执行计划如常被生成。在优化的过程中，有些已知的低质量估算种类（例如，对缺少统计信息的表的估算，或者带有复杂谓词的表的估算）会被做记号，对产生的游标的监控会被开启。如果系统开启了对一个游标的反馈监控，计划中的基数估算就被用来与执行过程中看到的实际基数进行比较。如果估算值被发现和实际基数有显著区别，则优化器会在下次执行寻求可替换的计划。优化器会利用前一次执行收集到的信息来帮助确定这个替换计划。优化器可能将一个查询重新优化好几次，每次都学习并且进一步改善计划。Oracle 12c数据库支持多种不同形式的重优化。
九、统计信息反馈
统计信息反馈(以前称为基数反馈，cardinality feedback)是重优化的一种形式，它自动为那些反复执行的具有基数估算误差的查询改善计划。在一个SQL语句的首次执行期间，优化器生成了一个执行计划，并且决定是否应该为游标启动统计信息反馈监控器。统计信息反馈在如下的情形被启用：缺失统计信息的表，表上有多个合取或者析取谓词(指用AND或者OR连接起的谓词), 谓词包含有复杂操作，使得优化器不能准确估算基数。
在查询结束之时，优化器将它原来的基数估算和在执行期间观测到的实际基数进行比较，如果估算值和实际值有显著差异，它会将正确的值存储起来供后续使用。它还会创建一个SQL计划指令，使得其他的SQL语句也能受益于这次初始执行中学到的信息。如果查询再次执行，优化器会使用纠正过的基数估算值，而不是它原先的估算值，来确定执行计划。如果它发现初始的估算值是正确的，则不会采取任何额外的措施。在第一次执行之后，优化器关闭了统计信息反馈的监控。
图9显示了一个SQL语句受益于统计信息反馈的例子。在这个两表连接的初次执行中，由于customers表上有多个相关的单列谓词，优化器将基数低估了8倍。
(图9: 一个受益于自动重优化的统计信息反馈的SQL语句初次执行的情况)
当估计值和实际返回的行数有很大的差别，这个游标被标记为IS_REOPTIMIZIBLE（可重优化）并且不会被再次使用。IS_REOPTIMIZIBLE属性指明这个SQL语句应该在下一次执行的时候被硬解析，所以优化器能够使用在初次执行时记录下来的统计信息来确定一个更佳的执行计划。
(图10: 在初次执行的统计信息与原有的基数估算有显著差异之后，游标被标识为可重优化)
一个SQL计划指令同样被创建，这是为了确保下次如果在customers表使用了相似的谓词的SQL语句被执行，优化器会注意到这些列之间的相关性。
在第二次执行，优化器使用了来自初次执行的统计信息来确定一个具有不同连接顺序的新计划。在生成执行计划的过程中对统计信息反馈的使用情况被注明于执行计划下面的备注部分。
(图11: 新生成的计划使用来自初次执行的统计信息)
新计划没有标识为IS_REOPTIMIZIBLE，所以它将被这个SQL语句的所有后续执行所使用。
(图12: 新生成的计划标识为不可重优化)
十、性能反馈
重优化的另一种形式为性能反馈，当自动并行度(AutoDOP)在自适应模式下被启用，这会有助于改善重复执行的SQL语句的并行度的选择（参见第25页上的OPTIMIZER_ADAPTIVE_STATISTICS内容）。
（注：关于自动并行度请参见参考文章5 “Oracle数据库12c并行执行基础知识”）
当自动并行度(AutoDOP)在自适应模式下被启用，在一个SQL语句的首次执行过程中，优化器会决定语句是否应该在并行模式下执行；如果是，应该使用哪种并行度。并行度的选择是基于语句的预计性能表现。对于优化器决定并行执行的任何SQL语句，额外的性能监视器同样在初次执行的时候被打开，
在初次执行结束时，优化器选择的并行度，和根据语句初次执行期间的实际性能统计信息（例如CPU时间）计算出来的并行度，被加以比较。如果两个值有显著差别，那么语句被标识为可重优化，初次执行的性能统计信息被作为反馈存储起来，以帮助为后续的执行计算出一个更加合适的并行度。
如果性能反馈被用于一个SQL语句，它会在计划下方的备注部分被注明，如图13所示。
(图13: 一个SQL语句的执行计划，性能反馈发现它串行执行会更好)
文章转自数据和云公众号，原文链接