How to Backup and Restore Statistics

Backup Optimizer Statistics

Step Checklist: 
1. Create a statistics table in the  User schema 
2. Transfer the statistics to this table
Step detail: 
------------
1. Create a statistics table in the user schema : 
User is the owner of the tables for which support requests CBO statistics.
SQL> connect user/password 
SQL> exec dbms_stats.create_stat_table(user,'STAT_TIMESTAMP'); 
PL/SQL procedure successfully completed.

 2a. Transfer the statistics to this table :

Transfer of statistics is achieved using the 'dbms_stats.export_table_stats' procedure.
Run the package once for each set of statistics to transfer. 
In the following example there are 2 tables:
SQL> exec dbms_stats.export_table_stats(user,'<TABLE_NAME>',NULL,'STAT_TIMESTAMP'); 
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.export_table_stats(user,'<TABLE_NAME_2>',NULL,'STAT_TIMESTAMP'); 
PL/SQL procedure successfully completed.

If you want to collect user/Schema level statistics

SQL> exec dbms_stats.export_schema_stats(user,'STAT_TIMESTAMP'); 
PL/SQL procedure successfully completed.

2b. Transfer SYSTEM statistics to this table :
———————————————-

Transferring SYSTEM statistics : 
If you have system statistics (below SQL returns rows) 
connect system/password
Check for System stats:
select sname,pname,pval1 from sys.aux_stats$ where pval1 is not null;
Create stats storage table
exec dbms_stats.create_stat_table(user,'STAT_TIMESTAMP');
-- Export:
exec dbms_stats.export_system_stats('STAT_TIMESTAMP');
-- Import:
exec dbms_stats.import_system_stats('STAT_TIMESTAMP');
Restore set of statistics 
=========================
Use your statistics backup table and Reimport your statistics
exec dbms_stats.import_table_stats(NULL,'<TABLE_NAME>', NULL,'STAT_TIMESTAMP'); 
exec dbms_stats.import_table_stats(NULL,'<TABLE_NAME_2>', NULL,'STAT_TIMESTAMP');
To find the tables statistics stored in the STAT_TIMESTAMP table: 
 select distinct c1 from STAT_TIMESTAMP where type ='T';
To restore statistics from All tables in STAT_TIMESTAMP' table: 
 exec dbms_stats.import_schema_stats(user,'STAT_TIMESTAMP');

RELATED DOCUMENTS 
----------------- 
Oracle9i Supplied PL/SQL Packages and Types Reference, Release 2 (9.2) Part Number A96612-01

Example of Scott user :

SQL> show user
USER is "SCOTT"
SQL> exec dbms_stats.create_stat_table('SCOTT','STAT_TIMESTAMP');
PL/SQL procedure successfully completed.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
STAT_TIMESTAMP TABLE  => new table created for stats

To collect table DEPT STATS
SQL> exec dbms_stats.export_table_stats('SCOTT','DEPT',NULL,'STAT_TIMESTAMP');
PL/SQL procedure successfully completed.
To collect EMP table stats
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STAT_TIMESTAMP');
PL/SQL procedure successfully completed.
To collect user level stats
SQL> exec dbms_stats.export_schema_stats('SCOTT','STAT_TIMESTAMP');
PL/SQL procedure successfully completed.
To know table stats in stat_timestamp table.
SQL> select distinct c1 from STAT_TIMESTAMP where type ='T';
C1
DEPT
EMP
BONUS
SALGRADE
SQL>

Move  DBMS_STATS Statistics to a Different Database:

1: First, run the export:
%exp scott/tiger tables= STAT_TIMESTAMP file= STAT_TIMESTAMP.dmp
About to export specified tables via Conventional Path ...
. . exporting table STAT_TIMESTAMP ...
Then on the new database, run import:
2: %imp scott/tiger file= STAT_TIMESTAMP.dmp full=y log=implog.txt
Populate the data dictionary in the new database.
3: SQL>exec dbms_stats.import_table_stats('SCOTT','EMP',NULL,'STATS',NULL,TRUE);
PL/SQL procedure successfully completed.

Target and source schema name should be take care

Same schema:

============

If there are two databases and users name are same in both i.e(SCOTT) than procedure is simple as below.
SQL> exec dbms_stats.export_table_stats('SCOTT','DEPT',NULL,'STAT_TIMESTAMP');
PL/SQL procedure successfully completed.
exec dbms_stats.import_table_stats('SCOTT','DEPT',NULL,'STAT_TIMESTAMP');
PL/SQL procedure successfully completed.

Different schema:
=================

If You are exporting stats from one schema name and import into a different schema name (Bug 1077535).
example
SQL> set autot trace explain
SQL> select * from dept;
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>
The schema names much match exactly.
If the target database schema name (import database) is different from the source
database schema name (export database), then you may update the table you exported the statistics
into and set the C5 column to the target schema name.

See example below:
————————————–

STAT_TIMESTAMP = table to store statistics in
DEPT - is my table
SCOTT & COPY_SCOTT - user accounts
---------------------------------------
Checking current explain plan of table DEPT on target db: 
select * from copy_SCOTT;
Execution Plan
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Update the STAT_TIMESTAMP table which contains the statistics from source db, schEma SCOTT,
setting the C5 column to the new schema name on the target db:
update STAT_TIMESTAMP set c5 = 'COPY_SCOTT';
where c5 = 'SCOTT';
commit;
Now import the statistics into the data dictionary on the target db:
exec dbms_stats.import_table_stats('COPY_SCOTT','DEPT',NULL,'STAT_TIMESTAMP');
Check the explain plan. Should reflect new statistics imported:
select * from COPY_SCOTT.DEPT;