method_opt中参数 auto跟skewonly的区别

我们先来看官方文档中对这两个参数的解释
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns
从字面上解读，AUTO和SKEWONLY的区别就在于收集histograms时ORACLE的选择条件不同。
AUTO会根据column数据分布情况以及column的负载情况进行判断是不是收集这个column的histogram.
而SKEWONLY只会根据column的数据分布情况决定是否收集histogram。
这里，我会强调这个“只”字，因为在SKEWONLY时，只要Oracle觉得数据分布可能不均，就会收集列的柱状图。
而AUTO时，即使某一个column它的数据分布不均，但只要这个column的workload很低，或者说没有workload，那么Oracle就不会收集这个column的histogram
Oracle自行判断的依据就来源于col_usage$字典基表，若表上的某一列曾在硬解析(hard parse)过的SQL语句中充当过predicate(通俗的说就是where后的condition)的话，我们认为此列上有收集柱状图的必要，那么col_usage$上就会被加入该列曾充当predicate的记录。当DBMS_STATS.GATHER_TABLE_STATS存储过程以’SIZE AUTO’模式执行时，收集进程会检查col_usage$基表以判断哪些列之前曾充当过predicate，若充当过则说明该列有收集柱状图的价值。
现在我们就来进行测试
06:34:25 kiwi@orcl> create table test2 as select * from dba_objects;

Table created.

Elapsed: 00:00:01.12
07:14:17 kiwi@orcl> select count(*) from dba_objects;

     COUNT(*)
-------------
        86277

Elapsed: 00:00:00.18
07:14:30 kiwi@orcl> update test2 set object_id=100 where rownum<=86200;

86200 rows updated.

Elapsed: 00:00:05.33
07:15:23 kiwi@orcl> commit;

Commit complete.
07:15:26 kiwi@orcl> create index idx_test2 on test2(object_id);

Index created.

我们此时查看test2这张表object_id列的使用情况
07:35:25 sys@orcl> select * from sys.COL_USAGE$ where OBJ# in (select object_id from dba_objects where object_name='TEST2');

no rows selected

先来看此时表TEST2的OBJECT_ID列是否有柱状图
select ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where TABLE_NAME='TEST2' and COLUMN_NAME='OBJECT_ID';
no rows selected

select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST2' and COLUMN_NAME='OBJECT_ID';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      NONE

此时我们以auto的方式来收集统计信息
07:52:22 kiwi@orcl> exec dbms_stats.gather_table_stats('KIWI','TEST2',method_opt=>'for columns OBJECT_ID size auto');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
07:53:28 kiwi@orcl> select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST2' and COLUMN_NAME='OBJECT_ID';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      NONE

由此可见，虽然此时object_id列上面数据分布是极不均衡的，但由于该列没有predicate，所以oracle并不会去收集他的柱状图
如果此时我们进行基于object_id的查询
07:53:35 kiwi@orcl> select * from test2 where object_id=57089;

07:51:32 sys@orcl> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

07:57:52 sys@orcl> select * from sys.COL_USAGE$ where OBJ# in (select object_id from dba_objects where object_name='TEST2');

         OBJ#       INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS   RANGE_PREDS    LIKE_PREDS    NULL_PREDS TIMESTAMP
------------- ------------- -------------- -------------- ----------------- ------------- ------------- ------------- -------------------
        87423             4              1              0                 0             0             0             0 2014-12-21 07:57:35
如果此时我们执行
exec dbms_stats.gather_table_stats('KIWI','TEST2',method_opt=>'for columns OBJECT_ID size auto');
select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST2' and COLUMN_NAME='OBJECT_ID';
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      FREQUENCY
可以看到已经生成了基于频率的直方图
下面我们删掉这个直方图，并且删除掉sys.COL_USAGE$的列，使用skewonly的方式来生成直方图
exec dbms_stats.delete_column_stats(user,'TEST2','OBJECT_ID')exec dbms_stats.delete_column_stats(user,'TEST2','OBJECT_ID')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
08:14:52 kiwi@orcl> select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST2' and COLUMN_NAME='OBJECT_ID';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      NONE

08:16:16 sys@orcl> delete from sys.COL_USAGE$ where OBJ#=87423;

1 row deleted.

Elapsed: 00:00:00.01
08:16:18 sys@orcl> commit;

Commit complete.

Elapsed: 00:00:00.01
08:16:22 sys@orcl> select * from sys.COL_USAGE$ where OBJ# in (select object_id from dba_objects where object_name='TEST2');

no rows selected

删除这些信息以后我们再来看使用auto的方式来进行收集
08:15:02 kiwi@orcl> exec dbms_stats.gather_table_stats('KIWI','TEST2',method_opt=>'for columns OBJECT_ID size auto');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.43
08:17:14 kiwi@orcl> select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST2' and COLUMN_NAME='OBJECT_ID';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      NONE
是不成功的，现在我们使用skewonly的方式来进行收集
08:17:19 kiwi@orcl> exec dbms_stats.gather_table_stats('KIWI','TEST2',method_opt=>'for columns OBJECT_ID size skewonly');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30
08:20:26 kiwi@orcl>  select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST2' and COLUMN_NAME='OBJECT_ID';

COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      FREQUENCY