This discussion is archived
2 Replies Latest reply: Jul 30, 2009 10:11 AM by 714417 RSS

Oracle ODBC Driver 10.02.00.04 sqora32.dll with /*+ RULE */ base hints

714417 Newbie
Currently Being Moderated
Hi Everybody,

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

Best Regards,

Manuel

Edited by: user10165637 on Jul 28, 2009 1:29 PM
  • 1. Re: Oracle ODBC Driver 10.02.00.04 sqora32.dll with /*+ RULE */ base hints
    gdarling - oracle Expert
    Currently Being Moderated
    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,
    Greg
  • 2. Re: Oracle ODBC Driver 10.02.00.04 sqora32.dll with /*+ RULE */ base hints
    714417 Newbie
    Currently Being Moderated
    Hi Greg,

    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.

    Best Regards,

    Manuel

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points