Analyzing over and over again might make your system unstable, because
the optimizer each time might choose a different approach. But. If you
never update/delete/your data after the initial load including initial
analyze, performance will be consistent, and no surprises will hurt you.
Instead of stopping analyzing alone, I would suggest to stop changing the
contents of your database at all ;-).
Is analyzing over and over again not one of the symptoms of CTD? I would
not analyze weekly, or because x % of the data has changed. I would
analyze when response times degrade. Then you can search for the cause
(trace the SQL involved) and do some analyzing, when appeared
necessary.
I agree with Dave, except for the fact that an initial load might give a
non-common distribution of the data compared to the more-or-less
stabilized situation after a period of using/changing the contents. Most
tables will stabilize after some time. You should reanalyze then, just
the first time after initial load is not enough.
Regards, Carel-Jan
===
If you think education is expensive, try ignorance. (Derek Bok)
===
At 02:34 30-12-03 -0800, 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@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: Carel-Jan Engel
INET: cjpengel.dbalert_at_xs4all.nl
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 - 04:59:30 CST
