RE: rm RULE based optimizer != GOOD IDEA

  • From: "Cary Millsap" <Cary.Millsap@xxxxxxxxxx>
  • To: "'Oracle-L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 22 Apr 2005 16:45:12 -0500

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Robyn
Sent: Friday, April 22, 2005 12:36 PM
To: Post, Ethan
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxx> 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@xxxxxxxxx]
> Sent: Friday, April 22, 2005 12:12 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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
>
--
//www.freelists.org/webpage/oracle-l

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

Other related posts: