1 2 Previous Next 29 Replies Latest reply: May 9, 2013 3:11 PM by JustinCave RSS

    ODBC Driver very very slow catalog functions ( table scans ?)

    568998
      10.02.00.03 ODBC driver appears to be doing table scans when executing ODBC catalog functions SQLStatistics, SQLPrimaryKeys, SQLForeignKeys ( imported keys ) and SQLTablePrivileges.

      For example, I get following times in minutes executing catalog function on 300 tables ( MS SQL Server ODBC driver takes about 2 seconds executing some of the same tests ):

      Function ---- Oracle ---- MS SQL Server
      SQLStatistics ---- 2:07 ---- 0:02
      SQLPrimaryKeys ---- 1:17 ---- 0:02
      SQLForeignKeys ---- 14:00 ---- 0:02
      SQLTablePrivileges ---- 2:06 ---- 0:30
        • 1. Re: ODBC Driver very very slow catalog functions ( table scans)
          JustinCave
          - What version of the Oracle database are you using?
          - Is it possible that you've got an older version of the database where statistics were inadvertently gathered on the SYS user's objects?
          - If you query the data dictionary tables in Oracle directly from SQL*Plus with the same criteria, are you seeing similar response times?
          - Can you trace these sessions and see what is taking so long?

          Justin
          • 2. Re: ODBC Driver very very slow catalog functions ( table scans)
            568998
            I am running Oracle 10g Enterprise Edition Release 10.2.0.1.0 on Windows XP 32-bit

            The worst function is SQLForeignKeys where driver is doing full table scans on 4 tables in this case. I was able to get the SQL the driver is executing by executing SQLForeignKeys in my app http://www.zidsoft.com/ and then using Enterprise Manager Console to look at the session details. Here is the sql the driver is executing for SQLForeignKeys for a test table owner_1.test_1.
            The explain plan details in Enterprise Console Manager shows that this query is doing 4 table scans. If I remember correctly, the previous Oracle 9.02.00.65 ODBC driver did not exhibit this performance issues with catalog functions

            SELECT '', a.owner, a.table_name, b.column_name, NULL, c.owner, c.table_name,
            d.column_name, b.position, NULL, decode(c.delete_rule, 'CASCADE', 0,
            'NO ACTION', 3), c.constraint_name, a.constraint_name, ''
            FROM all_constraints a, all_cons_columns b, all_constraints c,
            all_cons_columns d
            WHERE a.owner = b.owner
            AND a.constraint_name = b.constraint_name
            AND c.owner = d.owner
            AND c.constraint_name = d.constraint_name
            AND a.constraint_type = 'P'
            AND c.constraint_type = 'R'
            AND a.constraint_name = c.r_constraint_name
            AND a.owner = c.r_owner
            AND b.position = d.position
            AND c.table_name = 'TEST_1'
            AND c.owner = 'OWNER_1'
            ORDER BY 2, 3, 9


            Here is the SQL for SQLStatistics:

            (SELECT '', T.owner, T.table_name, TO_NUMBER(NULL), '', '', 0, TO_NUMBER(NULL), '', '', num_rows, blocks, NULL FROM ALL_TABLES T WHERE table_name='TEST_1' UNION SELECT '', a.table_owner, a.table_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name, 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.owner = b.index_owner AND a.index_name = b.index_name AND a.table_owner='OWNER_1' AND a.table_name='TEST_1' UNION SELECT '', c.owner, c.synonym_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name, 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL FROM ALL_INDEXES a, ALL_IND_COLUMNS b, ALL_SYNONYMS c WHERE c.synonym_name='TEST_1' AND c.owner='OWNER_1' AND c.table_name = a.table_name AND c.table_name = b.table_name AND c.table_owner= a.table_owner AND c.table_owner= b.table_owner AND a.owner = b.index_owner AND a.index_name = b.index_name ) ORDER BY 4, 5, 6, 8


            SQL for SQLPrimaryKeys ( 4 table scans ):

            SELECT '', b.owner, b.table_name, b.column_name, b.position, b.constraint_name
            FROM all_constraints a, all_cons_columns b
            WHERE b.table_name = 'TEST_1'
            AND b.owner = 'OWNER_1'
            AND a.table_name = 'TEST_1'
            AND a.owner = 'OWNER_1'
            AND a.constraint_type = 'P'
            AND a.constraint_name = b.constraint_name
            ORDER BY b.owner, b.table_name, b.position

            SQLTablePrivileges:
            SELECT '',b.owner,a.table_name,a.grantor,a.grantee,a.privilege,a.grantable FROM ALL_TAB_PRIVS a, ALL_TABLES b WHERE 1=1 AND b.owner = ('OWNER_1') AND b.table_name = ('TEST_1') AND a.table_schema = ('OWNER_1') AND a.table_name = ('TEST_1') AND a.table_schema=b.owner AND a.table_name = b.table_name UNION SELECT '',c.owner,c.synonym_name,a.grantor,a.grantee,a.privilege,a.grantable FROM ALL_TAB_PRIVS a, ALL_TABLES b, ALL_SYNONYMS c WHERE 1=1 AND c.owner = ('OWNER_1') AND c.synonym_name = ('TEST_1') AND c.table_name = b.table_name AND c.table_owner= b.owner AND a.table_schema=c.table_owner AND a.table_name = c.table_name UNION (SELECT '', owner_, table_, grantor_, grantee_, privilege_, grantable_ FROM (SELECT b.owner owner_,b.table_name table_,b.owner grantor_,b.owner grantee_ FROM ALL_TABLES b WHERE b.owner = user AND user = ('OWNER_1') AND b.owner = ('OWNER_1') AND b.table_name = ('TEST_1') UNION ALL SELECT c.owner owner_,c.synonym_name table_,c.owner grantor_,c.owner grantee_ FROM ALL_SYNONYMS c, ALL_TABLES b WHERE c.owner = user AND user = ('OWNER_1') AND c.owner = ('OWNER_1') AND c.synonym_name = ('TEST_1') AND c.table_name = b.table_name AND c.table_owner= b.owner ), (select 'SELECT' privilege_, 'YES' grantable_ FROM DUAL UNION ALL select 'INSERT' privilege_, 'YES' grantable_ FROM DUAL UNION ALL select 'UPDATE' privilege_, 'YES' grantable_ FROM DUAL UNION ALL select 'DELETE' privilege_, 'YES' grantable_ FROM DUAL UNION ALL select 'REFERENCES' privilege_, 'YES' grantable_ FROM DUAL) ) ORDER BY 2, 3, 6
            • 3. Re: ODBC Driver very very slow catalog functions ( table scans)
              JustinCave
              The ODBC driver, though, has no influence on the query plan the database chooses. While it is certainly possible that the actual query Oracle has changed between versions, I would rather strongly doubt that it has changed substantially since the 9.2.0.6.5 driver.

              What version of the Oracle database are you connecting to? Have the SYS and SYSTEM schemas been analyzed in that database?

              If you run the same query from SQL*Plus, are the timings similar?

              Justin
              • 4. Re: ODBC Driver very very slow catalog functions ( table scans)
                568998
                I am connecting to Oracle 10g Enterprise Edition Release 10.2.0.1.0 on Windows XP 32-bit.

                Looking at the change log for ODBC driver version 10.02.00.03, I see that it was supposed to fix slow catalog functions ( but it does not ). Apparently, the SQL queries the driver is using for catalog function have changed from ODBC driver version 9.2.x to 10.x and that's the cause of the problems.

                Feel free to run the SQL query the driver is using for SQLForeignKeys from SQLPlus and do an explain plan you will get the same performance issues due to 4 table scans. It takes about 0.3 seconds to run on my machine instead of milliseconds. So the performance problem is not apparent unless you try to get the foreign keys for a large number of tables. In this case it was taking about 14 minutes to get the foreign keys for 300 tables ( to do a topological sort on the tables ) where it should take no more than 2 seconds in the case of other drivers that are not doing table scans.

                Here is the query the 10.0200.03 driver is using for SQLForeignKeys again:

                SELECT '', a.owner, a.table_name, b.column_name, NULL, c.owner, c.table_name,
                d.column_name, b.position, NULL, decode(c.delete_rule, 'CASCADE', 0,
                'NO ACTION', 3), c.constraint_name, a.constraint_name, ''
                FROM all_constraints a, all_cons_columns b, all_constraints c,
                all_cons_columns d
                WHERE a.owner = b.owner
                AND a.constraint_name = b.constraint_name
                AND c.owner = d.owner
                AND c.constraint_name = d.constraint_name
                AND a.constraint_type = 'P'
                AND c.constraint_type = 'R'
                AND a.constraint_name = c.r_constraint_name
                AND a.owner = c.r_owner
                AND b.position = d.position
                AND c.table_name = 'TEST_1'
                AND c.owner = 'OWNER_1'
                ORDER BY 2, 3, 9
                • 5. Re: ODBC Driver very very slow catalog functions ( table scans)
                  JustinCave
                  I don't have a system on hand that has that many foreign keys defined for a single parent, so I can't replicate your performance problem. Full scans against data dictionary tables, though, are not at all unusual since they're often not real tables but memory structures that can't be indexed. If the query is slow in SQL*Plus, that at least eliminates the ODBC driver itself as the source of the problem and lets us focus on the query.

                  If you connect to the same 10.2.0.1 Oracle database using the 9.2.x Oracle ODBC driver, do you see a different query being executed? I'm hard pressed to come up with something that would be obviously faster than joining these four data dictionary tables. I know that some catalog queries were modified in the 10.2.0.3 release, but this query looks relatively familiar to me.

                  Are the "other drivers that are not doing table scans" connecting to Oracle databases? Or are you comparing the performance between Oracle and other databases?

                  If there has been a change in the query Oracle's ODBC driver is issuing for this particular function, it may be worthwhile to log a support request with Oracle Support to see if they can tell you what changed and why. It's possible they can either come up with a workaround or get you a one-off patch to resolve the issue.

                  If your schema is static (or at least relatively static), you could cheat by creating physical tables named ALL_CONSTRAINTS, ALL_CONS_COLUMNS, ALL_CONSTRAINTS, and ALL_CONS_COLUMNS, populating them with data from the real data dictionary tables, and indexing them appropriately. Querying physical tables is generally much more efficient than querying the virtual tables in the data dictionary, but this is obviously a bit of a hack.

                  Justin
                  • 6. Re: ODBC Driver very very slow catalog functions ( table scans)
                    568998
                    You don't have to have a table with foreign keys to run the query. The table does not have to exist. The table name and schema names are just literals. You can execute this query 300 times on table 'TEST_1' schema 'OWNER_1' to duplicate the results.

                    DataDirect 5.2 Oracle ODBC driver does not exhibit this performance issue and is inline with other dbms odbc drivers catalog function performance. Seems obvious that you will get poor performance when a query you are executing is doing table scans on 4 tables.

                    I would like to report this issue to Oracle but I have no support contract. I will refer my customers to Oracle support when encountering this issue. Thanks.
                    • 7. Re: ODBC Driver very very slow catalog functions ( table scans)
                      JustinCave
                      While it does take a couple seconds for me to run this query on my local system the first time, subsequent executions happen more or less instantly (less than 0.01 seconds) using SQL*Plus. Are you saying that you don't see any improvement no matter how many times you execute the same query?

                      Out of curiosity, can you trace what query the DataDirect driver (or the Oracle 9.2.x driver if that version is perfoming better for you) uses for the same function?

                      Can you post the exact query plan you're seeing on your system?

                      Out of curiosity, can you try adding a RULE hint to the query and running it from SQL*Plus, i.e.
                      SELECT /*+ RULE */
                             '', a.owner, a.table_name, b.column_name, NULL, c.owner,
                             c.table_name, d.column_name, b.position, NULL,
                             decode(c.delete_rule, 'CASCADE', 0, 'NO ACTION', 3),
                             c.constraint_name, a.constraint_name, ''
                        FROM all_constraints a,
                             all_cons_columns b,
                             all_constraints c,
                             all_cons_columns d
                      WHERE a.owner = b.owner
                         AND a.constraint_name = b.constraint_name
                         AND c.owner = d.owner
                         AND c.constraint_name = d.constraint_name
                         AND a.constraint_type = 'P'
                         AND c.constraint_type = 'R'
                         AND a.constraint_name = c.r_constraint_name
                         AND a.owner = c.r_owner
                         AND b.position = d.position
                         AND c.table_name = 'TEST_1'
                         AND c.owner = 'OWNER_1'
                      ORDER BY 2, 3, 9
                      Does that improve the performance on your system?

                      Justin
                      • 8. Re: ODBC Driver very very slow catalog functions ( table scans)
                        568998
                        After you execute the query the first time, subsequent execution of the query are FASTER. Oracle must be caching the resultset/data pages. This does not help me because I am executing calling SQLForeignKeys ( executing the query ) on 300 different tables.

                        YES. If I add /*+ RULE*/ to the query then the first execution of the query is significantly improved. Without the RULE -- first execution is 3.34 second, with rule, first execution is 0.5 seconds.

                        Here is the DataDirect ODBC driver SQLForeignKeys sql:

                        SELECT /*+ RULE */ a.owner, a.table_name, b.column_name, c.owner, c.table_name,
                        d.column_name, b.position, c.delete_rule, c.constraint_name,
                        a.constraint_name
                        FROM sys.dba_constraints a, sys.dba_cons_columns b, sys.dba_constraints c,
                        sys.dba_cons_columns d
                        WHERE a.owner = b.owner
                        AND a.constraint_name = b.constraint_name
                        AND c.owner = d.owner
                        AND c.constraint_name = d.constraint_name
                        AND a.constraint_type = 'P'
                        AND c.constraint_type = 'R'
                        AND a.constraint_name = c.r_constraint_name
                        AND a.owner = c.r_owner
                        AND b.position = d.position
                        AND c.owner = 'OWNER_1'
                        AND c.table_name = 'TEST_1'


                        Here is the execution plan for the Oracle SQLForeignKeys ODBC driver query:
                        SELECT '', a.owner, a.table_name, b.column_name, NULL, c.owner, c.table_name,
                        d.column_name, b.position, NULL, decode(c.delete_rule, 'CASCADE', 0,
                        'NO ACTION', 3), c.constraint_name, a.constraint_name, ''
                        FROM all_constraints a, all_cons_columns b, all_constraints c,
                        all_cons_columns d
                        WHERE a.owner = b.owner
                        AND a.constraint_name = b.constraint_name
                        AND c.owner = d.owner
                        AND c.constraint_name = d.constraint_name
                        AND a.constraint_type = 'P'
                        AND c.constraint_type = 'R'
                        AND a.constraint_name = c.r_constraint_name
                        AND a.owner = c.r_owner
                        AND b.position = d.position
                        AND c.table_name = 'TABLE_X'
                        AND c.owner = 'OWNER_X'
                        ORDER BY 2, 3, 9

                        Optimizer Mode Used:

                             ALL_ROWS

                        Total Cost:

                             113

                        Execution Steps:
                        Step #     Step Name
                        104          SELECT STATEMENT
                        103          SORT [ORDER BY]
                        102          FILTER
                        85          NESTED LOOPS [OUTER]
                        83          NESTED LOOPS
                        80          NESTED LOOPS
                        78          NESTED LOOPS
                        75          NESTED LOOPS [OUTER]
                        73          NESTED LOOPS
                        70          NESTED LOOPS
                        68          NESTED LOOPS
                        65          NESTED LOOPS [OUTER]
                        62          NESTED LOOPS [OUTER]
                        59          NESTED LOOPS [OUTER]
                        56          NESTED LOOPS [OUTER]
                        54          NESTED LOOPS
                        51          NESTED LOOPS [OUTER]
                        49          NESTED LOOPS
                        46          NESTED LOOPS
                        43          NESTED LOOPS
                        40          NESTED LOOPS
                        37          NESTED LOOPS
                        34          NESTED LOOPS
                        31          NESTED LOOPS
                        28          NESTED LOOPS [OUTER]
                        25          NESTED LOOPS
                        22          NESTED LOOPS [OUTER]
                        20          NESTED LOOPS
                        17          NESTED LOOPS [OUTER]
                        15          NESTED LOOPS
                        12          NESTED LOOPS
                        9          HASH JOIN
                        7          NESTED LOOPS
                        5          NESTED LOOPS
                        2          SYS.USER$ TABLE ACCESS [BY INDEX ROWID]
                        1          SYS.I_USER1 INDEX [UNIQUE SCAN]
                        4          SYS.USER$ TABLE ACCESS [BY INDEX ROWID]
                        3          SYS.I_USER1 INDEX [UNIQUE SCAN]
                        6          SYS.CDEF$ TABLE ACCESS [FULL]
                        8          SYS.CON$ TABLE ACCESS [FULL]
                        11          SYS.CON$ TABLE ACCESS [BY INDEX ROWID]
                        10          SYS.I_CON1 INDEX [UNIQUE SCAN]
                        14          SYS.CDEF$ TABLE ACCESS [BY INDEX ROWID]
                        13          SYS.I_CDEF1 INDEX [UNIQUE SCAN]
                        16          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        19          SYS.OBJ$ TABLE ACCESS [BY INDEX ROWID]
                        18          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        21          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        24          SYS.CON$ TABLE ACCESS [BY INDEX ROWID]
                        23          SYS.I_CON2 INDEX [UNIQUE SCAN]
                        27          SYS.USER$ TABLE ACCESS [CLUSTER]
                        26          SYS.I_USER# INDEX [UNIQUE SCAN]
                        30          SYS.USER$ TABLE ACCESS [CLUSTER]
                        29          SYS.I_USER# INDEX [UNIQUE SCAN]
                        33          SYS.USER$ TABLE ACCESS [BY INDEX ROWID]
                        32          SYS.I_USER1 INDEX [UNIQUE SCAN]
                        36          SYS.USER$ TABLE ACCESS [BY INDEX ROWID]
                        35          SYS.I_USER1 INDEX [UNIQUE SCAN]
                        39          SYS.CON$ TABLE ACCESS [BY INDEX ROWID]
                        38          SYS.I_CON1 INDEX [UNIQUE SCAN]
                        42          SYS.CON$ TABLE ACCESS [BY INDEX ROWID]
                        41          SYS.I_CON1 INDEX [UNIQUE SCAN]
                        45          SYS.CDEF$ TABLE ACCESS [BY INDEX ROWID]
                        44          SYS.I_CDEF1 INDEX [UNIQUE SCAN]
                        48          SYS.CDEF$ TABLE ACCESS [BY INDEX ROWID]
                        47          SYS.I_CDEF1 INDEX [UNIQUE SCAN]
                        50          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        53          SYS.OBJ$ TABLE ACCESS [BY INDEX ROWID]
                        52          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        55          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        58          SYS.CON$ TABLE ACCESS [BY INDEX ROWID]
                        57          SYS.I_CON2 INDEX [UNIQUE SCAN]
                        61          SYS.USER$ TABLE ACCESS [CLUSTER]
                        60          SYS.I_USER# INDEX [UNIQUE SCAN]
                        64          SYS.USER$ TABLE ACCESS [CLUSTER]
                        63          SYS.I_USER# INDEX [UNIQUE SCAN]
                        67          SYS.CCOL$ TABLE ACCESS [BY INDEX ROWID]
                        66          SYS.I_CCOL1 INDEX [RANGE SCAN]
                        69          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        72          SYS.COL$ TABLE ACCESS [BY INDEX ROWID]
                        71          SYS.I_COL3 INDEX [UNIQUE SCAN]
                        74          SYS.ATTRCOL$ TABLE ACCESS [CLUSTER]
                        77          SYS.CCOL$ TABLE ACCESS [BY INDEX ROWID]
                        76          SYS.I_CCOL1 INDEX [RANGE SCAN]
                        79          SYS.I_OBJ1 INDEX [UNIQUE SCAN]
                        82          SYS.COL$ TABLE ACCESS [BY INDEX ROWID]
                        81          SYS.I_COL3 INDEX [UNIQUE SCAN]
                        84          SYS.ATTRCOL$ TABLE ACCESS [CLUSTER]
                        88          NESTED LOOPS
                        86          SYS.I_OBJAUTH1 INDEX [RANGE SCAN]
                        87          SYS.X$KZSRO FIXED TABLE [FULL]
                        89          SYS.X$KZSPR FIXED TABLE [FULL]
                        92          NESTED LOOPS
                        90          SYS.I_OBJAUTH1 INDEX [RANGE SCAN]
                        91          SYS.X$KZSRO FIXED TABLE [FULL]
                        93          SYS.X$KZSPR FIXED TABLE [FULL]
                        96          NESTED LOOPS
                        94          SYS.I_OBJAUTH1 INDEX [RANGE SCAN]
                        95          SYS.X$KZSRO FIXED TABLE [FULL]
                        97          SYS.X$KZSPR FIXED TABLE [FULL]
                        100          NESTED LOOPS
                        98          SYS.I_OBJAUTH1 INDEX [RANGE SCAN]
                        99          SYS.X$KZSRO FIXED TABLE [FULL]
                        101          SYS.X$KZSPR FIXED TABLE [FULL]
                        • 9. Re: ODBC Driver very very slow catalog functions ( table scans)
                          JustinCave
                          It looks like both the Oracle driver and the DataDirect driver are issuing functionally the same query, the DataDirect driver is just forcing the old rule-based optimizer.

                          This would seem to imply that the dictionary and fixed object statistics on your database are missing and/or incorrect. Can you try the following for me in SQL*Plus (you probably need to be SYSDBA to have privileges to do this
                          exec DBMS_STATS.DELETE_DICTIONARY_STATS();
                          exec DBMS_STATS.DELETE_FIXED_OBJECT_STATS();
                          Once that's done, re-run the query. Next
                          exec DBMS_STATS.GATHER_DICTIONARY_STATS();
                          exec DBMS_STATS.GATHER_FIXED_OBJECT_STATS();
                          and run the query again.

                          Does either removing or regathering system statistics resolve the problem?

                          Justin
                          • 10. Re: ODBC Driver very very slow catalog functions ( table scans)
                            568998
                            Rebuilding statistics did not help. Same results as before. I could not execute the second proc:
                            exec DBMS_STATS.DELETE_FIXED_OBJECT_STATS();

                            resulted in an error:

                            ERROR at line 1:
                            ORA-06550: line 1, column 18:
                            PLS-00302: component 'DELETE_FIXED_OBJECT_STATS' must be declared
                            ORA-06550: line 1, column 7:
                            PL/SQL: Statement ignored

                            Another difference between Oracle driver and DataDirect drivers is that they are using different system views:
                            FROM all_constraints a, all_cons_columns b, all_constraints c,
                            all_cons_columns d

                            FROM sys.dba_constraints a, sys.dba_cons_columns b, sys.dba_constraints c,
                            sys.dba_cons_columns d
                            • 11. Re: ODBC Driver very very slow catalog functions ( table scans)
                              JustinCave
                              DBA_CONSTRAINTS, USER_CONSTRAINTS, and ALL_CONSTRAINTS (and all DBA_, USER_, ALL_ views) are really hitting the same dictionary tables and fixed objects under the covers. If anything, using the ALL_ versions should be more efficient (in addition to being more correct) since it weeds out objects you don't have access to see.

                              I typo'd the procedure name. It should be DELETE_FIXED_OBJECTS_STATS (and objects rather than object in the gather procedure as well).

                              Is there any change in performance when you delete both dictionary and fixed object statistics?

                              Justin
                              • 12. Re: ODBC Driver very very slow catalog functions ( table scans)
                                568998
                                Executed both procs and no change in performance.

                                I tested the DataDirect sql query without the RULE directive and got significantly better performance than the Oracle query ( 0.765 sec without RULE, 0.593 with RULE ).

                                Oracle driver query 3.297 sec without RULE, 0.610 with RULE ). At this point seems that including the RULE directive in the Oracle query would improve performance about 5 folds I will try to re-install Oracle 9.2.x driver to double check if it included the RULE directive in its sql.

                                Can't install 9.2.x odbc driver because Oracle Universal Installer for version 10g would not install 9.2.x driver. But having used the 9.2.x driver before, I did not experience these performance issues with the 9.2.x driver.

                                Message was edited by:
                                Farid_Z
                                • 13. Re: ODBC Driver very very slow catalog functions ( table scans)
                                  JustinCave
                                  Could you also try deleting and gathering system statistics (DBMS_STATS.GATHER_SYSTEM_STATS/ DBMS_STATS.DELETE_SYSTEM_STATS)?

                                  The only other thing I'd add is that Oracle's query would look to be more functionally correct than DataDirect's, so Oracle may have sacrificed a bit of performance for accuracy (though I'm not seeing meaningful performance differences between the various options on my machine). DataDirect's query could possible return objects that the application didn't have access to (DBA_ tables have information about every object in the database, ALL_ tables have information only about the objects a particular user has permission to access), which can cause some applications to abort when they try to work with those objects in the future.

                                  Justin
                                  • 14. Re: ODBC Driver very very slow catalog functions ( table scans)
                                    568998
                                    I agree the Oracle query is more correct. However, the current query is useless for an application that is trying to do a topological sort on a schema tables via SQLForeignKeys. On my high-end machine AMD X2 2.4 GHz 4 MB RAM it takes 14 minutes to get foreign keys info for 300 tables ( MS SQL Server takes 2 seconds! ), so seems that some work needs to be done by the driver to eliminate/reduce table scans.

                                    Thanks Justin for looking into this. I am hoping the next release of Oracle ODBC driver would address this issue.
                                    1 2 Previous Next