查询优化器参数 - optimizer_dynamic_sampling

  optimizer_dynamic_sampling

以往，查询优化器的估算只依靠存储在数据字典中的对象统计信息。有了动态采样，情况就不一样了。事实上，在解析阶段也可能会动态收集某些统计信息。这意味着要收集额外的信息，会针对引用的对象执行一些(采样)查询。

遗憾的是，由动态采样收集的统计信息既不会存储在数据字典中，也不会存储在其他什么地方。事实上重用它们的唯一方式就是在共享游标内部重用它们。还要注意由动态采样收集的技术并非一定要使用。实际上，查询优化器会执行一些合理性检查来决定是否应该使用它们。

注意 自12.1版本开始，已使用动态统计信息(dynamic statistics)取代了动态采样。在本书中我总是使用旧名称。

optimizer_dynamic_sampling初始化参数的值(也叫作级别)指定如何以及何时使用动态采样。表9-1总结了可接受的值和它们的含义。注意其默认值取决于optimizer_features_enable初始化参数。

?  如果将 optimizer_features_enable 设置为10.0.0或更高，默认值为级别2。

?  如果将 optimizer_features_enable 设置为9.2.0,默认值为级别1。

?  如果将 optimizer_features_enable 设置为9.0.1或更低，则禁用动态采样。

表9-1 动态采样的级别及其含义

*这是当动态采样通过初始化参数或在语句级别的语法中使用hint触发时用于采样的块的数量。对于级别3和级别4,如果对象统计信息可用，则抽取32个块；否则，抽取64个块。当在对象级别的语法中使用hint的时候，以及对于从1到9的级别，块的数量是用下面的公式计算出来的：32*2^(level-1)。

optimizer_dynamic_sampling初始化参数是动态的，并且可以在实例级别以及会话级别进行修改。在12.1多租户环境下，也可以在PDB级别进行设置。此外，也可以通过hint dynamic_sampling在语句级别指定一个值。这个hint支持以下两种语法。

?  语句级别的语法覆盖optimizer_dynamic_sampling初始化参数的值：dynamic_sampling(level)。

?  对象级别的语法只为特定的表触发动态采样：dynamic_sampling(table_alias level)。

警告 在对象级别语法中通过使用hint触发动态采样时，采样总是会发生。换句话说，查询优化器不去检查是否满足在表9-1中提到的规则。但是，根据对象统计信息是否已经可用，采样的统计信息可能会被丢弃掉。所有这些可能都是不必要的间接开支，所以我不推荐使用对象级别的语法。

从11.2版本开始，如果将optimizer_dynamic_sampling初始化参数设置为默认值，则由查询优化器自动决定如何以及何时将动态采样用于并行执行的SQL语句中。这样做是因为并行SQL语句可能会消耗大量的资源，因此，为其获得尽可能好的执行计划非常关键。

查询优化器可以使用动态采样收集两种类型的统计信息。第一种类型包含以下几个方面：

?  一个段高水位线以下的块的数量

?  一张表中行的数量

?  一个列中唯一值的数量

?  一个列中空值的数量

正如你所看到的，第一种类型的统计信息等同于在数据字典中应该已经可用的对应的统计信息。因此，动态采样收集的统计信息只有在对象统计信息缺失或不准确(陈旧)的条件下才有意义。但是要知道，默认情况下，第一种类型的统计信息只会为那些没有对象统计信息的对象进行收集。但是，可以通过指定hint dynamic_samplingestcdn(table_alias)强制收集。你可能需要在有统计信息但是统计信息不准确时做这件事。这个hint会在如果不强制就不会收集时强制进行收集。

动态采样收集的第二种类型的统计信息包含以下几项：

?  谓词的选择率

?  连接的基数(仅从12.1版本开始)

?  聚合的基数(仅从12.1版本开始)

因为这些统计信息超出了通过对象统计信息能提供的信息(尽管在某些情形中谓词的选择率可以通过扩展统计信息获得),它们意图增加对象统计信息能够提供的信息。有了它们，查询优化器可能能够执行更好的估算。

