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 --
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-563306/