Should we stop analyzing?

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