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

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 开发者_C百科this takes 819s (13 mins)! What on earth is causing this huge performance difference, and how can I fix it?

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.

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>