Mogens,
Quite a controversy you started here.
As always. ;)
I must admit this is the first time I've heard this come up.
As Jonathan stated, it does seem somewhat like rebuilding indexes.
But then again, if re-collecting statistics causes your database performance
to suddenly become very bad, it seems at first cut there are only two conclusions
you can come to.
1) CBO is broke if fresh statistics result in poor performance
2) statistics were collected at a time when temporary conditions
created different statistics than what would normally appear.
ie. at night when batch jobs are being run with lots of DML.
No. 2 seems the likely candidate, unless of course, it's both 1 and 2.
If just 2, then from a users perspective, it would seem most appropriate
to have statistics collected during the day, when people are banging
away on the OLTP stuff.
But then, might that play havoc with the batch jobs?
How about 2 sets of statistics. Import the OLTP stats in the morning for
the users, and the BATCH stats at night for the batch jobs.
I'm not sure if I should laugh at that, or not.
Jared
|
| Mogens Nørgaard <mln@miracleas.dk>
Sent by: ml-errors@fatcity.com
12/30/2003 02:34 AM
Please respond to ORACLE-L
|
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
cc:
Subject: 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@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@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:
INET: Jared.Still_at_radisys.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).
Received on Tue Dec 30 2003 - 16:29:26 CST
