I have a problem with SQL queries with /*+ RULE */ base hints being posted to an Oracle database. I have reason to believe that these queries are generated by the Oracle ODBC Driver itself. Although I don't have a performance problem per se, I need to reduce these hints to a minimum so we can fine-tune the database as a whole.
* The views that are hit are always the same: All_Objects, All_Arguments, All_Synonyms,
* We are using Oracle ODBC driver 10.02.00.04 for Windows, the database server is a RS6000 with AIX and Oracle 10g,
* Aside Oracle Development tools, there are no applications, reports or similar gadgets that query the tables described above.
I would really appreciate any help about this issue.
Here is an example of the type of queries that I'm writing about:
SELECT /*+ RULE */ '', b.owner, decode (b.object_type, 'PACKAGE', CONCAT( CONCAT (b.object_name, '.'), a.object_name), b.object_name), NULL, NULL, NULL, NULL, decode (b.object_type, 'PACKAGE', decode(a.position, 0, 2, 1, 1, 0), decode(b.object_type, 'PROCEDURE', 1, 'FUNCTION', 2, 0))
FROM ALL_ARGUMENTS a, ALL_OBJECTS b
WHERE ( b.object_type = 'PROCEDURE' OR b.object_type = 'FUNCTION' ) AND b.object_id = a.object_id AND (a.sequence=1 OR a.sequence=0) AND b.OBJECT_NAME = 'MYTABLE' AND b.OWNER = 'MYSCHEMA' UNION
SELECT /*+ RULE */ '', b.owner,b.object_name,NULL, NULL, NULL, NULL,decode(b.object_type, 'PROCEDURE', 1, 'FUNCTION', 2, 0)
FROM ALL_OBJECTS b
WHERE (b.object_type = 'PROCEDURE' OR b.object_type = 'FUNCTION') AND b.OBJECT_NAME = 'MYTABLE' AND b.OWNER = 'MYSCHEMA' UNION
SELECT /*+ RULE */ distinct '', a.owner,CONCAT(CONCAT (a.package_name, '.'), a.object_name),NULL, NULL, NULL, NULL,decode(a.position, 0, 2, 1, 1, 0)
FROM ALL_ARGUMENTS a
WHERE (a.sequence=1 OR a.sequence=0) AND a.OBJECT_NAME = 'MYTABLE' AND a.OWNER = 'MYSCHEMA' ORDER BY 2,3
Edited by: user10165637 on Jul 28, 2009 1:29 PM
You should find a "disable rule hints" option in the DSN config applet to resolve that.
If you're using MSAccess you'll probably need to refresh the table links with Linked Table Manager, as MSAccess caches DSN settings.
Hope it helps,
Thank you for your answer. One of the things that we did when we migrated to Oracle 10g was to ensure that the "Disable Rule Base Hints" flag, located in the "Work-arounds" tab in the ODBC DSN window is checked. We previously refreshed all Microsoft's Access databases to use this new DSN connection. Still, the hints are there.
However... we may have been able to link these calls to these Oracle System Catalog views, with a custom application that launces Crystal Reports.
Although we all know that Crystal Reports also caches ODBC DSN configuration settings, I must say that these same Crystal Reports do not generate hints when called from their InfoView server.
We will proceed to modify the custom application, overridding the dissable rule-base hint work-around with the string "DRH=T" on the connection string. Next week we will know if this works.