Re: Query with dblink runs slow

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.

explain plan set statement_id='my'
for
<your query>
--the OTHER column shows what gets executed remotedly

set linesize 160
set pagesize 80
set feedback 100
--column explain format a40
column explain format a80
column other format a70
set long 600
set longchunksize 40
column other word_wrapped
select id||lpad(' ',2*level) || operation ||decode(id,0,'Cost='||position)
||' '||object_name as explain, other
from plan_table
where statement_id='&1'
connect by prior id=parent_id
start with id=0
;
delete from plan_table where statement_id='&1'; commit
/

On Thu, 25 Jul 2002 22:27:17 GMT, Daniel Morgan <dmorgan_at_exesolutions.com> wrote:

>Bill Wong wrote:
>
>> 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
>
>It is the nature of the beast.
>
>Daniel Morgan
>

.......
We use Oracle 8.1.7.3 on Solaris 2.7 boxes remove NSPAM to email Received on Fri Jul 26 2002 - 15:13:30 CDT