RE: rm RULE based optimizer != GOOD IDEA

From: Marquez, Chris <CMarquez_at_aarp.org>
Date: Fri, 22 Apr 2005 14:35:18 -0400
Message-ID: <7E412C164E6ECB468834A39F31E6E0D4074B5FB3@mbs06dc.na.aarp.int>

Robyn,

>>our developers and dbas add rule hints as a quick fix

Your developers *go back* to add hints to their code!? Wow, lucky you...my developers just let me know=20 it was right the first time they wrote it. :o|

Chris

-----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 1: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

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

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.

As for the oic and oica, implementing system statistics seemed to negate the effect.

Robyn

On 4/22/05, Pete Sharman <peter.sharman_at_oracle.com> wrote:
> Well, I guess (and guess is about all anyone except the decision=20
> makers c=3D

an=3D3D
> do here) that the QA etc. has now finished for the RBO so my argument

> is=3D

r=3D3D
> etrospective. However, having just the RULE hint would mean that you=20
> nee=3D

d =3D3D
> virtually the entire RBO behind it anyway, wouldn't it? I can't see=20
> how =3D

it=3D3D
> would work otherwise.

>=3D20

> 10g does have something towards what you're talking about BTW. You=20
>can t=3D
el=3D3D
> l the CBO to work in normal mode, or you can tell it "I really want=20
> you t=3D

o =3D3D
> do a lot more work and validate what the best path is." It's=20
> something c=3D

al=3D3D
> led the Automatic Tuning Optimizer (see=20
> http://www.oracle.com/technology/=3D

pr=3D3D
> oducts/manageability/database/pdf/twp03/TWP_manage_automatic_SQL_tunin
> g.p=3D

df=3D3D
> ) for an overview.

>=3D20

> And you're right. I'll call you lazy. :)

>=3D20

> =3D20

>=3D20
> Pete

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

Received on Fri Apr 22 2005 - 14:39:30 CDT