DBA Notes: 2011/11/02
Cheng Li
Manage CBO
statistics
系统需要升级到11g R2， 想把现在的CBO统计信息保存起来，然后，万一升级后的性能下降了，可以从新导入。结合11g，SPM的特性，就起码可以保证升级后性能不下降了。 方法很直接，只要把CBO的Statistics数据在Schema这个Level导出和从新导入就可以了
Transferring Stats
In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
SQL> EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:
SQL> EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');
Reference:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#autoId27
http://www.oracle-base.com/articles/8i/CostBasedOptimizerAndDatabaseStatistics.php
http://www.myoraclesupports.com/content/statistics-best-practices-how-backup-and-restore-statistics
Img318778079.jpg