Re: Performance and Distrubuted Oracele 7

From: <hazledine_at_embl-heidelberg.de>
Date: 21 May 93 10:02:59 +0100
Message-ID: <1993May21.100259.86335_at_embl-heidelberg.de>


You might try reversing the order of table1 and table2 in the FROM clause. At least with Oracle V5, tables will be chosen to drive the query from right to left in the FROM clause (other things being equal), so you might find that putting table1 second will cause the query to be evaluated by a full table scan of table1 plus an indexed lookup into table2 for each table1 row thus retrieved.

The changes from V5 to V7 plus your networked environment might mean that the above no longer applies, but it might be worth a try!


David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                       6900 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE


> In article <C7C1C7.KL7_at_idm.com>, cdm_at_idm.com (Charles D. Marcotte) asks
>
> We are experiencing performace problems with Oracle 7 in a distributed database
> environment. Here's the picture:
>
> Local system Remote System
> -Oracle 7 -Oracle 6
> +Distributed opt. +Distributed opt.
> +SQL*Net +SQL*Net
>
> Table1 Table2
> -less than 10 rows ->600,000+ rows
> -foreign key to Table2 -primary key
> (name=t2ref) (name=primkey, indexed)
> -local DB login with synonym -user1 owns Table2
> to remote user2.Table2 user2 has Select rights to
> Table2 (i.e. read only)
>
> All queries to local and remote tables (through synonyms) work great, except
> for those that do a join of Table1 and Table2. When we try to do a join of
> these tables, the remote system experiences high disk and network I/O and
> the query never completes (we haven't waited for it to). A sample problem
> select statement is listed below:
>
> Select * from Table1, Table2
> where Table1.t2ref = Table2.primkey
>
> I don't consider myself an expert on Relational Databases, but I think the
> problem lies in Oracle 7's optimizer choosing to do a Sort/Merge join
> instead of a Nested Loop join. With a Sort/Merge join, the local system is
> trying to copy all of Table2 into the local system's TEMP database space.
> Once there, it will sort Table1 and Table2 and merge them for the join.
> This method is great if both tables are local, but in a distributed
> environment, the local system will have to move mega-bytes of data over the
> network: this is totally unacceptable! With a Nested Loop join, the local
> system would iterate over all rows in Table1 and do a lookup in Table2 for
> rows to join with. In the situation described above, this is obviously the
> preferred choice!
>
> I haven't looked at the execution plan for the query, but my first guess is
> that it is doing a Sort/Merge join. I will check out the execution plan
> today after I find the queries/views for looking at Explain Plan data.
>
> My questions about this problem are as follows:
>
> - Am I correct in the diagnosis of the problem?
>
> - Are there solutions?
> I have tried:
> -using oracle optimizer hints (USE_NL, etc...)
> -using in clauses with subqueries:
> "and Table2.primkey in (select t2ref from Table1)"
> -using open joins istead of closed joins
>
> - Is the problem because the remote system is Oracle 6?
>
> - Has anyone else experienced similar problems?
>
> - Am I missing something obvious?
> There is much to Oracle that I have yet to learn
>
> I am going to scan the Oracle 7 manuals today and may give Oracle a call this
> afternoon. Any help would be greatly appreciated. I will try to post
> follow ups and solutions if people ask for them. Thanks for listening.
>
>
> Chuck (cdm_at_idm.com)
Received on Fri May 21 1993 - 11:02:59 CEST

Original text of this message