[20130528]dbms_stats.gather_table_stats的method_opt.txt

同事想删除某个表的直方图信息,结果老是删除不掉.问我,我看了一下,马上明白使用参数错误,
他使用的是method_opt=>'for columns size 1'.缺少了all参数.应该写成method_opt=>'for all columns size 1'.

实际上这个问题我以前做一些测试的时候我就发现,一直没有好好的总结.找个机会总结一下.

1.测试环境:

SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> create table t1 as select rownum id1 ,mod(rownum,100)+1 id2,'test' name from dual connect by level<=1e4 ;
Table created.

9i下如果缺省不使用method_opt参数,默认是'for columns size repaet'.
10g,11G下如果缺省不使用method_opt参数.默认是'for columns size auto'.

column cascade format a30

column degree format a10

column estimate_percent format a30

column method_opt format a30

column no_invalidate format a30

column granularity format a10

column publish format a10

column INCREMENTAL format a10

column STALE_PERCENT format a10

column AUTOSTATS_TARGET format a10

SELECT DBMS_STATS.get_param ('CASCADE') CASCADE, DBMS_STATS.get_param ('DEGREE') DEGREE,

       DBMS_STATS.get_param ('ESTIMATE_PERCENT') estimate_percent, DBMS_STATS.get_param ('METHOD_OPT') method_opt,

       DBMS_STATS.get_param ('NO_INVALIDATE') no_invalidate, DBMS_STATS.get_param ('GRANULARITY') granularity,

       DBMS_STATS.get_param ('PUBLISH') publish, DBMS_STATS.get_param ('INCREMENTAL') incremental,

       DBMS_STATS.get_param ('STALE_PERCENT') stale_percent, DBMS_STATS.get_param ('AUTOSTATS_TARGET') autostats_target

  FROM DUAL;

CASCADE                        DEGREE     ESTIMATE_PERCENT               METHOD_OPT                     NO_INVALIDATE                  GRANULARIT PUBLISH    INCREMENTA STALE_PERC AUTOSTATS_

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

DBMS_STATS.AUTO_CASCADE        NULL       DBMS_STATS.AUTO_SAMPLE_SIZE    FOR ALL COLUMNS SIZE AUTO      DBMS_STATS.AUTO_INVALIDATE     AUTO       TRUE       FALSE      10         AUTO

--正常如果sql访问了这些表T1,使用了where条件,在sys.col_usage$会有相应的记录.刷新由SMON完成,手工可以使用 dbms_stats.FLUSH_DATABASE_MONITORING_INFO()刷新.

select * from t1 where id1=1 and id2=1 and name='test';

exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO()

SELECT *  FROM sys.col_usage$ where obj# in (select object_id from dba_objects where wner=user and object_name='T1');

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

    275187          1              1              0                 0           0          0          0 2013-05-28 15:27:22

    275187          2              1              0                 0           0          0          0 2013-05-28 15:27:22

    275187          3              1              0                 0           0          0          0 2013-05-28 15:27:22

2.测试各种method_opt参数:

--缺省实际上是method_opt=>'FOR ALL COLUMNS SIZE AUTO'

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_buckets, num_distinct, low_value, high_value, density, histogram  FROM dba_tab_col_statistics WHERE wner = USER AND table_name = 'T1';

COLUMN_NAME          NUM_BUCKETS NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY HISTOGRAM

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

ID1                            1        10000 C102       C302            .0001 NONE

ID2                          100          100 C102       C202           .00005 FREQUENCY

NAME                           1            1 74657374   74657374       .00005 FREQUENCY

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for  columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_buckets, num_distinct, low_value, high_value, density, histogram  FROM dba_tab_col_statistics WHERE wner = USER AND table_name = 'T1';

COLUMN_NAME          NUM_BUCKETS NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY HISTOGRAM

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

ID1                            1        10000 C102       C302            .0001 NONE

ID2                          100          100 C102       C202           .00005 FREQUENCY

NAME                           1            1 74657374   74657374       .00005 FREQUENCY

--可以发现2个字段直方图信息都没有删除. 我觉得这样写'for  columns size 1'可以理解为没有任何列选择1,因为columns后面没有字段名.

--删除直方图信息.method_opt=>'for all columns size 1'.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_buckets, num_distinct, low_value, high_value, density, histogram  FROM dba_tab_col_statistics WHERE wner = USER AND table_name = 'T1';

COLUMN_NAME          NUM_BUCKETS NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY HISTOGRAM

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

ID1                            1        10000 C102       C302            .0001 NONE

ID2                            1          100 C102       C202              .01 NONE

NAME                           1            1 74657374   74657374            1 NONE

--选择全部字段建立直方图.NUM_BUCKETS的最大数量是254.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_buckets, num_distinct, low_value, high_value, density, histogram  FROM dba_tab_col_statistics WHERE wner = USER AND table_name = 'T1';

COLUMN_NAME          NUM_BUCKETS NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY HISTOGRAM

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

ID1                          254        10000 C102       C302            .0001 HEIGHT BALANCED

ID2                          100          100 C102       C202           .00005 FREQUENCY

NAME                           1            1 74657374   74657374       .00005 FREQUENCY

--使用如下method_opt=>'for all columns'.这样也是在每个字段建立直方图,但是num_buckets最大75.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for all columns ');

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_buckets, num_distinct, low_value, high_value, density, histogram  FROM dba_tab_col_statistics WHERE wner = USER AND table_name = 'T1';

COLUMN_NAME          NUM_BUCKETS NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY HISTOGRAM

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

ID1                           75        10000 C102       C302            .0001 HEIGHT BALANCED

ID2                           75          100 C102       C202              .01 HEIGHT BALANCED

NAME                           1            1 74657374   74657374       .00005 FREQUENCY

--使用如下method_opt=>'for columns'.分析后直方图信息依旧.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for  columns ');

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_buckets, num_distinct, low_value, high_value, density, histogram  FROM dba_tab_col_statistics WHERE wner = USER AND table_name = 'T1';

COLUMN_NAME          NUM_BUCKETS NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY HISTOGRAM

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

ID1                           75        10000 C102       C302            .0001 HEIGHT BALANCED

ID2                           75          100 C102       C202              .01 HEIGHT BALANCED

NAME                           1            1 74657374   74657374       .00005 FREQUENCY

--如果我想单独在id1,id2上建立直方图.

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for  columns id1 size 254 for columns id2 size 254 for all columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_buckets, num_distinct, low_value, high_value, density, histogram  FROM dba_tab_col_statistics WHERE wner = USER AND table_name = 'T1';

COLUMN_NAME          NUM_BUCKETS NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY HISTOGRAM

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

ID1                          254        10000 C102       C302            .0001 HEIGHT BALANCED

ID2                          100          100 C102       C202           .00005 FREQUENCY

NAME                           1            1 74657374   74657374            1 NONE

总之最容易做错的就是method_opt=>'for  columns size 1'.