Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Exporting stats for a production server to a dev/test server

Re: Exporting stats for a production server to a dev/test server

From: Jack Silvey <>
Date: Mon, 08 Jul 2002 06:38:22 -0800
Message-ID: <>


Great name.

Our initial stats were generated with the ANALYZE command. We did export all the stats with the dbms_stats - one parameter of the GATHER_TABLE_STATS procedure is CASCADE, which picks up the index and column stats too. Please test first on your own system to make sure this is true for your version and setup.

We did not delete the stats before doing the new, what we did was this:

  1. export the old stats
  2. analyze using a new sample, cascade, with histos
  3. export the new stats (with a new label)
  4. test, reimport the old and new stats as necessary

Do this quick test to show yourself what it can do:

  1. create table tester.statstest as select * from dba_tables;
  2. create index tester.statstestidx on statstest(table_name);
  3. analyze table tester.statstest estimate statistics sample 1 percent for table for all indexes for all columns size 254;
  4. create the stats holding table in the tester schema using DBMS_STATS.CREATE_STAT_TABLE('tester','stats','tsuser01')
  5. export the stats using DBMS_STATS.EXPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
  6. select * from tester.stats to see what is stored
  7. analyze table tester.statstest delete statistics;
  8. import the stats using DBMS_STATS.IMPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
  9. select * from dba_tables, dba_indexes, dba_tab_histograms to see what was imported

You can store multiple versions of stats for the same table with different lables ('1pctest' in the example) but if you try to store stats with the same label it will overwrite.

We did have some data dictionary locking issues trying to import multiple partitions of the same table at the same time, but our db has suspect dd issues anyway, so this might just be a feature of our system.



Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
Please see the official ORACLE-L FAQ:
Author: Jack Silvey

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Jul 08 2002 - 09:38:22 CDT

Original text of this message