Re: rm RULE based optimizer != GOOD IDEA

  • From: Robyn <robyn.sands@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 22 Apr 2005 13:12:19 -0400

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@xxxxxxxxxx> wrote:
> Well, I guess (and guess is about all anyone except the decision makers c=
an=3D
>  do here) that the QA etc. has now finished for the RBO so my argument is=
 r=3D
> etrospective.  However, having just the RULE hint would mean that you nee=
d =3D
> virtually the entire RBO behind it anyway, wouldn't it?  I can't see how =
it=3D
>  would work otherwise.
>=20
> 10g does have something towards what you're talking about BTW.  You can t=
el=3D
> l the CBO to work in normal mode, or you can tell it "I really want you t=
o =3D
> do a lot more work and validate what the best path is."  It's something c=
al=3D
> led the Automatic Tuning Optimizer (see http://www.oracle.com/technology/=
pr=3D
> oducts/manageability/database/pdf/twp03/TWP_manage_automatic_SQL_tuning.p=
df=3D
> ) for an overview.
>=20
> And you're right.  I'll call you lazy.  :)
>=20
> =20
>=20
> Pete
--
//www.freelists.org/webpage/oracle-l

Other related posts: