oracle 数据统计收集,Oracle 10g收集数据库统计信息

1.需求概述

某数据库由于整体统计信息不准确，多次出现部分业务SQL选错执行计划，从而导致性能下降影响到最终用户体验，目前通过SQL_PROFILE绑定执行计划临时解决，但此方法不够灵活，后续维护工作量也会增加。

Oracle优化器(CBO)依赖数据库统计信息来计算目标SQL各种可能的执行路径的成本，并从中选择一条成本值最小的执行路径来作为目标SQL的执行计划。如果统计信息不准确甚至是错误，会导致优化器选择错误SQL执行计划的概率大大增加。

目前计划对该数据库统计信息进行重新收集，因为生产环境的复杂性，不排除重新收集正确的统计信息后，整体性能反而下降的情况。故而在收集之前需要对原有的统计信息做好备份，如发现收集后性能反而下降的极端情况，也可以快速回退到原有的统计信息。

2.实施步骤

2.1备份当前数据库统计信息

备份当前数据库的统计信息，建议备份后再导出一份留存：

--备份当前数据库的统计信息：

begin

DBMS_STATS.CREATE_STAT_TABLE('SYSTEM','DB_STATS_20190118');

DBMS_STATS.EXPORT_DATABASE_STATS(STATTAB => 'DB_STATS_20190118', STATOWN => 'SYSTEM');

end;

/

--备份后再导出一份留存

expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log tables=system.db_stats_20190118

2.2收集数据库统计信息

收集数据库的统计信息，需要在业务闲时操作：

--开启计时

set timing on

--开始收集全库统计信息

begin

dbms_stats.gather_database_stats(

ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade=>true, degree=>16);

end;

/

注意：degree的值并不是设置越高就越快，同时要根据实际CPU具体情况来设置。

如果数据库是11.2.0.2及以上版本，还可以通过DBMS_STATS.SET_GLOBAL_PREFS设置并发收集进一步提升收集效率，具体可参考：

3.回退方案

3.1正常回退

收集统计信息后发现性能大幅下降，正常回退，直接将原统计信息导入：

--正常回退，直接将原统计信息导入：

exec DBMS_STATS.IMPORT_DATABASE_STATS (STATTAB => 'DB_STATS_20190118', STATOWN => 'SYSTEM');

3.2其他情况

如果DB_STATS_20190118被损坏，可以删除后使用之前的备份导入：

--删除DB_STATS_20190118：

exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','DB_STATS_20190118');

--使用之前的备份导入DB_STATS_20190118：

impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=stats.dmp logfile=stats.log