Export Import statistics with dbms_stats in Oracle

You can use the Oracle dbms_stats and export utilities to migrate schema statistics from your PROD instance to your TEST instance, so that your developers will be able to do more-realistic execution-plan tuning of new SQL before it’s migrated into PROD. Here are the steps:

Step 1: Create the stats_table:

exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'prod_stats', - >
tblspace => 'SYSTEM');

Step 2: Gather the statistics with gather_system_stats. In this dbms_stats example, we compute histograms on all indexed columns:

DBMS_STATS.gather_schema_stats(
ownname=>'<schema>',
estimate_percent=>dbms_stats.auto_sample_size
cascade=>TRUE,
method_opt=>'FOR ALL COLUMNS SIZE AUTO')

Step 3: Export the stats to the prod_stats table using export_system_stats::

exec dbms_stats.export_system_stats(ownname => 'SYS', stattab => 'prod_stats');

Step 4: Export the stats to the prod_stats table using exp:

exp scott/tiger file=prod_stats.dmp log=stats.log tables=prod_stats rows=yes

Step 5: FTP to the production server:

ftp -i prodserv . . .

Step 6: Import the stats from the prod_stats.dmp table using the import (imp) utility:

imp scott/tiger file=prod_stats.dmp log=stats.log tables=prod_stats rows=yes

Step 7: We can now use the import_system_stats procedure in Oracle dbms_stats to overlay the existing CBO statistics from the smaller TEST instance:

dbms_stats.import_system_stats('STATS_TO_MOVE');

reference: http://www.dba-oracle.com/t_dbms_stats.htm

Related posts: