Performance problems with remote queries

From: Terje J?sang <terjejosang_at_hotmail.com>
Date: 21 May 2004 04:41:23 -0700
Message-ID: <a0cfd73f.0405210341.72b1379b_at_posting.google.com>

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

  SELECT <cols>
  FROM tab1_at_remote t1, tab2_at_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_at_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. Received on Fri May 21 2004 - 13:41:23 CEST