RE: migration from RBO to CBO

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Mon, 17 Sep 2001 10:03:46 -0700
Message-ID: <F001.00390744.20010917092025@fatcity.com>

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: EShevtsov_at_flagship.ru

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: 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.com
--

Author: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------