Export/Import Oracle Optimizer Statistics: Complete Guide

calendar Apr 29, 2025 · 3 min read · Oracle Database Query Optimization DBA Tools Statistics Management dbms_stats import export statistics  ·
Share on: linkedin copy

Oracle Optimizer Statistics Transfer Guide

This guide demonstrates how to migrate schema statistics between Oracle databases using DBMS_STATS procedures - crucial for maintaining consistent execution plans across environments.

Exporting and importing optimizer statistics in Oracle Database is essential for maintaining consistent query performance across different environments, such as migrating statistics from production to test systems. This process ensures that the Oracle optimizer has accurate data to generate optimal execution plans, minimizing the risk of performance degradation due to missing or stale statistics

Step-by-Step Breakdown

1. Create a Table to Hold Statistics

Create a user-defined table to store exported statistics:

1exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'prod_stats', tblspace => 'USERS');
  • ownname: Owner of the stats table (e.g., SYS)
  • stattab: Name of the stats table (e.g., prod_stats)
  • tblspace: Tablespace for the stats table (e.g., USERS)

2. Export Schema Statistics to the Table

Export the current statistics for a schema into the stats table:

1exec dbms_stats.export_schema_stats(ownname => 'SCOTT', statown => 'SYS', stattab => 'prod_stats');
  • ownname: Schema whose stats you want to export (e.g., SCOTT)
  • statown: Owner of the stats table (e.g., SYS)
  • stattab: Stats table name

3. Move the Statistics Table

  • Export the prod_stats table using Oracle Data Pump or the traditional export utility.
  • Transfer the dump file to the target database.
  • Import the prod_stats table into the target database

4. Import Statistics into the Target Schema Load the statistics from the table into the data dictionary of the target database:

1exec dbms_stats.import_schema_stats(ownname => 'SCOTT', statown => 'SYS', stattab => 'prod_stats');

Key Points & Insights

  • Why Export/Import?
    This method is crucial for testing environments where you want to replicate production performance or when migrating databases to new hardware or platforms

  • DBMS_STATS Package:
    The DBMS_STATS package is Oracle’s recommended tool for managing optimizer statistics. Avoid using deprecated methods like ANALYZE

  • Automatic vs. Manual Collection:
    Oracle supports automatic statistics collection, but manual export/import is necessary for cross-environment consistency or specific testing scenarios

  • Supported Objects:
    You can export/import statistics at various levels: database, schema, table, dictionary, or fixed objects. Use the corresponding EXPORT_*_STATS and IMPORT_*_STATS procedures

  • Optimizer Usage:
    The optimizer only uses statistics stored in the data dictionary. After importing, statistics must be loaded into the dictionary for the optimizer to use them

Example Workflow

  1. On Source Database:

    • Create stats table
    • Export schema stats to table
    • Export the stats table (Data Pump)

  2. On Target Database:

    • Import the stats table
    • Import schema stats from table to dictionary

Conclusion

Exporting and importing optimizer statistics with DBMS_STATS is a best practice for ensuring consistent query performance across Oracle environments. This process is especially valuable for database migrations, cloning, or performance testing, and helps maintain optimal execution plans by providing the optimizer with accurate statistics

References

Posts in this series