[翻译]Oracle 12c优化器白皮书(完整版WORD文档已上传)

tddtds · 发表于 2014-4-15 11:13

占位学习

dingjun123 · 发表于 2014-4-15 13:12

学习了

YuBinTAMU · 发表于 2014-4-15 20:27

占位学习

花好月不圆 · 发表于 2014-4-15 22:24

强烈支持

newkid · 发表于 2014-4-16 08:22

本帖最后由 newkid 于 2014-4-23 03:55 编辑

优化器统计信息

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

新型的直方图

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

顶级频度直方图

在过去，如果一个列有超过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列数据的分布情况，以及之上所创建的顶级频度直方图)

混合直方图

在前一个版本，当一个列中不同值的个数大于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数据库中被当作近似频繁值的值得到更精确的基数估算)

统计信息在线收集

当一个索引被创建，全表扫描是必不可少的，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可以关闭统计信息在线收集。

全局临时表上的会话级统计信息

全局临时表通常用于存储应用程序上下文中的中间结果。一个全局临时表的定义，是全系统中拥有适当权限的所有用户所共享的，但其数据永远是会话私有的。在全局临时表（必须是会话级临时表，PRESERVE ROWS ON COMMIT，提交时保留数据）上收集统计信息是行得通的；然而，在前一个版本中，收集到的统计信息会被所有访问这张表的会话所使用。如果不同会话之间在临时表中存储的数据量或者数据的性质有巨大差异，这样就不太理想了。

现在，每个使用临时表的会话都可以有一份单独的统计信息。临时表上共享的统计信息可以通过一个新的DBMS_STATS选项GLOBAL_TEMP_TABLE_STATS来进行控制。在缺省情况下，这个选项被设置为SESSION，意思是每个访问临时表的回航都会有自己的一套统计信息。优化器会试图先使用会话的统计信息，但是如果会话统计信息不存在，那么优化器会使用共享统计信息。如果你想回到以前的表现，即仅有一份统计信息被所有会话所使用，可以将GLOBAL_TEMP_TABLE_STATS选项设为SHARED。

(图25. 将全局临时表不共享统计信息的缺省表现修改为强制共享统计信息)

当通过直接路径操作往一张全局临时表中填充数据（提交时保留数据的那种），会话级的统计信息会被自动创建，这是由于统计信息在线收集的作用，它免除了额外运行统计信息收集指令的必要性，并且不会影响其他会话使用的统计信息。

(图26. 使用直接路径加载对全局临时表填充数据会导致会话级别的统计数据被自动收集)

DBMS_STATS包中的新的报告子程序

对于任何一个系统，为了维持一个可接受的性能水平，知道何时收集、如何收集及时的统计信息是至关重要的。想要确定一个环境中正在执行的是什么样的统计信息收集操作，对统计方法的变动将会如何影响系统，这可能是一项困难且耗时的任务。在Oracle 12c数据库中, 新的报告子程序被加入到DBMS_STATS包中，使得我们更易于监控何种统计收集活动正在进行，以及对这些操作的参数进行修改会有什么影响。

REPORT_STATS_OPERATIONS
这个新函数生成一个报表，显示的是关于在特定的时间窗口内发生了何种统计收集活动的详细信息。报表给出的细节包括每种操作何时发生，状态如何，覆盖了多少个对象。报表可以输出为文本或者HTML格式。

(图27. 生成一个新的 DBMS_STATS.REPORT_STATS_OPERATIONS 报表)

REPORT_SINGLE_STATS_OPERATION

这个新函数生成一个关于指定的统计信息收集操作的详细报表。

REPORT_GATHER_*_STATS
这组新的函数实际上并不会收集统计信息，但是会报告一个对象的列表：假如对应的GATHER_*_STATS过程被以相同的参数设定所调用，哪些对象将会被处理。REPORT_GATHER_*_STATS接受的输入参数和对应的GATHER_*_STATS过程相同，多了两个额外参数：详细级别(detail_level)以及格式(format)。

(图28. 生成新的DBMS_STATS.REPORT_GATHER_SCHEMA_STATS 报表)