HUGE query execution time difference between oracle 10g and 9i

Question

I'm running the following query:

SELECT * FROM all_tab_cols c
LEFT JOIN all_varrays v ON c.owner = v.owner
    AND c.table_name = v.parent_table_name
    AND c.column_name = v.parent_table_column

On a 10g server this takes ~2s, on 9i this takes 819s (13 mins)! What on earth is causing this huge performance difference, and how can I fix it?

Answer 1

One potential explanation for the disparity is data dictionary stats. In 10g Oracle introduced the DBMS_STATS.GATHER_DICTIONARY_STATS() procedure, which gathers stats against the SYS and SYSTEM schemas (and some others). Having statistics on the data dictionary could result in an improved execution plan for some queries against database views.

Even if you run DBMS_STATS.GATHER_DATABASE_STATS() it still gathers stats for the data dictionary, unless you explicitly set the gather_sys parameter to false.

You can check what statistics gather operations have been run against the 10g database with this query:

SQL> select * from  DBA_OPTSTAT_OPERATIONS
  2  order by start_time asc
  3  /

OPERATION                                                        TARGET
---------------------------------------------------------------- ----------------
START_TIME
---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
gather_database_stats(auto)
10-APR-10 06.00.03.953000 +01:00
10-APR-10 06.18.21.281000 +01:00

<snip/>

gather_database_stats(auto)
03-MAY-10 22.00.05.734000 +01:00
03-MAY-10 22.03.08.328000 +01:00

gather_dictionary_stats
06-MAY-10 13.48.49.839000 +01:00
06-MAY-10 13.57.42.252000 +01:00


10 rows selected.

SQL>

Answer 2

It turns out that, by default, 9i doesn't have statistics on the system tables, whereas 10g+ does. This is what's causing the performance difference - Oracle doesn't know how it should be joining it correctly.