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

dingjun123

学习了

YuBinTAMU

占位学习

花好月不圆

强烈支持

newkid

优化器统计信息

优化器统计信息是描述数据库以及里面的对象的数据的集合。优化器利用这些统计信息来为每个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 报表)

newkid

〇〇 发表于 2014-4-15 06:38
with虚拟表的基数估计不知道改进没

只提到了全局临时表的统计信息的改进。
在执行过程中生成的临时表不知道有没有采集并且保存。这种信息是不可能被其他SQL共享的，但是对于改进单个SQL的计划还是有好处的。
从理论上讲，改善了基表的统计信息（比如根据谓词情况增加列群组的采集），也就改善了WITH临时表的基数估算。

blueshuzi

好文留爪~

gyhgood

收藏学习

dingjun123

12c优化器很强大。。。12c的tuning文档我看了点

zqj101

好文章，正在学习中。谢谢版主。

newkid

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

部分连接取值

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

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

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

(图 30. 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表的连接之后。

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

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

标量子查询的展开

标量子查询是出现在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表是大表，将它扫描很多次是非常耗费资源的。

(图 33.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(+);

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

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

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

(图36. ANSI 语法导致带有横向视图的计划，它无法被合并，因此限制了连接顺序)
在Oracle 12c数据库，用Oracle的(+)语法指定的多表左外连接现已被支持。你也能够合并一个外连接的左边的多表视图。能够合并视图就能允许更多的连接顺序和连接方法被考虑，结果是更优的计划会被选中。

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

(译者注：在12c中，即使用ANSI的写法，也可以得到和(+)写法一样的优化计划)

初始化参数

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

OPTIMIZER_ADAPTIVE_FEATURES
对于新的自适应查询优化功能的使用，包括自适应连接，SQL计划指令的创建和使用，是受OPTIMIZER_ADAPTIVE_FEATURES参数控制的。这个参数的缺省值和OPTIMIZER_FEATURES_ENABLE(OFE)相关。如果OFE被设置为12.1.0.1或者更高，那么OPTIMIZER_ADAPTIVE_FEATURES被设为TRUE, 所有的自适应查询优化功能将会启用。如果OFE被设为比12.1.0.1更低，那么OPTIMIZER_ADAPTIVE_FEATURES将会被设为FALSE, 所有的自适应查询优化功能都不会启用。

OPTIMIZER_ADAPTIVE_REPORTING_ONLY
乍一看，执行计划的自适应或者中途变动有些吓人。为了更好地理解多少SQL语句会受到新的自适应计划的影响，你可以将自适应计划开启为只报告模式，方法是将OPTIMIZER_ADAPTIVE_REPORTING_ONLY设置为TRUE(缺省值是FALSE)。在这个模式下，启用自适应连接方法所需的信息被收集，但是不会有任何修改计划的行动。这意味着缺省的计划总是会被使用，但计划在非报告模式下会如何调整的信息也会被收集。

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