RE: Moving From Rule-Based to Cost-Based

Sam,

Ah-ha, Just the question I want to answer :) (To explain further - I will soon be presenting a paper at IOUG appropriately titled 'Safely navigating the RBO-to-CBO minefield', so this is right up my alley!)

Since I cannot distribute or make this paper available to the public (yet), I will stop with giving you the overall stuff that *may* be applicable to you.

• Keep in mind that the RBO is just based on rigid rules, while the CBO allows itself to led down a path that it thinks is the best. The two factors affecting the CBO path are the data (object statistics) you feed the algorithms (that comes from Oracle Corp), along with the various optimizer related init.ora parameters (hidden and visible). At the risk of oversimplifying the issue, I would say that as long as you keep the beast fed with proper statistics and correct init.ora parameters, and you are at a reasonable version such as 8.1.7.x (where x is preferably 4), I would say you are generally Ok.
• If you are using some of the newer Oracle 8+ objects or features, remember that CBO will be invoked regardless of mode (RULE) or presence of stats. PDML/PQ also invokes the CBO.
• If you set CHOOSE, and remote DBs are involved, or your SQL operates across schemas/tables some of which have stats and some not, be aware that the CBO will default with some very ugly defaults on the objects where statistics are not present.
• Be aware that some init.ora can _drastically_ affect plans for the CBO. Misconfigured defaults for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ will make the CBO lean towards FTS. Add high values for SORT_AREA_SIZE and DB_FILE_MULTI_BLOCK_READ_COUNT and you a recipe for disaster... Read Tim Gorman's execellent paper on finding life in the CBO at www.evdbt.com for a thorough explanation for this. I have also seen major parse problems with CBO that have been fixed only by reducing 'OPTIMIZER_MAX_PERMUTATIONS' - this one is worth considering if you are using lots of joins and views that are joins themselves.
• Do collect, store and report Oracle and OS stats and investigate any strange increases before/after the move.
• Look at using DBMS_STATS rather than ANALYZE if you are at 8i +
• Hint only as a last resort. Use Plan outlines if possible, but temporarily.

This is really an exciting journey, despite the hidden 'mines'. As long as you understand how the CBO works and the common pitfalls (and myths) that may crop up, you will be Ok.

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Sam Bootsma [mailto:SamB_at_cpas.com]
> Sent: Friday, April 05, 2002 8:49 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Moving From Rule-Based to Cost-Based
>
>
> Hello All,
>
> At our site, we are use rule based optimization. We are
> careful to write
> SQL that performs well under this mode. Some of our SQL also contains
> hints. When we provide our application to clients, we
> default it to use
> Rule-based. However, some clients may choose to run our
> application in
> cost-based mode. This will be as simple as analyzing all
> tables and indexes
> in our schemas', and changing the mode to use cost based.
>
> My Questions:
> 1. How likely is it that our application will perform worse under
> cost-based than it does under rule-based. What has been the
> experience of
> others on the list who have moved from Rule-based to Cost-based?
> 2. What are some of the underlying reasons for performance
> to decline when
> making such a move? What are some "gotchas" to watch for?
> 3. Is there more that needs to be done to move from rule to
> cost? (Other
> than changing the optimization mode and analyzing tables and indexes)?
>
> Most clients run our database on NT, although some use UNIX.
> Assume Oracle
> 9i. Our database (at client sites) tends to be small, with
> the largest
> being about 25 GB.
>
> Thanks for any suggestions,
>
> Sam Bootsma
> Technical Support Analyst
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sam Bootsma
> INET: SamB_at_cpas.com
>
> 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: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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