[翻译]Oracle 12c R2优化器白皮书

newkid · 发表于 2017-3-2 10:37

本帖最后由 newkid 于 2017-3-3 09:13 编辑

原文链接：
http://www.oracle.com/technetwor ... edb-12c-1963236.pdf

第一版翻译链接：http://www.kz486.com/thread-1855401-1-1.html

Oracle数据库12c第二版优化器

Oracle白皮书 2016年11月

序言

优化器是Oracle数据库最引人入胜的部件之一，因为它对每一个SQL语句的处理都必不可少。优化器为每个SQL语句确定最有效的执行计划，这是基于给定的查询的结构，可用的关于底层对象的统计信息，以及所有与优化器和执行相关的特性。

这份白皮书介绍了在Oracle数据库12c第二版中与优化器和统计信息相关的所有新特性并且提供了简单的，可再现的例子，使得你能够更容易地熟悉它们，尤其是当你从早先的版本进行迁移的时候。它还概括了已有的功能是如何被增强以改善性能和易管理性。

有些Oracle优化器特性已经被从本文移除，并在它们自己的文章中被讨论。具体来说，它们是：
  >>优化统计信息和优化统计信息顾问
  >>SQL计划管理
  >>近似查询处理

为了得知Oracle优化器的全貌，我们建议你结合参考文献部分列出的相关文件来阅读本文。详细信息请参见第28页。

自适应查询优化

到目前为止，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语句的执行计划，性能反馈发现它串行执行会更好)

newkid · 发表于 2017-3-2 10:42

本帖最后由 newkid 于 2017-3-2 10:55 编辑

SQL计划指令

SQL计划指令是根据通过自动重优化学习到的信息所创建出来的。一个SQL计划指令是一些额外的信息, 优化器可用来生成一个更优的执行计划。例如，当发生连接的两个表在连接列有倾斜数据，SQL计划指令可以指引优化器使用动态统计信息来获得更加精确的连接基数估算。

SQL计划指令是在查询表达式之上创建的，而非语句级或者对象级，这样就可确保它们可被应用于多个SQL语句。在一个SQL语句上有多个SQL计划指令也是可能发生的。一个SQL语句所使用的SQL计划指令数目被显示于执行计划下方的备注部分（图14）。

(图14: 一个语句所使用的SQL计划指令数目被显示于执行计划下方的备注部分)

数据库自动维护SQL计划指令，并把它们存储在SYSAUX表空间。任何未被使用的SQL计划指令在53周之后会被自动清除。SQL计划指令也可以通过DBMS_SPD包手动管理(修改或删除)，然而，你不可能手动创建一个SQL计划指令。SQL计划指令可以通过视图DBA_SQL_PLAN_DIRECTIVES和DBA_SQL_PLAN_DIR_OBJECTS进行监控(见图15)。

(图15: 查看根据通过自动重优化学习到的信息所创建出来的SQL计划指令)

总共有两种类型的SQL计划指令行：DYNAMIC_SAMPLING(动态取样) 和 DYNAMIC_SAMPLING_RESULT(动态取样结果)。“动态取样”类型会告诉优化器，如果看到了这个特定的查询表达式（例如，在country_id, cust_city, 和 cust_state_province上一起使用的过滤谓词），它就应该使用动态取样来纠正基数估算的偏差。

“动态取样结果”这种类型是在Oracle数据库12c第二版开始才有的，它指明了动态取样查询的结果是保存在SQL指令知识库中（而非Oracle数据库12c第一版所使用的服务器结果缓存）。　

(图16: 保存在SQL指令知识库中的动态取样结果，始于Oracle数据库12c第二版)

SQL计划指令同样被ORACLE用来确定扩展统计信息（特别是列群组）是否缺失，是否能够纠正基数估算偏差。一个SQL指令被使用之后，优化器会决定基数估算偏差是否能够用列群组解决。如果是这样的话，它会在下一次收集表统计信息的时候自动创建那个列群组。这个步骤在在Oracle数据库12c第一版中是“始终开启”的，但是从Oracle数据库12c第二版开始,它是通过DBMS_STATS的AUTO_STAT_EXTENSIONS选项进行控制。注意缺省的设置是OFF，所以为了开启自动列群组的创建，你必须执行下列步骤：
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS', 'ON')

