Re: Should we stop analyzing?

Now there's a thread from my heart. I have been saying and practicing (where I'm allowed to as a outside contractor) that for years. I am dead against regularly scheduled analyze jobs - "it must be Sunday because the analyze is running" - but it is sometimes hard to convince the resident DBAs of the futility and even outright danger of the practice. In one system for which I was the DBA for several years most of the tables have not been analyzed sine May 2001 when the system was upgraded to 8i. Even the yearly partitions are not re-analyzed when they are split off the maxvalue partition. I just copy the statistics from a prior year partition. There are some tables where the histograms on certain columns need to be re-calculated every night because of an update that changes the data distribution completely ( the column values are ever increasing and the new most frequently occurring value is larger than the previous maximum value ).

For me the bottom line is
you need to know your system(s) and what is required, but don't just blindly analyze on a schedule for the sole purpose of "keeping the stats up-to-date". If you analyze, there must be a (documented) reason for it and that reason must be tied to improving or preserving the response time of the application or parts of the application and not "because it is the weekend and I have the time and resources to do it".

At 03:34 AM 12/30/2003, you wrote:

>Friends,
>
>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?
>
>Let me clarify:
>
>I've always told people that using the 'monitoring' option (alter table X
>monitoring in 8i, plus alter index I monitoring in 9i) was a good thing,
>because they would make sure that after a certain amound of data changes
>you got fresh stats (after, of course, using
>dbms_stats.gather_stale_statistics, etc. on the collected objects). We can
>always discuss whether the 10% threshold that gather_stale_statistics is
>based on is sound or not, but it can be as good as any other number.
>Except 42 :).
>
>But then I listened to Dave Ensor at the UKOUG conference, and he said
>roughly this:
>
>* Stop analyzing after the first analyze. It's the new stats that cause
>the optimizer to change execution plans.
>* "I know that big tables tend to stay big. Small tables stay small.
>Unique indexes stay unique and non-unique indexes stay non-unique..."
>* If the data changes A LOT you should of course re-analyze.
>
>It made terrific sense in one respect to let the stats stay the same, thus
>letting the optimizer have access to the same information, thus choosing
>the same execution plan instead of changing it constantly. On the other
>hand it was irritating, because I had always beleived (and said) the
>opposite. Even more frustrating was Anjo's grin afterwards and his "Yeah,
>of course you shouldn't analyze all the time" remark. Hrmf. So everybody
>else knew but me. Typical.
>
>Looking back, I can recall several places where they analyzed every
>weekend, and on Monday the system could very well behave differently.
>Makes sense if the optimizer has some new/different information to consider.
>
>On the other hand, it feels so intuitively right to constantly have
>up-to-date stats, doesn't it?
>
>I'd like to know what practical and philosofical ideas you guys have on
>this topic.
>
>Best regards - and Happy New Year,
>
>Mogens
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
> INET: mln_at_miracleas.dk
>
>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).

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