Re: Should we stop analyzing?

• Original Message -----

> I'd like to start a debate, which perhaps has already taken place, but
> if so I don't recall it: Should we stop analyzing tables and indexes?

As a regular thing, yes. Unless there is a clear case for doing it often: highly variable tables. And even then, I want to know WHEN to analyze: when they are empty or when they are full?

>
> Looking back, I can recall several places where they analyzed every
> weekend, and on Monday the system could very well behave differently.

and usually for the worse. I recall a particular PS site where every time we analyzed, we got into trouble... That was with 8.0 and there was nothing we could do other than stop analyzing. Which we did and the problems went away (on that particular table).

> Makes sense if the optimizer has some new/different information to consider.

Yeah, but the $64K question is: HOW do you know that the optimizer has something different to consider? There is nothing (other than seat-of-the-pants feeling or prior knowledge of behaviour of app) that will tell you that.

> On the other hand, it feels so intuitively right to constantly have
> up-to-date stats, doesn't it?

No, not at all. I'm really against this "tune-every-minute" approach.
>From my point of view, I want to get the darn thing running OK and
then LOCK IT IN so it doesn't blow in my face unexpectedly.

I'll gladly trade the last 10% of performance I might (and I stress the "might") get for a system that behaves reasonably well and STAYS that way all the time!
Makes for quiet nights, full of sleep. And at my age I like those more and more...

> I'd like to know what practical and philosofical ideas you guys have on
> this topic.

Well, my approach has always been: get the thing to perform within 10-20% of optimal and lock it in so it won't suddenly go South.

It's much more important for me not to spring surprises on users than to give them a system that's only tuned optimally for 5 minutes before I next run the stats.
And the last thing I want when all hell breaks loose and I've got the site manager breathing down my neck is to overload the system even more with a full analyze...
But that may be just me.

> Best regards - and Happy New Year,

To you too.

Cheers
Nuno Souto
nsouto_at_optusnet.com.au

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nuno Souto
  INET: nsouto_at_optusnet.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).