Introduction
If you compare a production environnent with its test system and notice different Oracle execution plans it might be useful to transfer the CBO statistics from one system to another. I will show you how this can be done. As this is not a everyday procedure we will add some backup steps to have a fallback possibility.
Procedure Overview
1. Backup original table stats in the target system
2. Fetch and export stats in the source system
3. Import stats in the target system
4. Switch back or cleanup
1. Backup original table stats in the target system
All operations should be done as the SAP schema user. I assume it is SAPSR3 in the following example. I will use table T000 as well. First create a table for the backup stats:
SQL> conn sapsr3
SQL> exec dbms_stats.create_stat_table('SAPSR3','BACKUPSTATS','');
|
Now we dump the actual stats into BACKUPSTATS:
SQL> exec dbms_stats.export_table_stats
('SAPR3','T000',NULL,'BACKUPSTATS','T000_STATS',TRUE);
|
T000_STATS is just an identifier, as it is possible to have multiple table stats in a stat table.
2. Fetch and export the stats in the source system
Getting the stats is pretty much the same as in the step above, but this time executed on the source system:
SQL> conn sapsr3
SQL> exec dbms_stats.create_stat_table('SAPSR3','MYSTATS','');
|
Dump the stats into MYSTATS:
SQL> exec dbms_stats.export_table_stats
('SAPR3','T000',NULL,'MY STATS','T000_STATS',TRUE);
|
Now we have to export the stats table, and move it to the target system. This can be done with BR*Tools as well, I am using exp/imp here.
orasid> exp sapsr3/*** file=tab.dmp log=exp.log
tables=MYSTATS rows=yes indexes=no
|
Move the tab.dmp to the target system.
3. Import the stats in the target system
Import the stats table:
orasid> imp sapr3/*** file=tab.dmp log=imp.log
|
Use dbms_stats.import_table_stats to overwrite the T000 stats.
SQL> exec dbms_stats.import_table_stats
('SAPSR3','T000','NULL','MYSTATS','T000_STATS',TRUE,
no_invalidate=>FALSE);
|
4. Switch back or cleanup
If you are not pleased with the result, just re-import the original stats from the BACKUPSTATS table.
To make the changes stats permanent and prevent BR*Connect from overwriting the stats, you will need to lock the table stats generation, also set the set the Activ flag to ignore in transaction DB21.
SQL> exec DBMS_STATS.LOCK_TABLE_STATS('SAPSR3','T000');
|
To cleanup we simply need to drop the stats tables MYSTATS (on source and target) and BACKUPSTATS.
Further Information
Oracle 10g DBMS_STATS
932975 - Oracle statistics for RFC tables
