Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query with dblink runs slow

Re: Query with dblink runs slow

From: TurkBear <jgreco1_at_mn.rr.com>
Date: Fri, 26 Jul 2002 16:08:17 -0500
Message-ID: <9ge3kugdh5oadub3c7g7hdbed5g698g0qb@4ax.com>

Sorry NetComrade, Remote Database links do NOT use the indexes on the remote server.. Check the docs..
( at least thru 8.1.x)

andreyNSPAM_at_bookexchange.net (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.
>
>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

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Fri Jul 26 2002 - 16:08:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US