2

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?

thecoop
  • 45,220
  • 19
  • 132
  • 189
  • 1
    Why does it matter? Why not just use Oracle 10/11g? – cletus May 06 '10 at 10:41
  • Because the app I'm developing needs to connect to 11g, 10g and 9i. – thecoop May 06 '10 at 11:02
  • First question: are the rowcounts from all_tab_cols and all_varrays the same between the two databases you are comparing? – dpbradley May 06 '10 at 11:07
  • same order of magnitude. 9i: 35046, 301. 10g: 50317, 383. So the 9i server should be doing much less work. – thecoop May 06 '10 at 11:13
  • 1
    Can you look at the query plan? – Janek Bogucki May 06 '10 at 11:31
  • Taking a stab here, since I'm not sure what the logical intent of your query is, but could it be that what you really want is (and I know the lack of comment formatting will mangle this): SELECT * FROM all_tab_cols c ,all_varrays v where c.owner = v.owner AND c.table_name = v.parent_table_name AND c.column_name = v.parent_table_column /*i.e. no outer join?*/ – dpbradley May 06 '10 at 12:11
  • I want the information on all table columns, but with extra information on varrays if the column does have a varray datatype (this is actually significantly simplified; there are further left joins as well, but this is the simple case that is just as slow) – thecoop May 06 '10 at 12:59
  • On my Oracle 9i database even "SELECT * FROM ALL_TAB_COLS" takes a long time (a minute). Does yours? – Tony Andrews May 06 '10 at 14:18
  • @Janek - I'm trying to run EXPLAIN PLAN, but it keeps on complaining I have insufficient privileges, even though I've granted SELECT ALL DICTIONARY to the user, and have a public synonym for sys.plan_table, with full public grants... – thecoop May 06 '10 at 14:18
  • @Tony: selecting from all_tab_cols only takes a couple of the seconds, its the join that kills it – thecoop May 06 '10 at 14:26
  • Right, I've got the query plan, but when I try to specify a query hint `USE_HASH(v)` I get an ORA-1760 illegal argument for function. Where on earth is this coming from? – thecoop May 06 '10 at 14:47
  • Adding the `USE_HASH(v)` hint here drops execution to between 2 and 4s, for 29785/3 rows on the views and returning 30112 results. I gave up on the non-hint version after 5 minutes. Not sure how a hint can cause that error, how are you formatting it? – Alex Poole May 06 '10 at 15:08
  • select /*+USE_HASH(v) */ * 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 – thecoop May 06 '10 at 16:52
  • So the normal way then. Sorry, I'm out, no idea how that could generate the error. – Alex Poole May 06 '10 at 18:14

2 Answers2

3

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>
APC
  • 144,005
  • 19
  • 170
  • 281
3

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.

thecoop
  • 45,220
  • 19
  • 132
  • 189