Export Import optimizer statistics with dbms_stats

Oracle Database Tips by Donald BurlesonDecember 19, 2015

Question: I want to understand when to export and import by dbms_stats statistics and learn when it is a good idea to export and import statistics.

Answer: Importing and exporting statistics for the CBO and the systems stats (external system statistics for CPU, I/O. etc) and useful in a variety of areas:

Export production into test to make test systems "look like" large systems for execution plan generation".
Export/imports can be used to control execution plans by "freezing execution plans".
Statistics are used as a backup before re-analyzing a schema.
System stats can be moved to a smaller server to make it appear as if Oracle is executing on a large fast server.

You can batch together entire import export job under these scenarios:

- System stats: When migrating to a new server, you can export the old system statistics to ensure consistent execution plans until you are ready to use the "real" system stats.

- Systems reverse: Conversely, you can migrate system stats from production to test to make a tiny server appear to be a larger server. This will not improve SQL execution speed, but developers will see the same execution plans that they would see in production:

- Backup stats: Before making any production change to the CBO stats with dbms_stats, take a full schema backup and an backup of your dbms_stats system stats. Remember, the primary reason for re-analyzing stats is to change SQL execution plans.

For example, here we export production table stats and backport them to the test database to make it appear to be a larger table:

exec dbms_stats.create_stat_table ( ownname => user , stattab => 'temp_stat' ) ;

exec dbms_stats.export_table_stats ( ownname => user , stattab => 'temp_stat', tabname => 'mytable', statid => 'stats03252011') ;

FTP stats to to new database by exporting the table temp_stat table

exec dbms_stats.import_table_stats ( ownname => user , stattab => 'temp_stat', tabname => 'customer_fact' )

Oracle recommends that you collect and save CBO statistics and swap them whenever you see a major change in workloads, such as a database that runs in OLTP mode during the day and processes batch jobs at night. The dbms_stats export and import utilities are perfect for matching your statistics to a changing workload.

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: copy the export file (e.g. FTP) over to the production server:

ftp -i prodserv . . .

Step 6: Backup the existing production statistics:

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

Step 7: Delete the existing production schema stats

Step 8: Import the stats:

EXEC DBMS_STATS.import_schema_stats('TEST','STATS_TABLE',NULL,'TEST');

Step 9: 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');

��