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

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.