Re: Exporting stats for a production server to a dev/test server
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:
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:
a) create table tester.statstest as select * from
dba_tables;
b) create index tester.statstestidx on
statstest(table_name);
c) analyze table tester.statstest estimate statistics
sample 1 percent for table for all indexes for all
columns size 254;
d) create the stats holding table in the tester schema
using
DBMS_STATS.CREATE_STAT_TABLE('tester','stats','tsuser01')
e) export the stats using
DBMS_STATS.EXPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
f) select * from tester.stats to see what is stored
g) analyze table tester.statstest delete statistics;
h) import the stats using
DBMS_STATS.IMPORT_TABLE_STATS('tester','statstest',null,'stats','1pcttest',true,null);
i) 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
--- [EMAIL PROTECTED] 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
>
>
> Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> ahoo.com>cc:
>
>
> Sent by: Subject:
> Re: Exporting stats for a production server to a
> dev/test
> [EMAIL PROTECTED]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
>
>
> --- [EMAIL PROTECTED] 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:
> > [EMAIL PROTECTED]
> > Technical ConsultantWWW:
> > http://www.mincom.com
> > APAC Technical Services Phone: +61 (0)7 3303
> > 3461
> > Brisbane, AustraliaMobile: +61 (0)402 094
> > 238
> > Facsimile: +61 (0)7
> 3303
> > 3048
> >
Re: Exporting stats for a production server to a dev/test server
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 ahoo.com>cc: Sent by: Subject: Re: Exporting stats for a production server to a dev/test [EMAIL PROTECTED]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 --- [EMAIL PROTECTED] 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: > [EMAIL PROTECTED] > Technical ConsultantWWW: > http://www.mincom.com > APAC Technical Services Phone: +61 (0)7 3303 > 3461 > Brisbane, AustraliaMobile: +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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 informatio
Re: Exporting stats for a production server to a dev/test server
I've used 1 month ago on Oracle817 without any problem. --- [EMAIL PROTECTED] a écrit : > 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: > [EMAIL PROTECTED] > Technical ConsultantWWW: > http://www.mincom.com > APAC Technical Services Phone: +61 (0)7 3303 > 3461 > Brisbane, AustraliaMobile: +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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Exporting stats for a production server to a dev/test server
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 --- [EMAIL PROTECTED] 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: > [EMAIL PROTECTED] > Technical ConsultantWWW: > http://www.mincom.com > APAC Technical Services Phone: +61 (0)7 3303 > 3461 > Brisbane, AustraliaMobile: +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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Exporting stats for a production server to a dev/test server
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: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