于是如果可能的话，扩展信息就会取代SQL计划指令被使用在SQL计划中（等值谓词，group by分组等等）。如果SQL计划指令已经没必要存在，它会在53周后被自动清除。
(注：关于扩展统计信息的更多信息可见参考文章1“了解Oracle数据库12c的优化器统计信息”)

优化器统计信息

优化器统计信息是描述数据库以及里面的对象的数据的集合。优化器利用这些统计信息来为每个SQL语句选择最佳的执行计划。对于任何一个Oracle系统，为了把性能维持在一个可接受的水平，及时收集适当的统计信息是至关重要的。随着每个新版本的发布，Oracle一直致力于自动提供必要的统计信息。

此处提供的仅是摘要，完整的详细信息请见参考文章1，“了解Oracle数据库12c第二版的优化器统计信息”。

新型的直方图

直方图告诉优化器，数据在一个列中是如何分布的。在缺省情况下，优化器假定在一个列中，数据行是跨越不同的值均匀分布的, 在带有等值谓词查询中，基数的计算方法是将总行数除以等值谓词所用到的列中的不同值的个数。直方图的存在改变了优化器用来确定基数估算的公式，并且允许它生成更精确的估算值。从Oracle 12c第一版开始，多了两种额外的直方图，即顶级频度直方图和混合直方图。它们允许优化器对更为棘手的数据倾斜情形推导出改良的基数估算。在Oracle 12c之前，有两种类型的直方图：频度和等高直方图。

统计信息在线收集

当一个索引被创建，Oracle将自动收集优化器统计信息作为索引创建任务的一部分, 在创建索引所必需的全表扫描以及排序之上，顺便加上统计信息的收集(这从Oracle 9i以来就有了)。从Oracle数据库12c第一版开始，同样的技术也被应用于直接路径操作，例如create table as select (CTAS)和对空表的insert as select(IAS)操作。将统计信息收集搭载为数据加载操作的一部分，意味着在数据加载结束之后，不需要额外的全表扫描就可以立即拥有统计信息。

增量统计信息

在分区表上收集统计信息，包括在表级(全局统计信息)和分区(子分区)级别的收集。如果表分区（或子分区）的数据有任何变动，或者分区被添加或移除，那么全局级别的统计信息就必须被修改，以体现出这种变化，使得分区级别以及全局级别的统计信息之间有对应关系。对于大的分区表，扫描整个表来重构精确的全局级别统计信息可能是非常昂贵的。因此，Oracle数据库11g引入了增量统计信息来解决此问题，从而为表中的每个分区创建了纲要(synopses)。这些数据结构可以被用来推导出全局级别的统计信息——包括不可聚合的统计信息，例如列的基数——而无需扫描整个表。

增量统计信息及其陈化(staleness)

在Oracle 11g数据库中，如果一张表的增量统计被打开，当其中的一个分区的一行数据被修改时，那个分区的统计信息就被认为已经过于陈旧，必需被重新收集才能用于生成全局级别的统计信息。
在Oracle 12c数据库中，一个称为INCREMENTAL_STALENESS的新属性允许你控制分区统计信息何时被认为已陈化，并且不能胜任生成全局统计信息。在缺省情况下，INCREMENTAL_STALENESS被设为NULL, 这意味着一旦有一行数据被修改，分区级统计就被认为已陈化（和11g相同）。

或者，它也可被设置为USE_STALE_PERCENT 或 USE_LOCKED_STATS。USE_STALE_PERCENT的意思是，在相应的分区或子分区中，只要被修改行数的百分比低于STALE_PRECENTAGE属性的值(缺省是10%)，分区级统计就会被使用。USE_LOCKED_STATS的意思是如果一个分区上的统计信息被锁定，就会被用于生成全局统计信息，不管这个分区中自从上次收集以来有多少行数据被修改。

