Cherie,
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:
- export the old stats
- analyze using a new sample, cascade, with histos
- export the new stats (with a new label)
- test, reimport the old and new stats as necessary
Do this quick test to show yourself what it can do:
- create table tester.statstest as select * from
dba_tables;
- create index tester.statstestidx on
statstest(table_name);
- analyze table tester.statstest estimate statistics
sample 1 percent for table for all indexes for all
columns size 254;
- create the stats holding table in the tester schema
using
DBMS_STATS.CREATE_STAT_TABLE('tester','stats','tsuser01')
- export the stats using
DBMS_STATS.EXPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
- select * from tester.stats to see what is stored
- analyze table tester.statstest delete statistics;
- import the stats using
DBMS_STATS.IMPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
- 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.
hth,
Jack
- Cherie_Machler_at_gelco.com wrote:
>
> Jack,
>
> Were your old statistics generated with the ANALYZE
> command? If yes, were
> you able to export ALL of the old statistics with
> the export option of
> DBMS_STATS before you actually analyzed with
> DBMS_STATS for the first time?
> If yes, after exporting them, did you delete the old
> statistics using the
> delete option of ANALYZE command before gathering
> new statistics using
> DBMS_STATS?
>
> Thanks,
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
>
>
>
>
>
> Jack Silvey
>
>
> <jack_silvey_at_y To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> ahoo.com> cc:
>
>
> Sent by: Subject:
> Re: Exporting stats for a production server to a
> dev/test
> root_at_fatcity.c server
>
>
> om
>
>
>
>
>
>
>
>
> 07/07/02 08:58
>
>
> PM
>
>
> Please respond
>
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> We just used it with great success to test our new
> analyze strategy. You can label each set of stats,
> and
> quickly switch between different analyze levels.
> Store
> your old stats first and you can always fall back if
> necessary.
>
>
> Jack
>
>
> --- Peter.McLarty_at_mincom.com wrote:
> > Hi
> >
> > Can anyone give feedback good or bad on the
> > dbms_stats feature of
> > exporting statistics. Is there any gotcha's or
> does
> > it work well
> >
> > Cheers
> >
> >
> > --
> > =================================================
> > Peter McLarty E-mail:
> > Peter.Mclarty_at_mincom.com
> > Technical Consultant WWW:
> > http://www.mincom.com
> > APAC Technical Services Phone: +61 (0)7 3303
> > 3461
> > Brisbane, Australia Mobile: +61 (0)402 094
> > 238
> > Facsimile: +61 (0)7
> 3303
> > 3048
> > =================================================
> > A great pleasure in life is doing what people say
> > you cannot do.
> >
> > - Walter Bagehot (1826-1877 British Economist)
> > =================================================
> > Mincom "The People, The Experience, The Vision"
> >
> > =================================================
> >
> > This transmission is for the intended addressee
> only
> > and is confidential
> > information. If you have received this
> transmission
> > in error, please
> > delete it and notify the sender. The contents of
> > this e-mail are the
> > opinion of the writer only and are not endorsed by
> > the Mincom Group of
> > companies unless expressly stated otherwise.
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author:
> > INET: Peter.McLarty_at_mincom.com
> >
> > 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: ListGuru_at_fatcity.com (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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Sign up for SBC Yahoo! Dial - First Month Free
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jack Silvey
> INET: jack_silvey_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: Cherie_Machler_at_gelco.com
>
> 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 truncated ===
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.com
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: ListGuru_at_fatcity.com (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
