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语句的首次执行期间，优化器生成了一个执行计划，并且决定是否应该为游标启动统计信息反馈监视器。统计信息反馈在如下的情形被启用：缺失统计信息的表，表上有多个合取或者析取谓词(指AND或者OR连接的谓词), 谓词包含有复杂操作，使得优化器不能准确估算基数。

在查询结束之时，优化器将它原来的基数估算和在执行期间观测到的实际基数进行比较，如果估算值和实际值有显著差异，它会将正确的值存储起来供后续使用。它还会创建一个SQL计划指令，使得其他的SQL语句也能受益于这次初始执行中学到的信息。如果查询再次执行，优化器会使用纠正过的基数估算值，而不是它原先的估算值，来确定执行计划。如果它发现初始的估算值是正确的，则不会采取任何额外的措施。在第一次执行之后，优化器关闭了统计信息反馈的监视。

图10显示了一个SQL语句受益于统计信息反馈的例子。在这个两表连接的初次执行中，由于customers表上有多个相关的单列谓词，优化器将基数低估了8倍。

(图10. 一个受益于自动重优化的统计信息反馈的SQL语句初次执行的情况)

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

(图11. 在初次执行的统计信息与原有的基数估算有显著差异之后，游标被标识为可重优化)

一个SQL计划指令同样被创建，这是为了确保下次如果在customers表使用了相似的谓词的SQL语句被执行，优化器会注意到这些列之间的相关性。

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

(图12. 新生成的计划使用来自初次执行的统计信息)

新计划没有标识为IS_REOPTIMIZIBLE，所以它将被这个SQL语句的所有后续执行所使用。

(图13. 新生成的计划标识为不可重优化)

9、性能反馈

重优化的另一种形式为性能反馈，当自动并行度(AutoDOP)在自适应模式下被启用，这会有助于改善重复执行的SQL语句的并行度的选择。（注：关于AutoDOP的更多信息请参照Oracle并行执行基础白皮书）当自动并行度(AutoDOP)在自适应模式下被启用，在一个SQL语句的首次执行过程中，优化器会决定语句是否应该在并行模式下执行；如果是，应该使用哪种并行度。并行度的选择是基于语句的预计性能表现。对于优化器决定并行执行的任何SQL语句，额外的性能监视器同样在初次执行的时候被打开。在初次执行结束时，优化器选择的并行度，和根据语句初次执行期间的实际性能统计信息（例如CPU时间）计算出来的并行度，被加以比较。如果两个值有显著差别，那么语句被标识为可重优化，初次执行的性能统计信息被作为反馈存储起来，以帮助为后续的执行计算出一个更加合适的并行度。如果性能反馈被用于一个SQL语句，它会在计划下方的备注部分被注明，如图14所示。注意：哪怕AutoDOP不在自适应模式下被启用，性能反馈也可能影响一个语句的并行度

选择。

(图14. 一个SQL语句的执行计划，性能反馈发现它串行执行会更好)

10、SQL计划指令

SQL计划指令是根据通过自动重优化学习到的信息所创建出来的。一个SQL计划指令是一些额外的信息, 优化器可用来生成一个更优的执行计划。例如，当发生连接的两个表在连接列有倾斜数据，SQL 计划指令可以指引优化器使用动态统计信息来获得更加精确的连接基数估算。SQL计划指令是在查询表达式之上创建的，而非语句级或者对象级，这样就可确保它们可被应用于多个SQL语句。在一个SQL语句上有多个SQL计划指令也是可能发生的。一个SQL语句所使用的SQL计划指令数目被显示于执行计划下方的备注部分（图15）。

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

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

DBA_SQL_PLAN_DIR_OBJECTS进行监控(见图16)。

如前所述，当图10所示的SQL语句被发现优化器的基数估算和计划中的操作所返回的实际行数有显著差异时，一个SQL计划指令就被创建。实际上，有两个SQL计划指令被自动创建。一个是为了纠正在customers表上由于多个单列谓词之间的相关性所导致的基数估算偏差，一个是为了纠正sales表上的基数估算偏差。