增量统计信息以及分区交换加载

分区的好处之一是可以通过分区交换命令轻易地、迅速地加载数据，对业务用户造成最小的影响。分区交换命令允许一张非分区表中的数据被切换到分区表的指定分区中。这个命令并不会物理地移动数据；相反，它只是修改了数据字典，将指针从分区交换到表上，反之亦然。

在以前的版本中，在分区交换的操作过程中，你无法在非分区表上生成必要的统计信息来支持增量统计信息。相反，统计信息只有在交换发生后才能被收集，这是为了确保全局统计信息能够被增量维护。

在Oracle 12c数据库中，必要的统计信息(纲要)可以在非分区表上创建，从而使得在分区交换中被交换的统计信息能够自动被用于增量维护全局统计信息。

更加紧凑的纲要

使用增量统计信息来收集统计信息的性能好处，可能会伴随着纲要所消耗的更高的磁盘存储空间的代价（它们存储在SYSAUX表空间中）。对于具有大量分区和大量列的表，存储空间需求就更高，特别是在不同值（NDV）的数量较多的情况下。除了消耗存储空间，维护非常大的摘要的性能开销也可能变得很可观。 Oracle 数据库12c第二版引入了一种用于收集和存储NDV信息的新算法，这导致纲要缩小了很多，同时保持与先前算法类似的精度水平。

并发统计信息
在Oracle 11g数据库中，统计信息的并发收集被引入。当全局统计信息的收集属性CONCURRENT被设置，Oracle会利用作业调度器和高级队列部件来为每一个对象（表或者分区）创建和管理一个并发的作业。

在Oracle 12c数据库中，统计信息的并发收集被强化以更好地利用每个调度的作业。如果一个表，或分区，或者子分区很小或者是空的，数据库可能自动将这个对象以及其他的小对象整批放进一个单独的作业来降低作业维护的开销。

列群组的自动侦测
扩展统计信息是在Oracle 11g中被引入的。它们有助于优化器改善SQL语句的基数估算的精确性，如果这个语句涉及到包含函数的列(例如UPPER(LastName))，或者在过滤谓词、连接条件、分组键中用到的来自同一个表的多个列。虽然扩展统计信息极其有用，但要得知何种扩展信息应该被创建是很困难的，如果你对应用或者数据集不熟悉的话。

列群组的自动侦测，会自动根据给定的工作负载确定一张表上需要哪些列群组。列群组的自动侦测是一个简单的三步骤的过程。
（关于列群组的创建请参见“了解Oracle数据库12c的优化器统计信息”白皮书）

DBMS_STATS包中的新的报告子程序

对于任何一个系统，为了维持一个可接受的性能水平，知道何时收集、如何收集及时的统计信息是至关重要的。想要确定一个环境中正在执行的是什么样的统计信息收集操作，对统计方法的变动将会如何影响系统，这可能是一项困难且耗时的任务。

在Oracle 12c数据库中, 新的报告子程序被加入到DBMS_STATS包中，使得我们更易于监控何种统计收集活动正在进行，以及对这些操作的参数进行修改会有什么影响。这些DBMS_STATS 子程序是REPORT_STATS_OPERATIONS, REPORT_SINGLE_STATS_OPERATION 和 REPORT_GATHER_*_STATS。

图17演示了一个来自REPORT_STATS_OPERATIONS函数的输出例子。报告显示出详细的信息，在一个指定的时间窗口发生了哪些统计信息收集操作。它给出了详细信息，关于每个操作合适发生，状态如何，覆盖了多少个对象，这可以用文本或者HTML格式显示。

(图17: 统计信息操作的报告)

优化器统计信息顾问

众所周知，劣质统计会导致查询性能问题。识别陈旧的、过时的统计信息和缺失的统计信息相对比较容易，但低质量的统计信息可能就难以识别：例如表和索引之间的不一致，主键/外键关系等等。

