RE: DBMS_STATS [resend chomped version]

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Sat, 19 Jun 2004 16:44:42 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNEEKMCEAA.lex.de.haan@naturaljoin.nl>

... and this is (pretty much) precisely what ADDM in 10g does: it monitors your SQL workload, filters out the "problematic" high-load SQL, and then suggests to run the SQL Tuning advisor on those statements. one of the tasks the Tuning advisor performs is a thorough statistics check. it does many other smart things too, but that's less relevant in this discussion;
it just hightlights the right approach: don't waste any efforts on non-problematic SQL...
(and this includes the gathering of statistics on the underlying database objects)

Kind regards,
Lex.

visit my website at http://www.naturaljoin.nl

-----Original Message-----

Guess I'll go back to Mogens' recommendations (shown below) as the best posted thusfar. Especially this part:
"There's no correct answer to this question - if there was, it would be part of the DBMS_STATS thing now, I bet :-)"

I would suggest the following (subject to debate, please):

Collect stats (sample 1 percent) on all your objects OR
Import stats from your test system (or another similar system)
If there are performance problems somewhere, fix them with either SQL statement tuning or Breitlings methods, or whatever
If a table grows a LOT in size, re-analyze.
Otherwise, don't touch anything until somebody complains. What Peter Gram from Miracle has called Compulsive Analyze DisOrder (CADO) is perhaps fun, but mostly not needed.
- Binary/unsupported file stripped by Ecartis --
- Type: text/x-vcard
- File: Lex de Haan.vcf

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Sat Jun 19 2004 - 09:41:36 CDT