This discussion is archived
1 2 Previous Next 29 Replies Latest reply: May 9, 2013 1:11 PM by Justin Cave RSS

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

568998 Newbie
Currently Being Moderated
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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    - 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 Newbie
    Currently Being Moderated
    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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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