Transferring statistics between database
In general, development DB usually will have only portion of the data when we compared to Production database.
In such a scenario, when we fix any production issues, obviously we make the changes in Dev DB and test the code and move to Prod DB. While testing the code in Dev DB, if we want to compare the execution plan between Dev and Prod, then we can copy the Prod DB statistics into Dev DB and forcast the optimizer behaviour in development server.
DBMS_STATS has an ability to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data.
Source database : orcl
Source schema : sales
Target database : oradev
Target schema : sales
Now our goal is to copy the statistics from sales@orcl to sales@ordev.
step1. First create a stat table in the source database. The statistics table is created in SYSTEM schema.
SQL> connect system/password@orcl
Connected.
SQL> EXEC DBMS_STATS.create_stat_table(‘SYSTEM’,’STATS_TABLE’);
PL/SQL procedure successfully completed.
SQL>
Step2. Export the sales schema statistics.
SQL> EXEC DBMS_STATS.export_schema_stats(‘SALES’,’STATS_TABLE’,NULL,’SYSTEM’);
PL/SQL procedure successfully completed.
SQL>
Step3. Export the STATS_TABLE by using expdp or exp utility and move the dump file to target(ordev) server.
Step4. Import the dump file into target database by using impdp or imp utility. Here i imported the dump file in system schema at target server.
Step5. Import the statistics into application schema(sales@ordev). Please remember, previous step, we imported the stats_table content into system schema by using impdp method. But this step, we are importing the statistics into relevant data dictionary table by using dbms_stats pacakge.
SQL> EXEC DBMS_STATS.import_schema_stats(‘SALES’,’STATS_TABLE’,NULL,’SYSTEM’);
PL/SQL procedure successfully completed.
SQL>
Step6. Drop the stats_table in target server.
SQL> EXEC DBMS_STATS.drop_stat_table(‘SYSTEM’,’STATS_TABLE’);
PL/SQL procedure successfully completed.
SQL>
Note : We can follow step1 and step2 to backup the statistics before we gather new statistics. It is always good to backup the statistics before we overwrite the new statistics. In case, if we see any performance problem with new statistics, then we can import the old statistics. This option is very useful to transfer the statistics from one DB to another DB.
