RE: rm RULE based optimizer != GOOD IDEA

I have not supported Oracle Apps, but from what I understand it is loaded with hints and I believe some of the using "RULE hint. Seems a little "Do as I say not as I do" from Oracle Corp....eh?

I agree about the spending all day on with stats and explain plans. I have a script to look for blocking locks using v$ views...it ran ok, but a DBA friend made if faster in about 5 minutes by adding a RULE hint.
Maybe I should be going after the base Oracle tables, but I don't have all day.

Finally about execution plans and speed changing of sql using the RBO. If I'm not mistaken many environment changes can effect CBO too!?

Chris Marquez
Oracle DBA
HEYMONitor(tm) - heymonitor.com
"Oracle Monitoring & Alerting Solution"

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Post, Ethan Sent: Friday, April 22, 2005 9:14 AM
To: Niall Litchfield
Cc: Michael Fontana; oracle-l_at_freelists.org Subject: RE: rm RULE based optimizer !=3D GOOD IDEA

Well that would be a big leap. However, I am not saying Oracle should keep the RULE hint because one customer has one query which will benefit from doing so. I am assuming there are hundreds of queries such as this that I will come across in my career and that can be multiplied of 1000's of DBA's elsewhere.

Yes you can achieve the same results without the hint but that may require a lot of work and adding the hint does not. I apologize for not playing the part of the Oracle purist here but sometimes I just want to get the job done and the RULE hint has worked very nicely for me and others on this list numerous times in the past.

What if Oracle decides to drop other hints for indexes, join types etc...?

-----Original Message-----

From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com]=3D20 Sent: Friday, April 22, 2005 4:41 AM
To: Post, Ethan
Cc: Michael Fontana; oracle-l_at_freelists.org Subject: Re: rm RULE based optimizer !=3D3D GOOD IDEA

It seems a big leap to me from "adding the RULE hint helps my one query", to "we should keep the RBO", not least because you can achieve the same effect without the RULE hint.

--=3D20

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

--

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

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