Manage CBO statistics

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

Img318778090.jpg

Img318778081.jpg

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

转载于:http://blog.itpub.net/26136400/viewspace-710113/