1 2 Previous Next 29 Replies Latest reply: May 9, 2013 3:11 PM by JustinCave Go to original post RSS
      • 15. Re: ODBC Driver very very slow catalog functions ( table scans)
        JustinCave
        I wouldn't wager on Oracle adding /*+ RULE */ hints back into their code, assuming that hint was present in an earlier version of the driver, since the rule-based optimizer has been depricated for some time and Oracle has been working to get rid of those hints in the data dictionary. Since I'm not seeing anywhere near the performance difference you are on the systems I have access to, I'm also not convinced that this is a problem with the ODBC driver. My money at this point is on a problem with your database-- some parameter or incorrect statistics is causing Oracle to generate a poor query plan for this statement.

        Does removing system statistics do anything? Does re-gathering system statistics do anything?

        Justin
        • 16. Re: ODBC Driver very very slow catalog functions ( table scans)
          568998
          Does removing system statistics do anything? Does re-gathering system statistics do anything?
          No, either/both did not help.

          My database is a standard Oracle 10g database and I otherwise do not see any performance issues except for ODBC catalog functions. Feel free to test this on you own database. All you need is a database with about 300 tables ( less is ok but the more tables the more obvious the performance issue ). The tables do not have to have any foreign keys for the test.

          You can download my app from here
          http://www.zidsoft.com/Download/cmpdata_1.3.8.96.msi

          Create an ODBC data source for the database and then in CompareData ( my app ) add a DBMS comparison of the data source to itself.

          Click on the Tables comparison in the tree and then click on the fifth main tab toolbar icon to compare the tables foreign keys between the two data sources and then hit F5 to the execute the foreign key comparison for the tables.

          Typically it should take no more than 1 minute for retrieving all the foreign keys for 300 tables.
          • 17. Re: ODBC Driver very very slow catalog functions ( table scans)
            502698
            Hello,

            I was having the same problem, check this note, maybe it could help you out on solving this issue.

            Note:373129.1
            Slow Performance Exhibited by Oracle ODBC Against 10.2 Oracle Database

            Regards,

            --
            Isaac Gutierrez
            • 18. Re: ODBC Driver very very slow catalog functions ( table scans)
              JustinCave
              Although in that note, the problem was the folks were having poor performance when RULE hints were present rather than the reverse as seems to be happening here.

              It appears that there is a "Disable RULE hint" option in the DSN configuration for the Oracle ODBC driver now. The original poster may be able to enable the RULE hint and get back to the 9.2.x driver's performance on his system, but I wouldn't generalize that result. My system, and most others, are much more efficient without the rule hint than with it, as this note discusses.

              Justin
              • 19. Re: ODBC Driver very very slow catalog functions ( table scans)
                568998
                I have unchecked the Disable Rule Hints option in the Oracle driver data source set up screen and tested again. No change. The 10.02.00.03 ODBC driver is not sending SQL with RULE hints to begin with for SQLForeginKeys ( no RULE hint in the session last executed sql statement ). The database I am connected to is 10.2.0.1.0.

                I would like to get the patch for the driver to test, but this appears to be only available to customers, so I will wait for the next GA release of the driver since this is an identified issue that is being patched. Thanks for the info.
                • 20. Re: ODBC Driver very very slow catalog functions ( table scans)
                  JustinCave
                  Just for clarity, the fix for the bug that's being referenced appears to already be in the driver you're using and appears to be the source of your problem. The fix for the performance bug was to remove the RULE hint from the queries the ODBC driver was sending.

                  On my system, the query without the RULE hint is faster than the query with the RULE hint, and that appears, based on this bug, to be the far more common situation. Your system seems to be the odd-ball where the RULE hint speeds things up. Other than playing around with various combinations of generating and deleting system, dictionary, and fixed object statistics, though, I'm not sure how you would resolve your issue... There is a mention in the bug fix about re-creating a couple of data dictionary views to remove the RULE hint, but I would hesitate to suggest you change SYS objects without working with Oracle support.

                  Justin
                  • 21. Re: ODBC Driver very very slow catalog functions ( table scans)
                    568998
                    Justin, you assert that your system does not exhibit the problem, but you did not present any actual timings to support this. Can you run the following sql on your system to determine how long it takes to execute the SQLForeignKeys driver query on 10 tables? On my system it takes about 31 seconds. I am not a PL/SQL expert, otherwise I would have put the query in a loop ( taking care that each execution is on different table name ).

                    select
                    systimestamp from dual;

                    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;

                    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_2'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_3'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_4'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_5'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_6'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_7'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_8'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_9'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    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_10'
                    AND c.owner = 'OWNER_1'
                    ORDER BY 2, 3, 9;

                    select
                    systimestamp from dual;
                    • 22. Re: ODBC Driver very very slow catalog functions ( table scans)
                      568998
                      One more thing, for the timing test query results to be meaningful, the system needs to actually have a good number of FK constraints defined ( table scans on empty tables are understandably fast ). This also apply for comparing the query performance with and without the RULE hint directive. Here are the row counts for the query referenced tables on my system:

                      'ALL_CONSTRAINTSP COUNT(*)
                      ----------------- ----------
                      all_constraints P 2079
                      all_constraints R 3836
                      all_cons_columns 24239

                      3 rows selected.


                      SELECT 'all_constraints P', count(*)
                      FROM all_constraints a
                      where
                      a.constraint_type = 'P'
                      union all
                      SELECT 'all_constraints R', count(*)
                      FROM all_constraints c
                      where
                      c.constraint_type = 'R'
                      union all
                      SELECT 'all_cons_columns', count(*)
                      FROM all_cons_columns;
                      • 23. Re: ODBC Driver very very slow catalog functions ( table scans)
                        JustinCave
                        I've only got 1/4 to 1/3 of the objects on my laptop that you do, but that should provide for a reasonable data set. I've tried a couple of times to install your application to try to replicate your environment better, but the installer keeps hanging (I assume installation shouldn't take more than 20 or 30 minutes).
                        SCOTT @ nx102 Local> SELECT 'all_constraints P', count(*)
                          2  FROM all_constraints a
                          3  where
                          4  a.constraint_type = 'P'
                          5  union all
                          6  SELECT 'all_constraints R', count(*)
                          7  FROM all_constraints c
                          8  where
                          9  c.constraint_type = 'R'
                        10  union all
                        11  SELECT 'all_cons_columns', count(*)
                        12  FROM all_cons_columns;

                        'ALL_CONSTRAINTSP   COUNT(*)
                        ----------------- ----------
                        all_constraints P        845
                        all_constraints R        382
                        all_cons_columns        6413

                        Elapsed: 00:00:00.51
                        Just to be thorough, I tried running both with and without the RULE hint over every table in my system, a bit over 1500 iterations. As you see, on my system, both queries are pretty quick, but the one without the RULE hint runs in roughly a quarter of the time.
                        SCOTT @ nx102 Local> ed
                        Wrote file afiedt.buf

                          1  declare
                          2    l_iterations number := 0;
                          3  begin
                          4    for x in (select * from dba_tables)
                          5    loop
                          6      for y in (SELECT '' alias1,
                          7                       a.owner owner1,
                          8                       a.table_name table1,
                          9                       b.column_name col1,
                        10                       NULL alias2,
                        11                       c.owner owner2,
                        12                       c.table_name table2,
                        13                       d.column_name col2,
                        14                       b.position,
                        15                       NULL alias3,
                        16                       decode(c.delete_rule, 'CASCADE', 0,
                        17                                             'NO ACTION', 3) del_rule,
                        18                       c.constraint_name cons1,
                        19                       a.constraint_name cons2,
                        20                       '' alias4
                        21                  FROM all_constraints a,
                        22                       all_cons_columns b,
                        23                       all_constraints c,
                        24                       all_cons_columns d
                        25                 WHERE a.owner = b.owner
                        26                   AND a.constraint_name = b.constraint_name
                        27                   AND c.owner = d.owner
                        28                   AND c.constraint_name = d.constraint_name
                        29                   AND a.constraint_type = 'P'
                        30                   AND c.constraint_type = 'R'
                        31                   AND a.constraint_name = c.r_constraint_name
                        32                   AND a.owner = c.r_owner
                        33                   AND b.position = d.position
                        34                   AND c.table_name = x.table_name
                        35                   AND c.owner = x.owner
                        36                 ORDER BY 2, 3, 9)
                        37      loop
                        38        null;
                        39      end loop;
                        40      l_iterations := l_iterations + 1;
                        41    end loop;
                        42    dbms_output.put_line( 'iterations = ' || l_iterations );
                        43* end;
                        SCOTT @ nx102 Local> /
                        iterations = 1654

                        PL/SQL procedure successfully completed.

                        Elapsed: 00:00:07.20
                        SCOTT @ nx102 Local> ed
                        Wrote file afiedt.buf

                          1  declare
                          2    l_iterations number := 0;
                          3  begin
                          4    for x in (select * from dba_tables)
                          5    loop
                          6      for y in (SELECT /*+ RULE */ '' alias1,
                          7                       a.owner owner1,
                          8                       a.table_name table1,
                          9                       b.column_name col1,
                        10                       NULL alias2,
                        11                       c.owner owner2,
                        12                       c.table_name table2,
                        13                       d.column_name col2,
                        14                       b.position,
                        15                       NULL alias3,
                        16                       decode(c.delete_rule, 'CASCADE', 0,
                        17                                             'NO ACTION', 3) del_rule,
                        18                       c.constraint_name cons1,
                        19                       a.constraint_name cons2,
                        20                       '' alias4
                        21                  FROM all_constraints a,
                        22                       all_cons_columns b,
                        23                       all_constraints c,
                        24                       all_cons_columns d
                        25                 WHERE a.owner = b.owner
                        26                   AND a.constraint_name = b.constraint_name
                        27                   AND c.owner = d.owner
                        28                   AND c.constraint_name = d.constraint_name
                        29                   AND a.constraint_type = 'P'
                        30                   AND c.constraint_type = 'R'
                        31                   AND a.constraint_name = c.r_constraint_name
                        32                   AND a.owner = c.r_owner
                        33                   AND b.position = d.position
                        34                   AND c.table_name = x.table_name
                        35                   AND c.owner = x.owner
                        36                 ORDER BY 2, 3, 9)
                        37      loop
                        38        null;
                        39      end loop;
                        40      l_iterations := l_iterations + 1;
                        41    end loop;
                        42    dbms_output.put_line( 'iterations = ' || l_iterations );
                        43* end;
                        SCOTT @ nx102 Local> /
                        iterations = 1654

                        PL/SQL procedure successfully completed.

                        Elapsed: 00:00:28.76
                        Justin
                        • 24. Re: ODBC Driver very very slow catalog functions ( table scans)
                          568998
                          Seems that running the query in a loop is actually faster. I am not sure why, I was hoping that would be a shortcut to simplify things but does not seem to reproduce the issue. Ultimately driver sends one query for each SQLForeignKeys call.

                          Executing a modified version of your code 10 iterations takes about 3 seconds
                          26-MAY-07 10.45.22.687000 AM -04:00
                          26-MAY-07 10.45.25.812000 AM -04:00

                          but executing the 10 queries I listed in my previous post takes about 34 seconds
                          26-MAY-07 10.40.03.468000 AM -04:00
                          26-MAY-07 10.40.37.734000 AM -04:00

                          Can you execute the script I listed earlier that has 10 separate queries?

                          I am not sure why my app installer hangs on you. We can take that off line if you like, I would need to know what version of windows you are running. You can contact me directly at farid@zidsoft.com

                          Here is the modified code based on your PL/SQL code that I used for the loop test:
                          select systimestamp from dual;
                          declare
                            l_iterations number := 10;
                          begin
                               for x in (select * from dba_tables) loop
                                    for y in (SELECT '' alias1,
                                                         a.owner owner1,
                                                         a.table_name table1,
                                                         b.column_name col1,
                                                         NULL alias2,
                                                         c.owner owner2,
                                                         c.table_name table2,
                                                         d.column_name col2,
                                                         b.position,
                                                         NULL alias3,
                                                         decode(c.delete_rule, 'CASCADE', 0,
                                                                                    'NO ACTION', 3) del_rule,
                                                         c.constraint_name cons1,
                                                         a.constraint_name cons2,
                                                         '' alias4
                                                   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 = 'XTABLE_' || l_iterations
                                                    AND c.owner = 'OWNER_2'
                                                 ORDER BY 2, 3, 9) loop
                                      null;
                                    end loop;
                                    l_iterations := l_iterations - 1;
                                    exit when l_iterations <= 0;
                               end loop;
                            dbms_output.put_line( 'iterations = ' || l_iterations );
                          end;
                          /
                          select systimestamp from dual;
                          • 25. Re: ODBC Driver very very slow catalog functions ( table scans)
                            JustinCave
                            I don't see any appreciable difference executing your script vs my script for 10 iterations.

                            What happens if you do
                            ALTER SESSION SET cursor_sharing=FORCE
                            before running your script?

                            Justin
                            • 26. Re: ODBC Driver very very slow catalog functions ( table scans)
                              568998
                              Executing
                              ALTER SESSION SET cursor_sharing=FORCE

                              the script with 10 separate queries run in about 3 seconds as opposed to 34 seconds
                              27-MAY-07 08.45.07.250000 AM -04:00
                              27-MAY-07 08.45.10.562000 AM -04:00

                              I would think that's consistent with the 10 iterations script which appears to re-use the query 10 times. If that's the default cursor behavior on your system then that would explain the time differences.

                              Message was edited by:
                              Farid_Z
                              • 27. Re: ODBC Driver very very slow catalog functions ( table scans)
                                568998
                                I have updated my application to issue "ALTER SESSION SET cursor_sharing=FORCE" right after it connects to Oracle for driver 10.02.x. I realize the optimizer may not choose the optimal execution plan for some user queries with this setting, but it seems to be worth-wise solution ( I don't believe you can set cursor sharing option for a specific query without altering the session setting). Here is updated stats for executing the ODBC 10.02.00.03 driver catalog functions iterating over about 300 tables. The EXACT timings were obtained without altering the session cursor_sharing setting which default to EXACT:

                                Function ---- Oracle EXACT ---- Oracle FORCE --- MS SQL Server
                                SQLStatistics ---- 2:07 ---- 0:01 --- 0:02
                                SQLPrimaryKeys ---- 1:17 ---- 0:09 --- 0:02
                                SQLForeignKeys ---- 14:00 ---- 0:05 --- 0:02
                                SQLTablePrivileges ---- 2:06 ---- 0:03 --- 0:30

                                This definitely solves the performance issues with the catalog functions for my app. The improvements are sometimes over 100 times faster.

                                Actually about 3 additional seconds need to be added for each of FORCE results times, since by the time my app gets to the test at least one execution of the catalog function has occurred ( to get resultset description ).

                                I realize some of the catalog function queries are complex, but I am wondering if it is normal to take about 3 seconds for the dbms to determine the query execution plan ( SQLForeignKeys query for example ).

                                Message was edited by:
                                Farid_Z
                                • 28. Re: ODBC Driver very very slow catalog functions ( table scans)
                                  759978
                                  I've had the same problem with an Oracle 11g Release 11.2.0.1.0 database, where a query such as:
                                  SELECT fields
                                  FROM all_constraints ac,
                                  all_cons_columns loc,
                                  all_cons_columns rem
                                  WHERE ac.table_name = '&tab'
                                  AND ac.constraint_type = 'R'
                                  AND ac.owner = '&own'
                                  AND ac.owner = loc.owner
                                  AND ac.constraint_name = loc.constraint_name
                                  AND ac.r_owner = rem.owner
                                  AND ac.r_constraint_name = rem.constraint_name
                                  AND loc.position=rem.position
                                  ORDER BY constraint_name, loc_pos;

                                  Takes a very very long time, but adding the /*+ RULE */ hint makes it execute quite fast.

                                  When I ran:
                                  SQL> exec DBMS_STATS.DELETE_DICTIONARY_STATS();

                                  PL/SQL procedure successfully completed.

                                  SQL> exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS();

                                  PL/SQL procedure successfully completed.

                                  Then afterwards, the query was fast (without the /*+ RULE */ hint).

                                  Gathering the stats again:
                                  SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS();

                                  Slowed it back down.

                                  I can't imagine that best practice is to DELETE_DICTIONARY_STATS()? Can anyone explain what is going on and advise?
                                  • 29. Re: ODBC Driver very very slow catalog functions ( table scans)
                                    JustinCave
                                    You're almost certainly better off posting this as a new question over in the Database General forum rather than resurrecting a 6 year old thread in the ODBC forum. There are far more people over there that can help you than you'll find in the ODBC forum.

                                    Justin
                                    1 2 Previous Next