There may be times that you wish to save you database statistics. Perhaps to use in a test system or prior to a code release that may ultimately change your statistics and change you query plans for the worse. By exporting the statistics, they can be saved for future importing.
Source Database : srcdb ( host- example01)
Target database : trgdb ( host - example02)
1) To generate schema statistics and import them into a separate database:
SQL> select instance_name,host_name,status from v$instance;
INSTANCE_NAME HOST_NAME STATUS
---------------- ------------------------------ ------------
srcdb example01 OPEN
SQL>
On the source host, start SQL*Plus and connect to the source database as administrator.
Create a table to hold the schema statistics.
For example, execute the following PL/SQL program to create user statistics table opt_stats:
SQL> BEGIN
DBMS_STATS.CREATE_STAT_TABLE (
ownname => 'SCOTT'
, stattab => 'opt_stats'
);
END;
/
PL/SQL procedure successfully completed.
2) Gather schema statistics.
For example, manually gather schema statistics as follows:
-- generate representative workload
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
3) Use DBMS_STATS to export the statistics.
For example, retrieve schema statistics and store them in the opt_stats table created previously:
SQL> BEGIN
DBMS_STATS.EXPORT_SCHEMA_STATS (
ownname => 'SCOTT'
, stattab => 'opt_stats'
);
END;
/
PL/SQL procedure successfully completed.
4) Use Oracle Data Pump to export the contents of the statistics table.
For example, run the expdp command at the operating schema prompt:
SQL> create or replace directory dump_dir as '/u09/backup/export';
Directory created.
$ expdp DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats
e.g.,
[oracle@example01 export]$ expdp DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats
Export: Release 12.2.0.1.0 - Production on Wed Feb 5 12:40:39 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.125 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCOTT"."OPT_STATS" 1.534 MB 10486 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u03/work/export/SCOTT_stat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 5 12:44:09 2020 elapsed 0 00:03:24
5) Transfer the dump file to the target database host.
$ scp gmohapatra@example01:/u03/work/export/*.dmp .
6) Log in to the target host, and then use Oracle Data Pump to import the contents of the statistics table.
SQL> create or replace directory dump_dir1 as '/u08/backup/import';
Directory created.
For example, run the impdp command at the operating schema prompt:
$ impdp DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats
e.g.,
[oracle@example02 import]$ impdp DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats
Import: Release 12.2.0.1.0 - Production on Wed Feb 5 13:30:16 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."OPT_STATS" 1.534 MB 10486 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Feb 5 13:30:43 2020 elapsed 0 00:00:20
[oracle@example02 import]$
7) On the target host, start SQL*Plus and connect to the target database.
Use DBMS_STATS to import statistics from the user statistics table and store them in the data dictionary.
The following PL/SQL program imports schema statistics from table opt_stats into the data dictionary:
SQL> BEGIN
DBMS_STATS.IMPORT_SCHEMA_STATS(
ownname => 'SCOTT'
, stattab => 'opt_stats'
);
END;
/
PL/SQL procedure successfully completed.
SQL> select name from v$database;
NAME
---------
trgdb
SQL>
-- If you want to lock the statistics, below dbms api can be used.
SQL> exec dbms_stats.LOCK_SCHEMA_STATS('SCOTT');
PL/SQL procedure successfully completed.
SQL>
Note: You can do export and import statistics of whole database or for few tables also.
