Extemely slow query over database link
728815Oct 19 2009 — edited Oct 21 2009Hello,
Recently I have been having trouble with a query that executes over a database link from our local server to a server on the other side of our campus WAN.
Local server Setup
Oracle 10.2 - 64bit on RHEL
Remote Server Setup
Oracle 10.2 - 64 bit on Solaris
I can execute a query that returns 60,000 rows x 3 columns in just a few seconds over the dblink, so I know the dblink is working correctly.
The problem seems to lie with certain queries that I need to execute on the remote server which are somewhat complex in nature (involving mutiple subqueries).
I can login directly to the remote server and execute the complex query in less than 15 seconds, but trying to pull the data back over the dblink will cause a timeout or I will have to kill the session. At this point I am not joining tables between the remote database and the local database, I'm only pulling data from the remote database. Oddly enough, about 10% of the time I can pull all the data back through the database link without any issues (and very quickly, less than 30 seconds).
Any suggestions would be greatly appreciated.
Thanks.