We can transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. This is possible using the DBMS_STATS package
Here are the steps to do for a table
Step 1. Create the stat table
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT,'STATTAB', 'TOOLS');
Step 2 Export table statistics to statistics table.
EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'EMP', STATTAB=>'STATTAB',STATID=>'EXP_1',STATOWN=>'SCOTT);
Step 3 Export the user statistics table into an exp file
exp file=stats.dmp log=stats_exp.log tables=scott.stattab
Step 4 Import the exp file into another database
imp file=stats.dmp log=statistisc_import.log
Step 5 Import table statistics from the statistics table to the data dictionary table
EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'EMP', STATTAB=>'STATTAB',STATID=>'EXP_1',STATOWN=>'SCOTT);
Step 6 Drop the stat table
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT','STATTAB');
Here are the steps to do for a schema
Step 1. Create the stat table
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT,'STATTAB', 'TOOLS');
Step 2 Export schema statistics to the user statistics table.
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STATTAB',STATID=>'EXP_SC_1',STATOWN=>'SCOTT');
Step 3 Export the user statistics table into an exp file
exp file=stats.dmp log=stats_exp.log tables=scott.stattab
Step 4 Import the exp file into another database
imp file=stats.dmp log=statistisc_import.log
Step 5 Import the schema stats from the User statistics table to the data dictionary
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STATTAB',STATID=>'EXP_SC_1',STATOWN=>'SCOTT');
Hope you like this article on How to transfer statistics between databases in Oracle
Related Articles
How to import statistics in Oracle
How to create stat table in Oracle
How to delete statistics from Table in Oracle
How to check column statistics in Oracle
How to export statistics in Oracle
Leave a Reply