统计信息中的不一致通常是不遵循推荐方法的结果，但要严格遵守这些方法有时候也不容易，原因有多种。例如，Oracle不断增强统计信息收集功能，但升级后这些增强功能可能会被忽视（一个很好的示例是关于使用AUTO_SAMPLE_SIZE而非固定百分比的建议）。 DBA可能使用旧脚本手动收集统计信息，不愿意更改“已被证明”的过程。有时，统计信息收集可能被忽视，在批处理期间可能得不到维护，并且在批处理窗口中可能会令人感觉时间不足。还有许多“继承”下来的系统，没有人能理解用于维护统计信息的脚本。

为了解决这些问题，Oracle 数据库 12.2含有一个叫做优化器统计信息顾问(Optimizer Statistics Advisor)的新功能。顾问的目标是分析如何收集统计信息，检验已收集的统计信息的质量，并检查自动统计信息收集的状态（例如，检查是否成功完成）。为了实现这一点，它根据一套规则对数据字典进行检查。如果它发现违反规则的例外情况，可能会生成调查结果，而这些调查结果可能随之导致具体的建议。顾问将会生成一份报告，列出调查结果（伴随相应的“被违反”的规则），然后列出具体建议以纠正这种情况。最后，这些建议可以使用一组操作来实现。操作可以采用SQL脚本的方式输出，也可以自动被实施。

完整的详细信息请见参考文章2, "Oracle 12c数据库优化器统计信息收集的最佳实践"

新增的以及强化的优化技术

在查询优化的过程中，Oracle会使用一系列复杂的技术对SQL语句进行变换。查询优化的这个阶段的目标是为了将原来的SQL语句变换为一个语义上等价、但是处理起来更加高效的SQL语句。Oracle 12c数据库引入了几种新的查询优化方法。

从Oracle 数据库12c第一版开始有的：

部分连接取值

部分连接取值是这样一种优化技术，它是在连接顺序的生成过程中被使用的。这种技术的目标是为了避免产生重复的行，如果不用这种技术，这些重复只能在计划中随后用一个DISTINCT操作符来去除。通过在计划中早些用一个内连接(INNER JOIN)或者半连接(SEMI-JOIN)来取代DISTINCT操作符，这一步骤产生的行数将会减少。这应该会使得计划的总体性能得到改善，因为随后的步骤只需在缩小的行的集合上进行操作。这种优化可以应用在如下类型的查询块：MAX(),MIN(), SUM(DISTINCT), AVG (DISTINCT), COUNT (DISTINCT), DISTINCT, 以及UNION, MINUS, INTERSECT 操作符的分支, [NOT] EXISTS 子查询等等。

考虑如下的DISTINCT查询：

SELECT DISTINCT order_id
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND order_id < 2400;

(译者注：这个例子举得不好。order_id在orders中应该是唯一的，customer_id在customers表中也应该是唯一的，所以连接之后order_id在结果中也应该是唯一的，DISTINCT完全多余)

在Oracle 11g数据库中，ORDERSh CUSTOMERS之间的连接是一个哈希连接(HASH JOIN)，必须在去除重复行的排序发生前被完全取值。

(图18: Oracle 11g数据库的计划要求在ORDERS 和 CUSTOMERS之间的全连接，然后用unique sort去除重复)

有了部分连接取值，ORDERS 和 CUSTOMERS之间的连接被转换为一个半连接，这意味着一旦在CUSTOMERS表中找到一个匹配的CUSTOMER_ID，查询就会转移到下一个CUSTOMER_ID。通过将哈希连接转换为半连接，流入HASH UNIQUE 的行数大大减少，因为相同连接键的重复行已经被去除了。变换过的SQL的计划如图19所示。

(图19: Oracle 12c数据库的计划显示了ORDERS 和 CUSTOMERS之间的半连接，没有产生重复的行)

接受空值的半连接

应用开发者在包含EXISTS子查询的SQL语句中加入IS NULL谓词是不罕见的。加入额外的IS NULL谓词是因为 EXISTS子查询产生的半连接结果会去除具有空值的行，正如内连接(INNER JOIN)所做的一样。考虑如下的查询：

