ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】使用dbm_stats收集及删除列的统计信息

【实验】使用dbm_stats收集及删除列的统计信息

原创 Linux操作系统 作者:secooler 时间:2009-03-09 11:17:59 0 删除 编辑
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
09.03.09

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-563306/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8093375