Re: Query with dblink runs slow

From: Makbo <makbo2_at_netscape.net>
Date: Sun, 18 Aug 2002 21:20:30 GMT
Message-ID: <3D600F9D.2010302@netscape.net>

Try using the "DRIVING_SITE" hint to the CBO. It can make a huge difference. This is documented somewhere in Oracle's chapters on distributed databases.

Select /*+DRIVING_SITE(remote_large_table) */ col1, col2
from

   remote_large_table,
   local_table
where ....

NetComrade wrote:

> Queries DO use indexes, but require a lot of tuning.
> Somtimes you have to do a lot of nested queries, but that doesn't
> always produce the needed results. In most cases we try the nested
> query to return what's needed from the remote db, and than join it to
> local tables. 
> Additionally, queries that execute entirely on the remote site, should
> have the same explain plan as they're on local.
> 

[...]
>>>I have a query that retrieves data from a secondary database (TEST2)
>>>via a dblink from the primary database (TEST1) at which I logged in.
>>>It took 2+ minutes to run.
>>>
>>>I copied the query, removing the dblinks and ran it in TEST2. It took
>>>only 9 seconds.
>>>
>>>Any suggestions?
>>>
>>Slow network connection
>>DB Link queries don't use indexes

[...] Received on Sun Aug 18 2002 - 16:20:30 CDT