查询优化器参数 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 Ø  查询优化器计算总的行数，在WHERE子句(idØ  必须要知晓查询中使用的值。如果使用了绑定变量，查询优化器必须能够窥探绑定变量以便执行动态采样。Ø  SAMPLE子句是用来执行采样的。在我的数据库中t_noidx表占用了155个块，所以采样百分比为20%(32/155)。'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