RE: Should we stop analyzing?

From: Nicoll, Iain <IAIN.D.NICOLL_at_saic.com>
Date: Tue, 30 Dec 2003 03:34:25 -0800
Message-ID: <F001.005DB41F.20031230033425@fatcity.com>

Mogens,

We've been in the same situation here where analyzing was turned off to stop problems occurring (partly because of Oracle 7 and the fact that histograms were created at a second stage so if the analyze failed part way through the histograms were lost).

Although the data does not change significantly in character once a database is a bit older we have the problem then of how to manage any new tables or indexes which may take a bit of time to reach a more stable character. For this reason as we've just moved to 9i I'd like to see us going back to analyzing every week but only stale statistics.

If the database only had application changes once a year or so then I'd think not analyzing was a more sensible option but I believe I'd seen someone claim (not sure with which version of the optimizer) that the table size etc were also looked at rather than just purely stats so changes in execution plan could still occur.

I suppose all that I'm saying above boils down to IMHO "it depends" but it is certainly an arguable point, though less so in 9i than previous version.

Iain Nicoll

-----Original Message-----
Mogens Nørgaard
Sent: 30 December 2003 10:34
To: Multiple recipients of list ORACLE-L

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

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Nicoll, Iain
INET: IAIN.D.NICOLL_at_saic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------