Thank you Mr Burleson,
A couple of clarifications: A third party develops the code and hands over all the source code for us (the County) to maintain. So we have full access to the code and to make changes to it.
None of the statements in the code have the /*+ RULE */ in them, nor does the code insert it at any point.
Apparently this has been going on for years and has just come to light with a move from 9i to 10g.
We have the optimizer mode set to CHOOSE in Oracle.
We have looked at your suggestions and are trying them.
Based on what we've read and from your suggestions: Would I be correct to deduce that when Oracle receives the statement from the application, that Oracle CHOOSEs what to do, based on the statistics it has for the table? And if there are no statistics (or not enough) that Oracle itself will insert the RULE hint?
Or is that completely off base?
Thanks again for your reply.
QUOTE (burleson @ Nov 8 2007, 06:15 AM)
Hi Reamer, and welcome to the forum!
>> Iím trying to find out what is inserting this hint and how to disable it.
If it's embedded into the vendor app, you will probably loose support if you changed it, and there many be a reason for them choosing the RBO.
What app is it?
However, all is not lost, there are tricks to change SQL execution plans without changing the code, namely stored outlines and SQL Profiles. I have notes here:http://www.dba-oracle.com/art_builder_sql_mgt.htmhttp://www.dba-oracle.com/oracle11g/oracle...nce_advisor.htm
Here is an ebook on it:http://www.dba-oracle.com/bp/bp_ebook_vendor_tune.htm