ALL_ROWS is not always good for OLTP, is this an OLTP environment? Perhaps
FIRST_ROWS is better, perhaps not.
I would recommend sticking with CBO, properly tuned, it is generally faster
in all cases, although you sometimes have to use hints to make up for an
incorrect plan choice. Like you said, a lot of the new features truly
benefit from Cost based optimization.
Plan stability and outlines are a good idea if you feel you have good plans
and you do not want them to change, but a lot of times you may want them to
chance as data grows, especially when it is unexpected.
Make sure you look at the INDEX_ parameters which drastically increase the
effectiveness of the CBO. Also be concerned with parameters like
DB_FILE_MULTIBLOCK_READ_COUNT which also affect which path CBO takes.
I would recommend using DBMS_STATS instead of Analyze table due to parallel
use and bugs. You may want to also consider 'monitor' feature of tables to
assist in determining stale statistics with DBMS_STATS and get a good idea
of usage. When and how often plays a big factor in how you do statistics.
In a smaller environment, you have more lead way, in the larger environments
your decisions become more difficult.
I generally try to use hints as a last resort only, although they are needed
in times, not always. One interesting trick is to force the database into
RBO and capture outlines, then switch to CBO and use statistics, then you
have certain plans saved so you have the effect of using RBO for them.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
Sent: Saturday, September 15, 2001 3:00 PM
To: Multiple recipients of list ORACLE-L
Hi List,
I'm going to change my job and I'll have to inherite one DB which doesn't
seem to be very good
designated. I haven't known much about the application yet and I can't post
many details to you but
I would appreciate your general advices.
Despite of the optimizer_mode set to RULE at instance level, they use many
new features
(partitioning, bitmap indexes, function-based indexes, etc). It seems the
system is overloaded with
indexes and optimizer hints. They collect statistics regularly. Molotov's
cocktail! So I expect some
hybrid behaviour of the optimizer. The sql statements don't seem to be
especially optimized for RBO
and give real trouble for system (my first feeling).
Before we could start any sql tuning we have to decide which road to choose.
My current
understanding is that we need to fix existing execution plans with 'Plan
Stability' option and set
optimizer_mode = all_rows. Then ask the developers to use CBO in new
development and gradually test
and rewrite existing code. What would be your advice? Any experience?
Also I would like to know if anybody use the 'plan stability' feature. Is it
stable itself ? :) The
system is 8.1.7.0.1 on Red Hat
Thanks,
Ed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shevtsov, Eduard
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
