How does the METHOD_OPT parameter work —— method_opt 参数应用

这篇文章将详细介绍 method_opt 参数怎样影响目标列上的统计信息以及为目标列收集什么类型的统计信息.

Method_opt 可能是存储过程 dbms_stats.gather_*_stats 中最令人费解的参数. 这个参数最常见的功能就是控制直方图的收集方式, 但实际上它的功能远不及此.  它的实际功能如下所示：

• 控制哪些列收集基本的统计信息
• 收集直方图,
• 收集扩展的统计信息

Method_opt 参数用法分为两个部分, 如下图所示：

“For all [indexed | hidden] columns” 这一部分控制着哪些列将会收集列的基本统计信息(目标列上的最小值, 最大值, 列上不同值的数量, 空值的数量等等). 系统默认值为 FOR ALL COLUMNS, 它将收集表上所有列(包括隐藏列)的基本的统计信息. 此外, 它的其他可选值如下所示：

• FOR ALL INDEXED COLUMNS 指定只有含有索引的字段才能收集列的基本统计信息. 一般不推荐使用这个选项值, 因为在数据库环境中的所有 sql 语句所使用的字段, 比如 select 后面的字段, where 后面字段, group by 中的字段, 并不只是会引用含有索引的字段.
• FOR ALL HIDDEN COLUMNS  指定表中所有不可见的字段才能收集列的基本统计信息, 也就是说不会去收集表上实际可见的列的统计信息. 同样的一般也不推荐使用这个选项值.  这个选项值通常只用于这种情况, 在一个所有列的统计信息都是准确的表中新增了一个或几个不可见或者说是虚拟的列, 只需要收集这个或者这几个不可见列的统计信息, 而不再重复去其他列的统计信息, 那么就使用 for all hidden columns 这个选项.

如果一个字段(列)并不包括在这部分收集基本统计信息的列的列表中, 收集完成后只会计算这个列的平均长度.  而列的平均长度通常会用于计算行的平均长度.

"Size [size_clause]" 这一部分控制收集直方图的方式, size 后面可以有以下选项：      

• AUTO  Oracle 自己决定根据列的统计信息(sys.col_usage$)以及列的数据倾斜程度(均匀分布程度)决定哪些列需要收集直方图      
• Integer  指定收集直方图的桶数, 桶数最小为 1 最大为 254 (针对 11g 及以前的版本, 12c 后没有这个限制).注意如果桶数为 1, 即 size 1 意味着不建立直方图, 如果已经有直方图的列则会删除该列的直方图.
• REPEAT 只在已经有直方图的列上重新收集直方图. repeat 会确保在全局级别上对已经存在直方图的列重新收集直方图. 一般不推荐使用这个选项, 因为新的直方图使用的桶数将不能超过旧的直方图中的桶数. 假设当前直方图中桶数为 5, 当使用 size repeat 重新收集直方图时, 新的直方图使用的桶数将不能超过 5 , 这钟方式可能不会取得好的效果.
• SKEWONLY 只在数据不均匀分布的列上收集直方图.

如果 method_opt 的默认参数 FOR ALL COLUMNS SIZE AUTO 在你的数据环境不适用, 可能你遇到的情况属于下面两种情况：

• 除了指定的列, 在其它列上创建直方图
• 只在指定的列上创建直方图

下面假设我们只想在 sales 表上的 cust_id 列上创建直方图.  记住, method_opt 参数不仅可以指定哪些列收集基本的统计信息, 还可以指定哪些列收集直方图, 因此我们需要考虑把 method_opt 参数分为两个部分.

 下面图中的第一部分指定了哪些列将收集基本的统计信息. 在这里我们想要收集表上所有列的基本统计信息, 因此我们使用 For all columns. 但是关于 size 部分, 我们应该怎样设置参数呢？ 因为我们只想在一个列上收集直方图, 所以我们先指定 size 1 来阻止所有列收集直方图（删除所有列的直方图）

下面图中的第二部分指定了 cust_id 列需要收集直方图,  “For columns” 是 method_opt 语法中额外的部分, 它允许在参数设置部分为指定的列提供明确的操作. 在这里我们使用 FOR COLUMNS SIZE 254 CUST_ID 来指定 cust_id 列收集直方图. 因此最终的 Method_opt 参数设置如下:

下面我们就使用 DBMS_STATS.GATHER_TABLE_STATS 来执行收集统计信息, 虽然在系统中 sales 表中有很多列会用在  where 查询语句中, 但在这里我们只是在 cust_id 列上建立直方图.

       BEGIN
               dbms_stats.Gather_table_stats('SH', 'SALES', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID');
       END;
       /

       PL/SQL procedure successfully completed.

 
      SQL>  SELECT  column_name, num_distinct, histogram    

                  FROM user_tab_col_statistics    

                  WHERE   table_name = 'SALES';

对于 method_opt 参数在很多种情况下需要用到更复杂的设置, 比如你不想收集指定列的任何统计信息. 目前并没有方法告诉 Oracle 怎么样不去收集指定列的统计信息, 但是你可以反过来考虑, 你可以通过 for columns 语法明确的列出需要收集统计信息的列. 下面我们演示不收集 sales 表中 prog_id 列的统计信息的方法：

       BEGIN
             dbms_stats.delete_column_stats('SH', 'SALES', 'PROD_ID');
       END;
       /

    PL/SQL procedure completed successfully. 

 BEGIN 
       dbms_stats.Gather_table_stats('SH', 'SALES', 

 method_opt => 'FOR COLUMNS SIZE 254 CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD'); 
   END; /

   PL/SQL procedure completed successfully.   

-- 即使我们没有收集 prod_id 列的统计信息, oracle 仍然会准确的计算每行的平均长度.

 SQL> SELECT num_rows, avg_row_len
         FROM   user_tables
         WHERE  table_name = 'SALES';

注意:  for columns 语句只能用于 gather_table_stats 存储过程.

最后, 在本文的开头我提到 method_opt 参数可以用于收集扩展的统计信息. 扩展的统计信息包含两类列的统计信息: 列的组合以及列的表达式的统计信息. 在下面的例子中, oracle 将会收集 sales 表上 prod_id 和 cust_id 列的组合的统计信息以及所有基本列的统计信息, 同时, oracle 还将会为列的组合的统计信息自动生成名字.

在收集统计信息的语句中, 我们不建议在 method_opt 中直接指定参数, 而建议使用 dbms_stats.set_table_prefs 存储过程

总结, GATHER_DICTIONARY_STATS, GATHER_DATABASE_STATS, 和 GATHER_SCHEMA_STATS 存储过程只接受 “ FOR ALL [INDEXED|HIDDEN] columns ” 语法, 不能指定具体的列名.

       GATHER_TABLE_STATS procedure 存储过程可以接受以 “for columns ” 格式的额外参数, 使用这种语法可以控制以下内容：

• 哪些列将收集基本的统计信息
• 哪些列将收集直方图以及指定直方图的桶数
• 收集扩展的统计信息