2 Replies Latest reply on Jul 30, 2009 5:11 PM by Tarh Ik

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

    Tarh Ik
      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 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))
      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)
      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)
      WHERE (a.sequence=1 OR a.sequence=0) AND a.OBJECT_NAME = 'MYTABLE' AND a.OWNER = 'MYSCHEMA' ORDER BY 2,3

      Best Regards,


      Edited by: user10165637 on Jul 28, 2009 1:29 PM
        • 1. Re: Oracle ODBC Driver sqora32.dll with /*+ RULE */ base hints
          gdarling - oracle
          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,
          • 2. Re: Oracle ODBC Driver sqora32.dll with /*+ RULE */ base hints
            Tarh Ik
            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,