SELECT p.prod_id,s.quantity_sold,s.cust_id
  FROM products p, sales s
WHERE p.prod_list_price > 11
      AND p.prod_id = s.prod_id
      AND (s.cust_id IS NULL
         OR EXISTS (SELECT 1
                        FROM customers c
                     WHERE c.cust_id = s.cust_id
                           AND c.country_id = 'US'
                     )
         );

这里的假定是在s.cust_id 列上可能有空值，而我们想要返回那些行。在Oracle 12c数据库之前，EXISTS子查询无法被展开，因为它出现在一个带有IS NULL谓词的OR谓词（析取谓词）中。因为子查询无法被展开，导致不理想的计划被产生，子查询被作为过滤操作应用在SALES和PRODUCTS表的连接之后。

(图20: Oracle 11g数据库的计划显示EXISTS 自查询被作为过滤操作应用于连接之后)

在Oracle 12c数据库中，一种新型的半连接被引入，称为接受空值的半连接。这种新连接扩展了半连接的算法，在连接的左边的表的连接列上检查空值。在这个例子中检查会发生在s.cust_id。如果列包含空值，那么相应的SALES表上的行被返回，否则半连接被执行以确定该行是否满足连接条件。接受空值的半连接计划如下图21所示。

(图21: Oracle 12c数据库的计划显示 EXISTS子查询被展开，在customers和sales之间使用了接受空值的半连接)

newkid · 发表于 2017-3-2 10:42

本帖最后由 newkid 于 2017-3-2 10:54 编辑

标量子查询的展开

标量子查询是出现在SQL语句的SELECT子句的子查询。因为标量子查询不能被展开，所以一个相关的标量子查询（它引用了子查询之外的列）必须为外层查询产生的每一行被取值。考虑下面的查询：

SELECT c.cust_id, c.cust_last_name, c.cust_city,
   (SELECT avg(s.quantity_sold)
      FROM sales s
      WHERE s.cust_id = c.cust_id) avg_quan
FROM customers c
WHERE c.cust_credit_limit > 50000;

在Oracle 11g数据库中，对于CUSTOMERS 表中 CUST_CREDIT_LIMIT大于50000的每一行，在sales表上的标量子查询都必须被执行。SALES表是大表，把它扫描多次是非常耗费资源的。

(图22: Oracle 11g数据库的计划显示，对于customers表返回的每一行，标量子查询都必须被取值)

将标量子查询展开并且将其转换为一个连接，就免除了为外层查询的每一行都进行求值的必要性。在Oracle 12c数据库中，标量子查询能够被展开，在这个例子中，SALES表上的标量子查询被转换成一个group-by视图。group-by视图确定会返回每组一行，正如标量子查询一样。查询中同样加入了一个外连接，这是为了确保即使当视图的结果为空时，CUSTOMERS的数据仍然会被返回。转换后的查询如下:

SELECT c.cust_id, c.cust_last_name, c.cust_city, v.avg_quan
FROM customers c,
   (SELECT avg(s.quantity_sold) avg_quan, s.cust_id
      FROM sales s
   GROUP BY s.cust_id) v
WHERE c.cust_credit_limit > 50000
   AND c.cust_id = v.cust_id(+);

(图23: Oracle 12c数据库的计划显示标量子查询已经被展开成外连接和GROUP BY视图)

多表左外连接
在Oracle 12c数据库之前，如果在外连接的左边有多个表是不合法的，会导致ORA-01417错误。

(图24: Oracle 11g数据库不支持多表左外连接)
执行这样一个查询的方法是将其翻译为ANSI语法。可是，实现这样的ANSI语法会导致一个横向视图被使用。Oracle无法合并横向视图，所以优化器的计划在连接顺序和连接方法上的选择就受到了限制，这可能导致不理想的计划。
（注：横向视图指的是这样的视图：它引用了不在视图中的表的列）

(图25. ANSI 语法导致带有横向视图的计划，它无法被合并，因此限制了连接顺序)

