Re: DBMS_STATS [resend chomped version]

From: Barbara Baker <barbarabbaker_at_yahoo.com>
Date: Sat, 19 Jun 2004 07:26:48 -0700 (PDT)
Message-ID: <20040619142648.7959.qmail@web50606.mail.yahoo.com>

Jonathan:
"bit of a luxury" is an understatement.
We have 2 dba's. More than 70 oracle databases. Several operating systems (solaris, vms, w2k, linux). Oracle versions from 7.3.3.6 to 9.2.0.4

I don't have 1 database out there where I can get to know every spec of data and how it's distributed.

This thread started (iirc) by the poster asking what's the best way to gather stats. I'm adverse to blindly turning some kind of process on auto-pilot, cross my fingers, and hope it works. But I'm not entirely clear on what options I have.

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.
- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
  >
  > Agree completely - it's a bit of a luxury to have
  > the time,
  > and hard to get the correct information, but every
  > (complex)
  > system needs a table-driven stats gathering process
  > to
  > minimize the work done, and maximise the return on
  > effort.
  >
  > Regards
  >
  > Jonathan Lewis
  >

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:23:42 CDT