RE: Cost vs Rule

Here's a convincing stat for your architect:

There will be no RBO in a future version of Oracle. That version is not far away (conjecture is perhaps as early as 10). As often occurs, this is a case of do what we say, not what we do, since RBO is still used in the data dictionary tables. However that is a very special case of RBO for a primarily clustered, legacy schema that is in the process of being migrated to CBO. You can bet that when the data dictionary moves to CBO, RBO is going to go away.

Now if you want to tie your application to a feature that severely limits the new features you can use, reduces scalability (forget bitmap indexes, partition pruning, function-based indexes, etc.), and is strongly discouraged by the vendor, then anchor yourself to the rock of RBO. Just make sure you don't have a short chain and a rising tide.

While your architect is at it, make sure they use lots of the LONG datatype. It's really inconvenient and not long for this world too.

And by the way, CBO works better than RBO in the great majority of cases. You need to investigate why your PK index is not being picked up in your query, not toss the entire current technology and fall back to heuristic optimization. As someone else mentioned, a hint is just a comment when viewed by other RDBMS. You can and should use hints and init.ora parameters to tune server and optimizer behavior.

Regards,
Chris Gait

On 13 Mar 2002, at 7:08, Magaliff, Bill wrote:

Date sent:      	Wed, 13 Mar 2002 07:08:25 -0800
To:             	Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Send reply to:  	ORACLE-L_at_fatcity.com
Organization:   	Fat City Network Services, San Diego, California

> yes, everything analyzed.
>
> sr tech arch has decided he wants to use RBO due to predictability in
> production. not much I can do at this point, unless I can really come up
> with convincing stats
>
> -----Original Message-----
> Sent: Wed, March 13, 2002 6:34 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Well, since hints are implemented within comments, I would assume that other
> databases would simply ignore them. If anyone has direct experience, that
> would be interesting. Being completely database-agnostic may play against
> tuning.
> Just a thought. I suppose you analyzed all tables when you were
> testing CBO?
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Thursday, March 07, 2002 2:00 PM
> To: Multiple recipients of list ORACLE-L
>
>
> not much - desire is to keep sql ANSI compliant due to cross-platform issues
> (want to be able to run the app on multiple db's)
>
> -----Original Message-----
> Sent: Thu, March 07, 2002 2:44 PM
> To: Multiple recipients of list ORACLE-L
>
>
> How much have you played with Oracle Hints???
>
> -Joe
>
> --- "Magaliff, Bill" <Bill.Magaliff_at_lendware.com> wrote:
> > I work in a dev shop - most of the sql is canned and pretty basic.
> > We've
> > been running CBO in all of our dev environments, but we have a few
> > long txns
> > that just take forever. At the request of some savvy developers, I
> > turned
> > on RBO, and it brought down execution times dramatically.
> >
> > I've been analyzing affected tables often (we do a lot of bulk
> > load/unload
> > for testing), and have played with partitioning and clustering,
> > particularly
> > on one table that's just a dog. CBO will always do a FTS where RBO
> > uses the
> > PK to retrieve data.
> >
> > Where to go next? I've been unable to alter the costs dramatically
> > enough
> > to make any real difference in execution time.
> >
> > thx
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Magaliff, Bill
> > INET: Bill.Magaliff_at_lendware.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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Try FREE Yahoo! Mail - the world's greatest free email!
> http://mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Joe Raube
> INET: jraube_at_yahoo.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: Magaliff, Bill
> INET: Bill.Magaliff_at_lendware.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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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: Magaliff, Bill
> INET: Bill.Magaliff_at_lendware.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: 
  INET: cjgait_at_earthlink.net

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