dbms_stat的基本使用

http://www.xuexizu.com/group/32991/forum/3361/85131

1.建立測試表stats_test

[email protected]> conn sec/sec

Connected.

[email protected]> create table stats_test ( col1 number, col2 varchar2(40));

Table created.

[email protected]> insert into stats_test select rownum,object_name from all_objects;

11237 rows created.

[email protected]> commit;

Commit complete.

2.收集列的基本資訊，不收集柱狀圖，指定的size等于1(bucket“小桶”=1)

[email protected]>exec dbms_stats.gather_table_stats(user, 'STATS_TEST', cascade=>false, method_opt=>'for columns col1 size 1');

PL/SQL procedure successfully completed.

[email protected]>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)

[email protected]>exec dbms_stats.gather_table_stats(user, 'STATS_TEST', cascade=>false, method_opt=>'for columns col1 size 2');

PL/SQL procedure successfully completed.

[email protected]>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的統計資訊

[email protected]>exec dbms_stats.gather_table_stats(user, 'STATS_TEST', cascade=>false, method_opt=>'for columns col1 size 1');

PL/SQL procedure successfully completed.

[email protected]> 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中删除已有的柱狀圖資訊同時保留列的基本統計資訊得到了簡化

[email protected]>exec dbms_stats.delete_column_stats(user, 'STATS_TEST','COL1',col_stat_type=>'HISTOGRAM');

PL/SQL procedure successfully completed.

[email protected]> 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

[email protected]>exec dbms_stats.delete_column_stats(user, 'STATS_TEST', 'COL1');

PL/SQL procedure successfully completed.

[email protected]> 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數

[email protected]>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.

[email protected]> 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