dbms_stat的基本使用

http://www.xuexizu.com/group/32991/forum/3361/85131
1.创建测试表stats_test
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> create table stats_test ( col1 number, col2 varchar2(40));

Table created.

sec@ora10g> insert into stats_test select rownum,object_name from all_objects;

11237 rows created.

sec@ora10g> commit;

Commit complete.

2.收集列的基本信息，不收集柱状图，指定的size等于1(bucket“小桶”=1)
sec@ora10g>exec dbms_stats.gather_table_stats(user, 'STATS_TEST', cascade=>false, method_opt=>'for columns col1 size 1');

PL/SQL procedure successfully completed.

sec@ora10g>select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram from user_tab_columns where table_name='STATS_TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ------- ---------
COL1              11237           1 C102       C3020D26   .000088992       5 NONE
COL2                                                                         NONE

3.收集列的柱状图信息，指定的size大于等于2小于等于254(bucket“小桶” between 2 and 254)
sec@ora10g>exec dbms_stats.gather_table_stats(user, 'STATS_TEST', cascade=>false, method_opt=>'for columns col1 size 2');

PL/SQL procedure successfully completed.

sec@ora10g>select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram from user_tab_columns where table_name='STATS_TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_ HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ---- ---------------
COL1              11237           2 C102       C3020D26   .000088992    5 HEIGHT BALANCED
COL2                                                                      NONE

4.10g中删除列已有的柱状图信息同时保留列的基本统计信息方法：需要重新收集size为1的统计信息
sec@ora10g>exec dbms_stats.gather_table_stats(user, 'STATS_TEST', cascade=>false, method_opt=>'for columns col1 size 1');

PL/SQL procedure successfully completed.

sec@ora10g> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram from user_tab_columns where table_name='STATS_TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ----------- ---------
COL1              11237           1 C102       C3020D26   .000088992           5 NONE
COL2                                                                             NONE

5.11g中删除已有的柱状图信息同时保留列的基本统计信息得到了简化
sec@ora11g>exec dbms_stats.delete_column_stats(user, 'STATS_TEST','COL1',col_stat_type=>'HISTOGRAM');

PL/SQL procedure successfully completed.

sec@ora11g> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram from user_tab_columns where table_name='STATS_TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE              DENSITY AVG HISTOGRAM
---------- ------------ ----------- ---------- -------------------- ---------- --- ---------
COL1              15404           1 C102       C3023705             .000064918   5 NONE
COL2                                                                               NONE

6.删除整个列的统计信息，使用delete_column_stats
sec@ora10g>exec dbms_stats.delete_column_stats(user, 'STATS_TEST', 'COL1');

PL/SQL procedure successfully completed.

sec@ora10g> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram from user_tab_columns where table_name='STATS_TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ----------- ---------
COL1                                                                             NONE
COL2                                                                             NONE

7.混合打法：同一个过程中收集多个列的统计信息，不同的列指定不同的size数
sec@ora10g>exec dbms_stats.gather_table_stats(user, 'STATS_TEST', cascade=>false, method_opt => 'for columns col1 size 1 for columns col2 size 2');

PL/SQL procedure successfully completed.

sec@ora10g> select column_name,num_distinct,num_buckets,low_value,high_value,density,avg_col_len,histogram from user_tab_columns where table_name='STATS_TEST';

COLUMN_NAM NUM_DISTINCT NUM_BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_C HISTOGRAM
---------- ------------ ----------- ---------- ---------- ---------- ----- ---------------
COL1              11237           1 C102       C3020D26   .000088992     5 NONE
COL2               8717           2 4143434553 5F75746C24 .000134719    19 HEIGHT BALANCED
                                    5324       5F6C6E635F
                                               696E645F70
                                               61727473

8.小结
使用dbm_stats收集及删除列的统计信息是一种高效的方法，也是Oracle推荐的统计列信息的方法
对生产系统的核心表可以使用“混合打法”编写合适的统计分析脚本，定期执行，提高数据库的效率

secooler