Gerard H. Pille wrote:
> vic wrote:
>
>> I have 2 servers, server a is HP (DSS) and server b is Sun. Now one
>> of the
>> applications among these 2 servers is to query server b from server a.
>> server a has a user, which has a dblink to connect to server b. The
>> strange
>> thing is:
>> If I query server a from server b using sqlplus as:
>> sql> conn user/password_at_b from server a and run query, e.g.
>> sql> select ... from table1 , table2 where.... The elapsed time is 10
>> seconds but if I use a user in server b and use its dblink (svra) to run:
>> sql > select ... from table1_at_svra , table2_at_svra where.... the elased
>> time is
>> 4 minutes.
>> The listener.ora for server a has an entry as sdu=32768 and
>> tnsnames.ora in
>> server b (hp) has also sdu=32768 as its entry. I set tdu for both the
>> same
>> as sdu. It is still the same. Theoritically they should have the same
>> response time. Why using sqlplus is faster than using dblink? The
>> dblink
>> user is also the same user I connect from server a with sqlplus and
>> the same
>> connect string as the using clause in dblink. I couldn't figure out. I
>> think it is strange. Does anyone have the same strange experience as
>> mine?
>> Has anyone ever tackled this issue and willing to share it with me and
>> others?
>>
>> Thanks in advance.
>>
>>
>
> It's not strange: using the db-link, Oracle may need to fetch both
> tables completely, and will then join (where-clause) on server b. In
> the first case, the action happens on server a, only the results are
> transferred.
>
Take a look at the hint "DRIVING_SITE in the form:
SELECT /*+DRIVING_SITE(dept)*/ e.empno, e.empname
FROM emp e, dept_at_RSITE d
WHERE e.deptno = d.deptno;
It may solve the problem.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu Jan 01 2004 - 23:26:20 CST
