RE: DBMS_STATS [resend chomped version]

... 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):

1. Collect stats (sample 1 percent) on all your objects OR
2. Import stats from your test system (or another similar system)
3. If there are performance problems somewhere, fix them with either
   SQL statement tuning or Breitlings methods, or whatever
4. If a table grows a LOT in size, re-analyze.
5. 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: