Oracle Database Performance Tuning – How to Export/Import Schema Statistics?

Export/Import of schema statistics in Oracle:

As database administrators, most of us might have encountered a scenario where the performance of sql queries shows a sudden degradation in our database.
But there are no hard and fast rules about how to deal with performance issues.

Mostly, it is through trial and error methods that we find a solution.

Say, if the same query is working perfectly in another database with the same data ( like the test environment ) , then the issue may be related with the optimizer statistics.
Export/import of schema statistics could be a possible solution.

This article explains how to export/import schema statistics.

Steps to exp/imp schema statistics

In the below steps, OWNER refers to the schema for which export/import of statistics happens and TABLE_NAME refers to the newly created stats table to hold the statistics.

Step 1 : Create a stats table to hold the schema statistics in the source database

SQL> execute DBMS_STATS.create_stat_table (‘OWNER’,’TABLE_NAME’);

Step 2 : Export the existing statistics to the stats table

SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (‘OWNER’,’TABLE_NAME’);

Step 3 : Take an export of the stats table using exp utility

$ exp file=filename.dmp log=filename.log consistent=y buffer=6400000 tables= OWNER.TABLE_NAME

Step 5 : FTP the dump file to the target database

Step 6 :  import the stats table to the target database

$ imp file=filename.dmp log=filename.log fromuser=OWNER touser=OWNER BUFFER=6400000

Step 7: Generate the script to update the data dictionary in the target database

SQL> select ‘exec dbms_stats.import_table_stats(‘||chr(39)||owner||chr(39)||’,

‘||chr(39)||table_name||chr(39)||’,null,’||chr(39)||’TABLE_NAME’||chr(39)||’,

null,true,’||chr(39)||’OWNER’||chr(39)||’)’ from dba_tables where owner =’OWNER’;

Step 8: Execute the script generated in Step 7 to update the statistics.

NB: It is always better to take a backup of the existing statistics before updating it. This will help to revert the changes , in case the issue is not fixed.

Research and Article by : Anju