如何使用dbms_stats分析统计信息

Dbms_stats是oracle8i新增的程序包，它使统计数据的生成和处理更加方便，很多人都在推荐使用dbms_stats替代analyze,我倒是不怎么用过，记录一下

estimate_percent --估算抽样百分比
method_opt：for table --只统计表　for all indexed columns --只统计有索引的表列 for all indexes --只分析统计相关索引

--创建统计信息历史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--导出整个scheme的统计信息
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => 'scott',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )

--分析表
sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

pl/sql procedure successfully completed

--分析索引
SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;

pl/sql procedure successfully completed

--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;

pl/sql procedure successfully completed

--导入表的历史统计信息
sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');

pl/sql procedure successfully completed

--导入索引的统计信息
SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table')

--检查是否导入成功
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';

TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED

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

WORK_LIST 4005 186 2007-10-12 15

SQL>

analyze和dbms_stats不同的地方:
analyze是同时更新表和索引的统计信息，而dbms_stats会先更新表的统计信息，然后再更新索引的统计信息，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan
参考：
http://www.dbafan.com/blog/?p=114
http://zhouwf0726.itpub.net/post/9689/288301

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/rockywu/archive/2009/03/07/3966308.aspx