收集直方图AUTO和SKEWONLY的区别

       收集列的直方图在官方文档中，Oracle® Database PL/SQL Packages and Types Reference

method_opt

Accepts: ·   FOR ALL [INDEXED | HIDDEN] COLUMNS[size_clause] ·   FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...] size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY} - integer : Number of histogram buckets. Must be in the range [1,254]. - REPEAT : Collects histograms only on the columns that already have histograms. - 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会根据column数据分布情况以及column的使用情况（根据COL_USAGE$来判断，COL_USAGE$是被用来记录数据库的列级使用信息，这些信息被用来辅助统计信息收集）进行判断是不是收集这个column的histogram。而SKEWONLY只是根据column的数据分布情况决定是否收集histogram。下面来做一个实验，用的数据库版本是10g：

SQL> create table test as select * from dba_objects;

表已创建。

SQL> alter session set tracefile_identifier = 'Look_For_Me';

会话已更改。

SQL> alter session set events '10046 trace name context forever ,level 12' ;

会话已更改。

SQL> exec dbms_stats.gather_table_stats(user,'TEST',METHOD_OPT=>'for all columns size auto');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats(user,'TEST',METHOD_OPT=>'for all columns size skewonly');

PL/SQL 过程已成功完成。

SQL> alter session set events '10046 trace name context off' ;

      收集直方图为auto后进行tkprof，发现有COL_USAGE$，而执行skewonly后没有用到COL_USAGE$。

SELECT/*+ ordered use_nl(o c cu h) index(u i_user1) index(o i_obj2)

       

         index(ci_obj#) index(cu i_col_usage$)

               index(h

  i_hh_obj#_intcol#) */

 C.NAME COL_NAME,

 C.TYPE# COL_TYPE,

 C.CHARSETFORM COL_CSF,

 C.DEFAULT$ COL_DEF,

 C.NULL$ COL_NULL,

 C.PROPERTY COL_PROP,

 C.COL# COL_UNUM,

 C.INTCOL# COL_INUM,

 C.OBJ# COL_OBJ,

 C.SCALE COL_SCALE,

 H.BUCKET_CNT H_BCNT,

 T.ROWCNT / GREATEST(H.DISTCNT,1) H_PFREQ,

 C.LENGTH COL_LEN,

 CU.TIMESTAMP CU_TIME,

 CU.EQUALITY_PREDS CU_EP,

 CU.EQUIJOIN_PREDS CU_EJP,

 CU.RANGE_PREDS CU_RP,

 CU.LIKE_PREDS CU_LP,

 CU.NONEQUIJOIN_PREDS CU_NEJP,

 CU.NULL_PREDS NP

  FROM SYS.USER$     U,

       SYS.OBJ$       O,

       SYS.TAB$       T,

       SYS.COL$       C,

       SYS.COL_USAGE$ CU,

       SYS.HIST_HEAD$ H

 WHERE :B3 ='0'

   AND U.NAME = :B2

   AND O.OWNER# = U.USER#

   AND O.TYPE# =2

   AND O.NAME = :B1

   AND O.OBJ# = T.OBJ#

   AND O.OBJ# = C.OBJ#

   AND C.OBJ# = CU.OBJ#(+)

   AND C.INTCOL# = CU.INTCOL#(+)

   AND C.OBJ# = H.OBJ#(+)

   AND C.INTCOL# = H.INTCOL#(+)

UNIONALL

SELECT/*+ ordered use_nl(c) */

 C.KQFCONAM COL_NAME,

 C.KQFCODTY COL_TYPE,

 DECODE(C.KQFCODTY,1,1,0) COL_CSF,

 NULL COL_DEF,

 0 COL_NULL,

 0 COL_PROP,

 C.KQFCOCNO COL_UNUM,

 C.KQFCOCNO COL_INUM,

 O.KQFTAOBJ COL_OBJ,

 DECODE(C.KQFCODTY,2, -127,0) COL_SCALE,

 H.BUCKET_CNT H_BCNT,

 ST.ROWCNT / GREATEST(H.DISTCNT,1) H_PFREQ,

 DECODE(C.KQFCODTY,2,22, C.KQFCOSIZ) COL_LEN,

 CU.TIMESTAMP CU_TIME,

 CU.EQUALITY_PREDS CU_EP,

 CU.EQUIJOIN_PREDS CU_EJP,

 CU.RANGE_PREDS CU_RP,

 CU.LIKE_PREDS CU_LP,

 CU.NONEQUIJOIN_PREDS CU_NEJP,

 CU.NULL_PREDS NP

  FROM SYS.X$KQFTA   O,

       SYS.TAB_STATS$ ST,

       SYS.X$KQFCO    C,

      SYS.COL_USAGE$ CU,

       SYS.HIST_HEAD$ H

 WHERE :B3 !='0'

   AND :B2 ='SYS'

   AND O.KQFTANAM = :B1

   AND O.KQFTAOBJ = ST.OBJ#(+)

   AND O.KQFTAOBJ = C.KQFCOTOB

   AND C.KQFCOTOB = CU.OBJ#(+)

   AND C.KQFCOCNO = CU.INTCOL#(+)

   AND C.KQFCOTOB = H.OBJ#(+)

   AND C.KQFCOCNO = H.INTCOL#(+)