在Oracle 12c数据库中，用Oracle的(+)指定的多表左外连接现已被支持。一个外连接的左边的多表视图也能够被合并。能够合并视图就能允许更多的连接顺序和连接方法被考虑，结果是更优的计划会被选中。

(图26. 对新的多表作连接的支持允许视图合并，并且会导致更优的计划)

分组和聚合消除

许多应用程序包含具有以下形式的查询：一个group-by查询块具有单个表，这个表又是个group-by视图。在某些条件下，两个查询块的一些group-by子句和聚合函数可以被消除。生成的查询更简单，包含更少的group-by子句和聚合函数。

分组和聚合是昂贵的操作，而它们的消除可能导致更优化的执行计划。此外，这种类型的消除能够触发视图合并，这又可以导致其他的优化得以应用。

考虑下面的例子，外层查询被转换了，所以只含有一个分组操作而不是两个：

(图27: 分组和聚合消除的例子)

相应的SQL执行计划如下：

(图28: 有转换和没有转换的SQL执行计划)

newkid · 发表于 2017-3-2 10:44

本帖最后由 newkid 于 2017-3-2 10:59 编辑

从Oracle 数据库12c第二版开始有的：

基于成本的OR扩展转换

Oracle 12.2数据库中的新增功能是基于成本的OR扩展转换。这种转换是对12.2之前的“OR扩展”的增强，该功能自Oracle 9i以来就有。

OR扩展转换可以被用来优化包含OR子句（技术上称为“析取”）的查询。 OR扩展的基本思想是将包含析取的查询转换为两个或多个分支的UNION ALL查询的形式。这是通过将析取分解为组件，并将每个组件与UNION ALL查询的一个分支相关联来实现的。例如：

(图29: 基于成本的OR扩展转换例子)

OR扩展可以实现更高效的访问路径（索引访问，分区裁剪），并且有时开启了更多的连接方法选项。在Oracle 12.2数据库之前，这种转换在SQL执行计划中是用CONCATENATION操作来指示的，它在语义上等同于UNION-ALL操作符。从Oracle 12.2数据库开始，UNION-ALL操作符将会显示，这反映了为改进转换所做的底层修改。特别地，在UNION ALL分支之上有更多机会应用其他转换（因为用于替代访问方法的成本算法已经被改进）。每个UNION-ALL分支可以并行执行（对于CONCATENATION操作符就不是这样），因此有希望在利用并行执行的决策支持应用中看到性能的改善。

考虑如下的SQL执行计划，把Oracle数据库12.1和12.2相比较：

(图30: 对比Oracle数据库12.1和12.2)

注意CONCATENATION如何被UNION-ALL替换，在个例子中，一个额外的转换可用于消除对T_10K_HUNDRED表的额外扫描。

子查询消除

许多应用程序含有这样的查询，它们的WHERE子句中包含一个单表子查询。在下列条件下，这样的查询可以通过消除子查询来优化：
>>子查询包含单个表
>>这个表也在外层查询中出现
>>相关/关联谓词中涉及到的列是一样的
转换将会从SQL执行计划中消除表的访问路径。例如：

(图31:子查询消除的一个例子)

增强的连接消除

如果一个查询的结果，带连接和不带连接都是一样的，那么连接就就可以被消除。这是这种转换背后的原则，它依赖于主键/唯一键和外键约束。在Oracle 12.2数据库之前，转换只能应用于单列键约束。从Oracle 12.2数据库开始，这种转换在更多情况下被支持，具体地说，现在可以将多列的键约束用于转换，在某些条件下还可以使用延迟约束。

转换的应用是迭代性的，所以连接消除还可能触发更进一步的连接消除。

图32演示了这种转换的影响。在这个例子中，对DEPATMENTS表的访问路径被消除了：

(图32:连接消除的一个例子)

近似查询处理

Oracle 12c数据库添加了新的优化SQL函数APPROX_COUNT_DISTINCT()来为聚合的不同值提供近似的计数。对大量数据的处理明显快于精确的聚合，特别是对于具有大量不同值的数据集，其结果和精确结果的偏差可以忽略不计。

