HUGE query execution time difference between oracle 10g and 9i
I am 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 it takes ~ 2s, on a 9i it takes 819s (13 minutes)! What is actually causing this huge performance difference, and how can I fix it?
a source to share
One possible explanation for the inconsistency is data dictionary statistics. In 10g Oracle introduced the DBMS_STATS.GATHER_DICTIONARY_STATS () Procedure , which collects statistics on SYS and SYSTEM (and some others). The presence of statistics in the data dictionary can lead to better execution plans for some queries on the database views.
Even if you run DBMS_STATS.GATHER_DATABASE_STATS (), it still collects statistics for the data dictionary unless you explicitly set the parameter gather_sys
to false
.
You can check what statistics collection operations were performed on 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>
a source to share