469,625 Members | 1,690 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Performance problems with remote queries

I am struggling with the performance of a SQL of the following
structure:

SELECT <cols>
FROM tab1@remote t1, tab2@remote t2
AND t1.col = t2.col
UNION ALL
SELECT <cols>
FROM localtab1 t1, localtab2 t2
AND t1.col = t2.col
UNION ALL
SELECT <cols>
FROM localtab1 t1, tab2@remote t2
AND t1.col = t2.col

If I run the three statements separately they use about 15, 1 and 6
seconds, but the whole thing takes 220 seconds.

The two databases I'm using are located are located in two different
countries, and the connection between them aren't among the fastest.

As I have understood the Oracle executions the queries will be
executed remotely if all tables in a query is located on the remote
database. I have also tried to use the DRIVING_SITE hint on each of
the query blocks, but it doesn't seem to help.

The version of the local database is 7.3.4 and the remote 8.1.7.
Jul 19 '05 #1
1 2255
We had something similar happening when testing our Applications that
run on 7.3.4 on a 8.1.7.4 Oracle database. Some queries that
connected to a remote database went from 5 seconds to about 15
minutes. The explain plan showed many hash joins in the queries. We
had to set the init.ora parameter hash_join_enabled = false, and the
queries ran again in about 5 seconds.

Hope this helps,
David
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jindrich Prchal | last post: by
8 posts views Thread by lyn.duong | last post: by
1 post views Thread by Terje J?sang | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.