下面的例子(11.2.0.3版本中运行的dynamic_sampling_levels.sql脚本生成的摘录)表明在哪种情况下1和4之间的值会引导动态采样发生。用于测试的表通过下面的SQL语句创建。最初，它们没有对象统计信息。注意，t_noidx表和t_idx表唯一的不同是后者有一个主键(因此也就有一个索引):

CREATE TABLE t_noidx (id, n1, n2, pad) AS

SELECT rownum, rownum, cast(round(dbms_random.value(1,100)) AS VARCHAR2(100)), cast(dbms_random.string('p',1000) AS VARCHAR2(1000))

FROM dual

CONNECT BY level <=1000;

CREATE TABLE t_idx (id CONSTRAINT t_idx_pk PRIMARY KEY, n1, n2, pad) AS

SELECT *

FROM t_noidx;

下面是首次执行的测试查询。它们之间的唯一区别是，第一个引用的是t_noidx表，第二个引用的是t_idx表：

SELECT *

FROM t_noidx t1, t_noidx t2

WHERE t1.id=t2.id AND t1.id < 19;

SELECT *

FROM t_idx t1, t_idx t2

WHERE t1.id=t2.id AND t1.id < 19;

如果将级别设置为1,则只会在第一查询中执行动态采样，因为第二个查询引用的表上有索引。下面是为我的测试库上的t_noidx表收集统计信息时执行的递归查询。为了更容易阅读，一些hint被去掉了，并且用字面值替换了绑定变量。注意在执行这个测试查询之前已打开SQL跟踪。接下来我要做的仅仅是观察生成的跟踪文件以找出执行的是哪一个递归SQL语句：

select NVL(SUM(C1), 0),

       NVL(SUM(C2), 0),

       COUNT(DISTINCT C3),

       NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END), 0)

  FROM (select 1 AS C1,

               CASE WHEN "T1"."ID" < 19 THEN 1 ELSE 0 END AS C2,

               "T1"."ID" AS C3

          FROM "CHRIS"."T_NOIDX" SAMPLE BLOCK(20, 1) SEED(1) "T1") SAMPLESUB;

下面是需要重点关注的内容。

?  查询优化器计算总的行数，在WHERE子句(id<19)中指定范围内的行数，以及唯一值的数量和id列空值的数量。

?  必须要知晓查询中使用的值。如果使用了绑定变量，查询优化器必须能够窥探绑定变量以便执行动态采样。

?  SAMPLE子句是用来执行采样的。在我的数据库中t_noidx表占用了155个块，所以采样百分比为20%(32/155)。

警告 根据你要处理的数据，可能需要级别6或7来确保动态采样生成有代表性的信息。毕竟，即使是级别7,最多也只抽取256个块。依赖于数据总量和数据分布情况，抽取很少数量的数据块可能不足以正确地代表一张表的整体内容。

如果将级别设置为2,则在两个测试查询中都会执行动态采样，在这个级别，当对象统计信息缺失时总是会使用动态采样。用来为两张表收集统计信息的递归查询和之前展示的语句是相同的。抽取百分比的增加是因为，在这个级别上，它是基于64个块而不是32个。

此外，对于t_idx表，也会执行下面的递归查询。它的目的是通过扫描索引代替之前查询中扫描的表。这么做是因为，在表上执行快速采样可能会漏掉在WHERE子句中谓词指定范围内出现的数据。而如果这些数据存在，在索引上的快速扫描一定会定位到它们：

select NVL(SUM(C1), 0), NVL(SUM(C2), 0), NVL(SUM(C3), 0)

  FROM (select 1 AS C1, 1 AS C2, 1 AS C3

          FROM "CHRIS"."T_IDX" "T1"

         WHERE "T1"."ID" < 19

           AND ROWNUM <=2500) SAMPLESUB;

