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. This article is tested in oracle10g. Here are the steps to transfer the statistics.

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.

Let us follow the below steps to copy the statistics from source(production) to target(development). I am running all the steps in System Schema..

step1. First create a stat table in the source database. The statistics table is created in SYSTEM schema.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

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.

In oracle10g, it automatically save the statistics for last 31 days by default. We can restore the past statistics within the database at any time. This option is useful to restore the statistics in the same database. Please refer this Restoring statistics