How to import stats on new database :

source
source 1

Step 1: Create the stats_table:

SQL> exec dbms_stats.create_stat_table(ownname =>'ULTIMUS',stattab => 'prod_stats', tblspace => 'SYSTEM');
PL/SQL procedure successfully completed.


---
SQL> show user
USER is "ULTIMUS"
SQL> select tname from tab where tname='PROD_STATS';

TNAME
------------------------------
PROD_STATS
---


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=>’’,
   estimate_percent=>dbms_stats.auto_sample_size
   cascade=>TRUE,
   method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

This gather_stat takes a lot of times .. ...




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

exec dbms_stats.export_schema_stats(ownname => 'ULTIMUS',stattab => 'prod_stats');

---------
SQL> exec dbms_stats.export_schema_stats(ownname => 'ULTIMUS',stattab => 'prod_stats');

PL/SQL procedure successfully completed.

SQL> select count(*) from PROD_STATS;

  COUNT(*)
----------
    118464

-------------


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

   expdp ultimus/ultimus directory=test_dir dumpfile=prod_stats.dmp tables=prod_stats



Step 5: copy the export file (e.g. FTP) over to the production server:


step 6:  Import the stats to TEST :

 SQL> exec dbms_stats.import_schema_stats(ownname => 'ULTIMUS', stattab => 'prod_stats');

PL/SQL procedure successfully completed.




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:


exec dbms_stats.import_schema_stats(ownname => 'ULTIMUS', stattab => 'prod_stats');