动态采样的下一个级别是3。从这个级别开始，动态采样也用于数据字典中有可用的对象统计信息的情况。在执行进一步的测试之前，通过下面的PL/SQL代码块收集对象统计信息：

BEGIN

  dbms_stats.gather_table_stats(ownname=>user,

                                tabname=>'t_noidx',

                                method_opt=>'for all columns size 1');

  dbms_stats.gather_table_stats(ownname=>user,

                                tabname=>'t_idx',

                                method_opt=>'for all columns size 1',

                                cascade=>true);

END;

/

如果将级别设置为3或更高，查询优化器会执行动态采样，然后通过测算表中数据样本的选择率来估算谓词的选择率，而不是使用来自数据字典的统计信息以及可能是硬编码的值。下面的两个查询验证了这一点：

select * FROM t_idx WHERE id=19;

select * FROM t_idx WHERE round(id)=19;

对于第一个查询，查询优化器能根据列统计信息和直方图估算id=19这个谓词的选择率。因此没有必要进行动态采样。相反，对于第二个查询(除非round(id)表达式上有扩展的统计信息存在),查询优化器无法推断出round(id)=19这个谓词的选择率。事实上，列统计信息和直方图只提供关于id列自身的信息，并没有关于舍入值的。

下面的查询是用于动态采样的。正如所看到的，它与之前讨论的那个查询有着相同的结构。C2和c3列不同是因为导致动态采样的SQL语句中的WHERE子句不同了。因为一个表达式作用于索引的列(id)上，与t_idx表一样，所以在这个特殊的案例中在索引上没有执行采样：

select NVL(SUM(C1), 0),

       NVL(SUM(C2), 0),

       COUNT(DISTINCT C3)

  FROM (select 1 AS C1,

               CASE WHEN round("T_IDX"."ID")=19 THEN 1 ELSE 0 END AS C2,

               round("T_IDX"."ID") AS C3

          FROM "CHRIS"."T_IDX" SAMPLE BLOCK(20, 1) SEED(1) "T_IDX") SAMPLESUB;

如果将级别设置为4或更高，当WHERE子句中引用同一张表中的两个或两个以上列时查询优化器也会执行动态采样。这样做有助于在有相关列的情况下改进估算能力。下面的查询提供了一个这方面的例子。如果你回头查看创建测试表使用的SQL语句，你会注意到id和n1列包含同样的数据：

select * FROM t_idx WHERE id<19 AND n1<19;

同样在本例中，查询优化器通过与之前的例子结构相同的查询执行动态采样。同样，主要的区别还是在于引起动态采样的SQL语句的WHERE子句：

select NVL(SUM(C1),0),

       NVL(SUM(C2),0)

FROM(

select 1  AS C1

      CASE  WHEN "T_IDX"."ID"<19 AND "T_IDX"."N1"<19 THEN 1 ELSE 0 END AS C2

FROM "CHRIS"."T_IDX" SAMPLE BLOCK(20,1) SEED (1) "T_IDX"

) SAMPLESUB;

总结一下，你可以发现级别1和级别2通常没有太大的帮助。事实上，表和索引都应该拥有最新的对象统计信息。一个常见的例外是当临时表包含的临时数据被访问的时候，临时表可以由全局临时表或普通表实现。

实际上，对于它们来讲经常没有对象统计信息可供访问。关于临时表的例外情况是，在12.1版本中，你可以利用会话级别统计信息。不管怎样，要知道一个会话可以共享另一个会话解析的游标，即使这一时刻它被使用了，与临时表关联的段包含完全不同的数据集。

级别3以及更高的级别对于改进“复杂”谓词的选择率估算非常有用。因此，如果查询优化器因为“复杂的”谓词无法做出正确的估算，请将optimizer_dynamic_sampling初始化参数设置为4或更高的值。否则，就保持默认值吧。此外，在第8章中提到过，从11.1版本开始可以在表达式和列组上收集统计信息。所以在某些情形下，应该能够避免动态采样。