对不同的值进行计数的需求是现今数据分析中的常见操作。对处理时间和消耗资源的优化以几个数量级计，同时提供近乎精确的结果，这大大加速了现有的任何处理过程，并使得分析洞察力达到了新的水平。

Oracle 12.2数据库扩展了这个功能，包括：
>>百分位数和中位数的近似版本(APPROXIMATE_PERCENTILE和APPROXIMATE_MEDIAN)。
>>物化视图的支持和查询重写。
>>能够通过设置会话级或系统级数据库参数，代码零修改就可使用近似SQL函数。

完整的详细资料间参考文章3, "Oracle数据库12c第二版的分析函数"。

SQL计划的管理

对于需要保证SQL执行计划稳定性的关键应用，SQL计划管理（SPM）是一个非常重要的功能。此外，SPM是任何数据库升级的基础，以便以可控的方式将执行计划从一个优化器版本演化为另一个版本，管理执行计划并确保仅使用已知或已验证的计划。

Oracle 12c数据库中的SQL计划管理得到了许多增强：

  >>自动计划演化
  >>强化的自动捕获
  >>从AWR存储库捕获

这些功能的详细资料见参考文章4，“Oracle数据库12c第二版的SQL计划管理”

初始化参数

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

OPTIMIZER_ADAPTIVE_FEATURES(在Oracle数据库12c第一版中引入，在Oracle数据库12c第二版弃用)

此参数在Oracle数据库12c第二版中已过时，已被下面所述的OPTIMIZER_ADAPTIVE_PLANS和OPTIMIZER_ADAPTIVE_STATISTICS取代，它们为客户提供了对优化器自适应功能的粒度更细的控制机制。

在Oracle数据库12c第一版中，自适应查询优化功能的使用（包括自适应连接和SQL计划指令的产生和使用）是通过OPTIMIZER_ADAPTIVE_FEATURES参数来控制的。

如果 OPTIMIZER_ADAPTIVE_FEATURES 被设置为 TRUE, 那么在OPTIMIZER_FEATURES_ENABLE被设置为 12.1.0.1 或更高的情况下，所有自适应查询优化功能都会被使用。

如果 OPTIMIZER_ADAPTIVE_FEATURES 被设置为 FALSE, 那么所有自适应查询优化功能都不会被使用。

OPTIMIZER_ADAPTIVE_PLANS (新增于Oracle数据库12c第二版, New in Oracle Database 12c Release 2, 取代OPTIMIZER_ADAPTIVE_FEATURES)

对自适应计划功能的使用受控于OPTIMIZER_ADAPTIVE_PLANS参数。这个参数的缺省值是TRUE。这个参数控制的功能是：
  >>自适应连接
  >>位图索引裁剪
  >>并行分配方法

如果 OPTIMIZER_ADAPTIVE_PLANS 被设置为 TRUE, 那么在OPTIMIZER_FEATURES_ENABLE被设置为 12.1.0.1 或更高的情况下，自适应计划功能会被使用。
如果 OPTIMIZER_ADAPTIVE_PLANS 被设置为 FALSE, 那么自适应计划功能不会被使用。

OPTIMIZER_ADAPTIVE_STATISTICS (新增于Oracle数据库12c第二版, New in Oracle Database 12c Release 2, 取代OPTIMIZER_ADAPTIVE_FEATURES)

对自适应统计信息功能的使用受控于OPTIMIZER_ADAPTIVE_STATISTICS参数。这个参数的缺省值是FALSE。这个参数控制的功能是：

  >>在查询优化中使用SQL计划指令(SPDs)
  >>对连接的统计信息反馈
  >>对并行查询的自适应动态取样
  >>性能反馈

如果 OPTIMIZER_ADAPTIVE_STATISTICS 被设置为 TRUE, 那么在OPTIMIZER_FEATURES_ENABLE被设置为 12.1.0.1 或更高的情况下，自适应统计信息功能会被使用。
如果 OPTIMIZER_ADAPTIVE_STATISTICS 被设置为 FALSE, 那么自适应统计信息功能不会被使用。优化器继续生成SQL计划指令，但是它们不会和动态取样一起被用于改善SQL执行计划。