(图16. 查看根据

通过自动重优化学习到的信息所创建出来的SQL计划指令)

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

SQL计划指令同样被ORACLE用来确定扩展统计信息（特别是列群组）是否缺失，是否基数估算偏差能被列群组所纠正。如果是这样的话，它会在下一次收集统计信息的时候自动在相应的表上创建那个列群组。于是如果可能的话，扩展信息就会取代SQL计划指令被使用在SQL计划中（等值谓词，group by分组等等）。如果SQL计划指令已经没必要存在，它会在53周后被自动清除。

(注：关于扩展统计信息的更多信息可见文章“理解优化器统计信息”)

举个例子，在前面的例子中，SQL计划指令16334867421200019996被创建于customers表。这个SQL计划指令被创建的原因是多个单列谓词之间的相关性。 一个CUST_CITY,

CUST_STATE_PROVINCE,和 COUNTRY_ID上的列群组就可以解决基数估算偏差。下一次收集customers表的统计信息的时候，这个列群组就会被自动创建。

(图17. 基于SQL计划指令自动创建的列群组)

下次这个SQL语句被执行的时候，列群组统计信息就会取代SQL计划指令被使用。

DBA_SQL_PLAN_DIRECTIVES中的state(状态)列指明了一个SQL计划指令在这个周期中目前处于哪个环节。

一旦单表的基数估算被解决，额外的SQL计划指令可能被创建于同样的语句来解决计划中的其他问题，例如连接和分组的基数估算偏差。

(图18. 随着时间推移，为图10中所见的SQL语句所创建的多个SQL计划指令)

11、优化器统计信息

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

12、新型的直方图

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

13、顶级频度直方图

在过去，如果一个列有超过254个不同值而且指定的桶数为AUTO, 那么一个等高直方图就会被创建。但如果99%或者更多的数据所含有的不同值少于254个，会怎么样？如果等高直方图被创建，那么就存在这种风险，即不能将表中最频繁的值捕获为多个桶的端点值。因此有些频繁值就会被当作非频繁值处理，这会导致不理想的执行计划被选中。

在这种情况下，为了创建一个质量更加的直方图，更好的方法是在构成了表中数据的主体的那些极为频繁的值之上创建一个频度直方图，并且忽略那些非频繁值。一个频度直方图被创建于列中最频繁出现的那些值，当这些值出现在表中99%的数据或者更多。这允许列中所有频繁出现的值被当作频繁值来处理。仅当收集统计指令的ESTIMATE_PERCENT参数被设置为AUTO_SAMPLE_SIZE时，一个顶级频度直方图才会被创建，这是因为列中所有的值都必须被看到，才能确定是否达到必要的标准（99.6%的数据具有254个或者更少的不同的值）。

以PRODUCT_SALES表为例，这个表含有一个圣诞饰物公司的销售数据。表中有 1.78M 行，共有632个不同的TIME_ID。但是PRODUCT_SALES数据的主体含有少于254个不同的值，因为每年的圣诞饰物主要都在12月销售。为了让优化器知道列中的数据发生倾斜，有必要在TIME_ID列上创建一个直方图。在这个情况下，一个含有254个桶的顶级频度直方图被创建。

(图19. PRODUCT_SALES表中TIME_ID列数据的分布情况，以及之上所创建的顶级频度直方图)

14、混合直方图

在前一个版本，当一个列中不同值的个数大于254, 一个等高直方图就会被创建。在等高直方图中，只有在两个或两个以上的桶中作为端点出现的值才会被认为是频繁值。等高直方图的一个突出问题是，一个频度落在总群体的1/254和2/254之间的值可能会也可能不会作为一个频繁值出现。虽然它可能横跨两个桶，它可能只在一个桶中作为端点值出现。这样的值被认为是近似频繁值。等高直方图无法区分近似频繁值和真正非频繁值。

