Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle managed statistics

Re: Oracle managed statistics

From: DA Morgan <>
Date: Thu, 01 Nov 2007 00:06:06 -0700
Message-ID: <>

Altus wrote:

> On Oct 31, 5:17 pm, DA Morgan <> wrote:

>> Altus wrote:
>>> Once again I turn to the gurus.
>>> I have a vendor who is nervous about the Oracle managed statistics
>>> (target object auto). They don't like seeing objects with statistics
>>> older than a month.
>>> My choices are:
>>> 1) Fight a battle "re: are the statistics REALLY stale".
>>> This will be long and futile, since management likes vendors.
>>> 2) Change the frequency/agressiveness of the auto analyze.
>>> 3) Run the full analyze every day/week/2 weeks.
>>> Waste of resources / 8i way of doing things.
>>> Since I haven't played with the statistics area of GRID or 10g, I
>>> would like to take that approach.
>>> Is there a way to adjust Oracle's threshold?
>>> Thanks,
>>> Evan
>>> Oracle on Red Hat 3.
>>> Three node RAC.
>>> GRID shows the script as:
>>> begin
>>> dbms_stats.gather_database_stats(
>>> options=> 'GATHER AUTO');
>>> end;
>>> Last night it analyzed 222 objects in 8.46 minutes. This left 899
>>> tables and 1553 indexes analyzed before September 22 (vendor has a
>>> tizzy).
>> Fire the vendor.
>> There are times when stale statistics are the better. They are using
>> mythology to make decisions rather than real-world experience.
>> I would suggest getting a copy of Jonathan Lewis' book on the CBO
>> and reading it with care.
>> --
>> Daniel A. Morgan
>> University of Washington
>> (replace x with u to respond)
>> Puget Sound Oracle Users Hide quoted text -
>> - Show quoted text -
> Not a chance!!!!
> If I read a book, I may want to do things in a 9i or 10g way, rather
> than the way they want (Oracle 8).
> As far as stale being better, there is a table with one row which
> never changes. Guess if they want it analyzed. Guess... I dare yah.
> <grin>
> thanks for the sympathy guys.

Unless you are 60+ years old holding out for a pension ... I'd recommend updating your skills, updating your cv, and getting out of there before your skill set deteriorates to the point that your next job includes saying "Do you want fries with that?" (1/2 * <g>)

Daniel A. Morgan
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Thu Nov 01 2007 - 02:06:06 CDT

Original text of this message