将OPTIMIZER_ADAPTIVE_STATISTICS设置为false会继续保留着Oracle 11g数据库引入的统计信息反馈功能(以前称为基数反馈)。

OPTIMIZER_ADAPTIVE_REPORTING_ONLY
这个参数是从Oracle数据库12c第一版开始才有的。为了更好地了解多少SQL语句会受到新的自适应计划的影响，你可以将自适应计划开启为只报告模式，方法是将OPTIMIZER_ADAPTIVE_REPORTING_ONLY设置为TRUE(缺省值是FALSE)。在这个模式下，启用自适应连接方法所需的信息被收集，但是不会有任何修改计划的行动。这意味着缺省的计划总是会被使用，但计划在非报告模式下会如何调整的信息也会被收集。当这个参数被设置为TRUE, 优化器采取的决策可以通过在DBMS_XPLAN.DISPLAY_CURSOR中使用REPORT格式化参数来显示。图33显示了如何查看报告的示例，为简洁起见，默认的NESTED LOOPS计划已被编辑掉了：

(图33: 显示一个自适应计划报告)

通常情况下，比较有用的是检查有多少个SQL执行计划会被自适应计划的启用所影响。例如，将此参数设置为TRUE后，你可以检查游标缓存中的游标，如下：

SET SERVEROUTPUT ON

declare
  cursor si is
select sql_id,
      child_number
   from v$sql
where is_resolved_adaptive_plan='Y'
   and parsing_schema_name in ('SYS','SYSTEM');
begin
for r in si
loop
   for p in (
      select *
         from table(dbms_xplan.display_cursor(
                           sql_id=>r.sql_id,
                           cursor_child_no=>r.child_number,
                           format=>'+report')
                  )
            )
   loop
      dbms_output.put_line(p.plan_table_output);
   end loop;
end loop;
end;
/

OPTIMIZER_DYNAMIC_SAMPLING
虽然OPTIMIZER_DYNAMIC_SAMPLING参数不是新的，但它确实有了新的级别11, 这个级别控制动态统计信息的生成。当设置为级别11时，优化器会自动确定哪些语句会受益于动态统计信息，即使所有的对象已经有了统计信息。

结论

优化器被认为是Oracle数据库最引人入胜的部件之一，这是因为它的复杂性。它的目的是为每个SQL语句确定最高效的执行计划。它是基于查询的结构，它所具有的可用的关于数据的统计信息，以及所有与优化器和执行相关的特性来作出这些决定的。

在Oracle 12c数据库中，随着新的自适应查询优化方法的引入，还有对可用的统计信息的强化，优化器实现了一个巨大的飞跃。

新的查询优化自适应方法使得优化器能够对执行计划作出实时调整，并且发现能够导致更佳的统计信息的额外信息。利用这些信息，和已有的统计信息一起，能够使得优化器对环境有更多的了解，并且允许它每次都选择一个最优的执行计划。

如同以往，我们希望通过详细描述在这一版本中对优化器和统计信息的修改，围绕着它们的迷雾将会被去除，而这一知识将会使得你的升级过程更加顺利，先知先戒备，凡事预则立!

参考文章

在写这篇文章的时候，下列白皮书的Oracle 12c R2版本还没有发布。
它们很快就会被发布，同时也会涵盖Oracle 12c R1的内容。
1. 了解Oracle数据库12c第二版优化器统计信息
http://www.oracle.com/technetwor ... pts-12c-1963871.pdf
2. Oracle数据库12c第二版优化器统计信息收集的最佳实践
http://www.oracle.com/technetwor ... her-12c-1967354.pdf
3. Oracle数据库12c第二版中的分析SQL
http://www.oracle.com/technetwor ... nalysis-2431343.pdf
4. Oracle数据库12c第二版的SQL计划管理
http://www.oracle.com/technetwor ... gmt-12c-1963237.pdf
5. Oracle数据库12c的并行执行基础知识
http://www.oracle.com/technetwor ... amentals-133639.pdf