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博客

Manage CBO statistics

最新文章