I am hoping someone can point me in the right direction.

I have a VB 6 application that uses ADODB to connect to Oracle 10g.

The Optimization Mode is set to CHOOSE in the Oracle Admin.

The majority of the SQL statements are predefined in the code. For any given statement, a "where" clause is added/set and the statement is executed.

When these statements get to Oracle, they all have a hint inserted. Specifically the /*+ RULE */ hint.

I’m trying to find out what is inserting this hint and how to disable it.

Anything would help!

Thanks in advance.

your application will be doing it

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

http://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

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.

> And if there are no statistics (or not enough) that Oracle itself will insert the RULE hint?

If any table in the query has stats, Oracle will re-analyse the missing stats, way slow.

Start by verifying your auto stats collection in 10g:


http://www.dba-oracle.com/t_10g_dbms_stats.htm

> Oracle CHOOSEs what to do, based on the statistics it has for the table?

It chooses between first_rows and all_rows.

You should change optimizer_mode from "choose" to first_rows or all_rows.

Use first_rows for online trans systems, and all_rows for warehouse . . .

http://www.dba-oracle.com/art_sql_tune.htm

And look at 10g upgrade gotchas:

http://www.dba-oracle.com/t_slow_performan...ter_upgrade.htm