By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

Performance problems with remote queries

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.