Performance problems with remote queries

[Terje J?sang]

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.

[Dave Steele]

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_enabl ed = false, and the
queries ran again in about 5 seconds.

Hope this helps,
David