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 jack_silvey@y To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ahoo.comcc: 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
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
jack_silvey@y To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
ahoo.comcc:
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
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).
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).
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).
