Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: rm RULE based optimizer != GOOD IDEA

RE: rm RULE based optimizer != GOOD IDEA

From: Cary Millsap <Cary.Millsap_at_hotsos.com>
Date: Fri, 22 Apr 2005 16:45:12 -0500
Message-ID: <2066.38631114206284.hotsos01.hotsos.com@MHS>


I /think/ the reason RBO is going away is that the elimination of the RBO code path results in a superior product. There is nothing that RBO does that CBO can't do at least as well, if not better.

The problem is that CBO is more operationally complex to manage. It considers many more inputs than RBO did, and if an operator (e.g., DBA) fails to understand many of the subtleties of those inputs, you get worse plans. I agree that the increased operational complexity is a cost that in a lot of places doesn't stimulate an appropriate offsetting benefit. This is a big problem that some combination of Oracle Corporation and all the companies in Oracle's orbit need to fix.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Visit www.hotsos.com for curriculum and schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robyn
Sent: Friday, April 22, 2005 12:36 PM
To: Post, Ethan
Cc: oracle-l_at_freelists.org
Subject: Re: rm RULE based optimizer != GOOD IDEA

I agree that the valid reason for RBO to go is to remove the need for code maintenance, but it has become a crutch in some of our systems and because of that, I personally won't miss it. Never meant to imply that lazy developers justifies Oracle getting rid of it.

As for the baseline, I'd prefer to see traces on the original code and use it as baseline to compare changes throughout a tuning process. Of course I can do this on the queries I know about, but I'm not the only one moving stuff to production.

In your case, you mentioned gathering the type of info I like to see BEFORE adding the rule hint, and I'd throw a party if one of developers did that.

Robyn

On 4/22/05, Post, Ethan <Ethan.Post_at_ps.net> wrote:
> The "our developers are lazy" argument is not a good reason to get rid
> of something. The only valid reason I see so far is that Oracle has a
> significant amount of work to do to maintain the RULE engine. I have
> never seen the code for this so I would not know but will take it for
> granted that this is true (must be more than 20 simple rules).
>=20
> Not sure what you mean by "there is no baseline to tune it from?".
> Would you have a baseline if they added a INDEX hint? You could remove
> the INDEX hint also. You could update the stats on a table and have all
> sorts of changes to queries, some fast some slower, what is your
> baseline there?
>=20
>=20
> -----Original Message-----
> From: Robyn [mailto:robyn.sands_at_gmail.com]
> Sent: Friday, April 22, 2005 12:12 PM
> To: oracle-l_at_freelists.org
> Cc: Post, Ethan; Christian Antognini; Peter Ross Sharman
> Subject: Re: rm RULE based optimizer !=3D GOOD IDEA
>=20
> I for one will be happy to see the rule based optimizer go. Most of
> our developers and dbas add rule hints as a quick fix without taking
> the time to actually figure out the problem. Then, they announce
> that the query completed 'really fast' in dev and move it to
> production, where it may or may not run 'really fast'. Either way,
> there is no baseline to work from if we need to continue to tune it.
> Plus, the rule hint is likely to exist through the next 3 upgrades,
> and no one remembers that it exists, much less knows if it's still
> making things 'really fast'.
>=20
> As a result, we have lots of legacy queries that need to be
> reevaluated and there have been many times when REMOVING the rule hint
> in an old query sped things up considerably.
>=20
> As for the oic and oica, implementing system statistics seemed to
> negate the effect.
>=20
> Robyn
>

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 22 2005 - 17:51:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US