Re: Should we stop analyzing?

Wolfgang,

First off, sorry for mangling your name in the previous post.

I too will make notes inline.

On Tue, 2003-12-30 at 22:14, Wolfgang Breitling wrote:
> Note inline
>
> At 10:29 PM 12/30/2003, you wrote:
>
> >If my data changes, and I analyze it, CBO should still find
> >reasonable execution paths for the current data.
>
> If the CBO were infallable we wouldn't have this discussion. There are many
> reasons why even the most up-to-date statistics can lead to less than
> optimal access plans. My point is not necessarily with the frequency of
> statistics gathering but with the untested activation of new statistics,
> which is the hallmark of scheduled analyze jobs, as it carries the same
> risk as any untested change.
>
>
> >If my data does not change, and I analyze it, CBO should have
> >the same set of statistics as it did previously.
>
> If your data didn't change, or didn't change enough to make a difference in
> access plans, wouldn't you agree that the exercise of gathering statistics
> was futile and useless.

I didn't dispute that. My point is, it shouldn't matter.

One thing that may help to see another perspective is to consider the lone DBA in a medium sized company that doesn't have the luxury of spending much time trying to determine if stats should be run. That DBA may also be involved in development projects, maintenance and monitoring of the databases in 3 sites, maintaining licenses, running change control, and a few other goodies. (yes, that would be me)

I automate as much of this as possible.

No, stats don't need to be run frequently, but it shouldn't really matter if they are run periodically. I administer several SAP databases and use brconnect to manage the statistics. It does a fair job of only running dbms_stats when needed. ie. though there are 22k tables it may only cause 30 of them to be analyzed, as it did this week.

brconnect is setup to run weekly, I check the logs occasionaly, one less thing to worry about. In a previous job we had a large team of DBA's and it was possible ( I had more time) to exercise more control over things like this.

Other non-SAP database have automated jobs to run collect the stats weekly. Hasn't caused a problem so far, while on one of those databases, going for some time without an analyze does seem to cause some problems, IIRC.

There are ideal ways to do things, but sometimes compromises are necessary. In a situation like this I will either automate stats gathering, or it likely will never be done.

And then there's the fleet of RX-7's in my garage that demand attention, and of late I much prefer working on them to running maintenance Oracle jobs at work. :)

Jared

>
>
> >Is that not true, or is there some other piece missing here?
>
> If the current statistics produce access plans that render the required
> data in the time stipulated by your SLAs, why the urge to change something.
> You are getting dangerously close to symptoms of CTD.
> If, on the other hand, there are performance problems, they should be
> analyzed case by case and at that time the possibility that newer
> statistics will change the access plan and improve the performance should
> be explored.
>
>
> >Jared
> >
> >--
> >Author: Jared Still
> > INET: jkstill_at_cybcon.com
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Wolfgang Breitling
> INET: breitliw_at_centrexcc.com
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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