Saving and restoring Optimizer Statistics

When you are in the process of optimizing a query, it's very convenient to backup the optimizer statistics previous to gather specific statistics like histograms or other gathering methods.

You can do it following these steps:

1.- Create the statistics table where you hold the statistics of the table that you would like to backup

exec DBMS_STATS.CREATE_STAT_TABLE (ownname =>'BDD', stattab =>'STATS_TABLE', tblspace => 'USERS');

in this case BDD is the schema where the STATS_TABLE table will be created.

2.- Backup the statistics of the application table

exec dbms_stats.export_table_stats (ownname =>'TST10', tabname => 'T_PART',stattab =>'STATS_TABLE', statid => 'BKP20080702', cascade => TRUE, statown=>'BDD');

TST10 is the application schema and T_PART is the table whose statistics will be saved. With parameter statid we can associate an identifier to the backed up statistics.

3.- Importing the statistics

If we want to restore the statistics we can execute:

exec dbms_stats.import_table_stats (ownname =>'TST10', tabname => 'T_PART',stattab =>'STATS_TABLE', statid => 'BKP20080702',cascade => TRUE, statown=>'BDD');

Following these simple steps we can save and restore the optimizer statistics of a table.