混合直方图类似于传统的等高直方图，因为它只在列中不同值的个数大于254的时候才会被创建。可是，相似性也仅限于此。在混合直方图中，没有任何一个值会出现在多个桶的端点，这就允许直方图包含更多的端点值，实际上也就是比等高直方图具有更多的桶数。那么，混合直方图是如何标识频繁值的？每个端点的频度被记录下来（在一个新的名为endpoint_repeat_count的列中），这样就为每个端点值提供了更精确的指示。

以CUSTOMERS表的CUST_CITY_ID列为例。CUSTOMERS表中有55,500行数据，CUST_CITY_ID列有620个不同的值。在这种情况下频度直方图和顶级频度直方图都不合适。在Oracle 11g数据库中，一个等高直方图将会被创建在这个列上。这个等高直方图有213个桶但是只代表了42个频繁值（出现在2个或更多的桶的端点的值）。CUST_CITY_ID列中实际的频繁值个数是54(即，出现频度大于总行数/桶数=55500/254的那些值有54个)。

在Oracle 12c数据库中，一个混合直方图会被创建。混合直方图有254个桶，并且代表了所有54个频繁值。实际上混合直方图将63个值当作频繁值。这意味着在Oracle 11g数据库中被当作近似频繁值（只在一个桶中作为端点值）现在被处理为频繁值，并且将会有更精确的基数估算。图20显示了一个例子，在Oracle 11g数据库中的一个近似频繁值(52114)如何在Oracle 12c数据库中得到更佳的基数估算。

在 CUST_CITY_ID=52114的数据总共有227行：

(图 20. 混合直方图使得那些Oracle 11g数据库中被当作近似频繁值的值得到更精确的基数估算)

15、统计信息在线收集

当一个索引被创建，全表扫描是必不可少的，Oracle顺便加上统计信息的收集，将自动收集优化器统计信息作为索引创建任务的一部分。现在，同样的技术也被应用于直接路径操作，例如create table as select (CTAS)和insert as select(IAS)操作。将统计信息收集搭载为数据加载操作的一部分，意味着在数据加载结束之后，不需要额外的全表扫描就可以立即拥有统计信息。

(图21. 统计信息在线收集为新创建的SALES2表同时提供了表统计和列统计信息)

统计信息在线收集并不包括收集直方图或者索引统计，因为这些类型的统计信息需要额外的扫描，这可能会对数据加载的性能造成很大的影响。为了收集必要的直方图和索引统计，而无需重新收集基础列统计信息，请使用DBMS_STATS.GATHER_TABLE_STATS过程并将options参数设置为新的GATHER AUTO选项。

(图22. 将 options 设为 GATHER AUTO 在SALES2表上创建了直方图而无需收集基础统计信息)

备注列中的“HISTOGRAM_ONLY”指明直方图在没有重新收集基础列统计的情况下被收集。GATHER AUTO选项仅在统计信息的在线收集发生之后被推荐使用。

有两种方法可以确定统计信息的在线收集是否发生：检查执行计划，看看新的数据源OPTIMIZER STATISTICS GATHERING是否在计划中出现，或者查看USER_TAB_COL_STATISTICS表的新的NOTES列，看看是否有状态值STATS_ON_LOAD。

(图23. 统计信息在线收集操作的执行计划)

根据设计，统计信息在线收集对直接路径操作的性能影响要最小化，因此它只能发生于空对象的数据加载。在向一张现有的表的新的分区中加载数据的时候，为了确保统计信息在线收集能够发生，请使用扩展的语法来显式指定分区。在这种情况下，分区级别的统计信息会被创建，但是全局（表级别）统计信息不会被修改。如果增量统计信息在分区表上被打开，一份纲要也会作为数据加载的一部分被创建。(译注: 纲要(synopsis)是在表分区级别收集的辅助统计信息，包含这个分区的某个列的不同值(NDV)的清单，根据这个信息可以推算出全表的不同值)

(图24. 在往现有分区表插入操作时发生了统计信息在线收集)

注意，在语句级别指定提示NO_GATHER_OPTIMIZER_STATISTICS可以关闭统计信息在线收集。