oracle method_opt,统计量收集Method_Opt参数使用（下）

上篇中，我们介绍了dbms_stats的重要参数method_opt的使用和默认参数。本篇中我们继续来讨论这个参数的作用。

4、method_opt的格式块

从官方介绍上看，method_opt格式是一个字符串结构，有如下结构：

For all [indexed | hidden] columns size xxx

或者

For columns size xxx column

在含义中，字符串包括两个层面：一个是要确定要收集数据表哪些列的统计量，另一个是要确定收集直方图的时候设置多少个bucket。

目前实践场景中，第一个层面一般都是选择收集所有的统计量。也就是使用all columns选项。早期CBO和一些特殊场景下，可以考虑使用indexed和hidden取值，但是并不推荐。

All indexed columns表示只是将出现在数据表索引中的数据列收集统计量。非索引列是不会收集统计量。同时，只有索引列才会创建直方图。

我们给实验数据表T添加索引对象。

SQL> create index idx_t_owner on t(owner);

Index created

SQL> create index idx_t_status on t(status);

Index created

SQL> create index idx_t_id on t(object_id);

Index created

--删除原有的统计量

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all indexed columns size 254');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER27 FREQUENCY

OBJECT_ID254 HEIGHT BALANCED

STATUS2 FREQUENCY

Indexed columns方法显然是反映了Oracle在列统计量收集问题上的一种想法。但是，这种思考是欠考虑的。因为我们不能保证where条件后面出现的所有列均是索引列，而且其他操作，如group by等也是有列因素在其中的。

Hidden columns选项的范围更小，只有那些virtual columns才会被收集统计量。Hidden columns统计量可以帮助提高虚拟列的成本评估。

Size部分表示的是生成直方图的时候，选择的bucket个数。Size参数可以有如下控制值：

Auto：10g之后的默认选项。根据列使用的情况和数据分布情况进行直方图的创建。根据我们在上篇中的讨论，auto情况下要依据col_usage$基础表和数据的倾斜情况来判断。

整数：直接指定bucket的个数。在11g版本中，bucket个数在1-254之间。如果选择直接指定bucket个数的方法，我们可以找到的是一个最大bucket数量。具体真实的个数要根据收集过程中的实际情况而定。如上面的代码片段，我们要求生成254 bucket的直方图，但是只有离散度最好的object_id生成了高度均衡的254 bucket直方图，其他都比较少。应该说，实际收集过程中的bucket个数，与distinct值和直方图类型相关。

Skewonly：根据数据列的分布情况来判断直方图的生成。

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all columns sizeskewonly');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER29 FREQUENCY

OBJECT_NAME254 HEIGHT BALANCED

SUBOBJECT_NAME110 FREQUENCY

OBJECT_ID1 NONE

DATA_OBJECT_ID254 HEIGHT BALANCED

OBJECT_TYPE35 FREQUENCY

CREATED254 HEIGHT BALANCED

LAST_DDL_TIME254 HEIGHT BALANCED

TIMESTAMP254 HEIGHT BALANCED

STATUS2 FREQUENCY

TEMPORARY2 FREQUENCY

GENERATED2 FREQUENCY

SECONDARY2 FREQUENCY

NAMESPACE17 FREQUENCY

EDITION_NAME0 NONE

15 rows selected

从上面的结果看，如果使用skewonly选项，Oracle会去分析数据列的分布情况。如果数据呈现出偏移倾斜的情况，会去生成直方图。

Repeat：Oracle的统计量是一个累积的过程。使用repeat选项去收集哪些当前已经有统计量的列。

5、特定列的统计量收集

For all columns是收集所有的数据列，从格式上，method_opt还支持一种特定列统计量的收集格式。具体格式为for columns size xxx [column name]。

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER10 HEIGHT BALANCED

这样的格式中，只给指定的列生成指定数量bucket的统计量。那么，如果需要收集多个列的统计量，可以按照两种格式进行指定。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner for columns size 5 object_name');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER10 HEIGHT BALANCED

OBJECT_NAME5 HEIGHT BALANCED

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner object_name object_id');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

OWNER10 HEIGHT BALANCED

OBJECT_NAME10 HEIGHT BALANCED

OBJECT_ID10 HEIGHT BALANCED

6、拓展extended统计量收集

Oracle CBO优化器的统计量统计维度默认是单列。当SQL语句中出现多个条件列的时候，其估算的行数是不准确的。在目前的版本中，有两个方法来提高估算精确度，一个是采用动态采样Dynamic Sampling进行实时收集，另一个就是采用11g的拓展统计量Extended Statistic。

在之前的文章中，我们介绍过如何创建Extended统计量。我们使用method_opt，也可以进行拓展统计量收集。

--Sys用户下

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)');

begin dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)'); end;

ORA-20000: Unable to create extension: not supported for SYS owned table

ORA-06512:在"SYS.DBMS_STATS", line 20337

ORA-06512:在"SYS.DBMS_STATS", line 20360

ORA-06512:在line 1

转换到scott普通用户下进行试验。

SQL> conn scott/tiger@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> drop table t purge;

Table dropped

SQL> create table t as select * from dba_objects;

Table created

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)');

PL/SQL procedure successfully completed

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SCOTT' and table_name='T';

COLUMN_NAMENUM_BUCKETS HISTOGRAM

------------------------------ ----------- ---------------

SYS_STUXJ8K0YTS_5QD1O0PEA514IY10 HEIGHT BALANCED

7、结论

在诸多Oracle dbms_stats参数中，method_opt灵活性很高。使用好这个参数，可以帮助我们更好的进行精确化统计量定义，提高SQL执行效率。