There isn't really enough information to give
sound advice - but one 'surprise' effect that
shows up in this kind of thing is the Nagle
algorithm, which is used to make network
traffic more efficient, but in the case of
Oracle can make things very slow. If your
second table has longer rows, so that a
tiny bit of each row needs to be postponed
to a second network package, then the second
package may be held briefly in the network queue
pending 'a little more data'.
You need to look at using tcp.nodelay = true
in your protocol.ora (or possibly sqlnet.ora, I
can never remember the exact name and location
for this parameter).
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Alexander Zemerov wrote in message
<3ba925c8.0211211225.6332adda_at_posting.google.com>...
>Hi,
>
>I found that select query performance through database link has very
>unpredictabale dependency on the source table, source and destination
>databases.
>
>I have three different schemas on three different instances -
>SRC_at_SRCDB, DST_at_DSTDB1 and DST_at_DSTDB2. SRC schema contains two table
>TABLE1 and TABLE2, tables have different structures. DST_at_DSTDB1 and
>DST_at_DB2 schemas have database links to the SRC_at_SRCDB named SRCDB.
>I execute the next script on these three schemas. In case of SRC
>schema I used simple table names without database link name becouse I
>select the local tables. Additionally I used different ROWNUM limits
>to get acceptable responce time in case of slow performance on the
>DSTDB2.
>
>declare
> st date;
> c integer;
> s integer;
>begin
> st := sysdate;
> c := 0;
> for x in (select * from TABLE1_at_SRCDB where rownum<1001) loop
> c := c+1;
> end loop;
> s := (sysdate - st)* 24*60*60; -- seconds
> dbms_output.put_line( 'TABLE1 '||c||' / '||s||'='||(c/s));
>
> st := sysdate;
> c := 0;
> for x in (select * from TABLE2_at_SRCDB where rownum<10001) loop
> c := c+1;
> end loop;
> s := (sysdate - st)* 24*60*60; -- seconds
> dbms_output.put_line( 'TABLE2 '||c||' / '||s||'='||(c/s));
>end;
>
>I tested this script several times and results are pretty stable -
>
> from from from
>table SRC (locally) DSTDB1 DSTDB2
> about the same about the same very different!!!!
>------- -------------- -------------- ------------
>TABLE1 100,000/5=20000 100,000/20=500 1,000/70= 14
>TABLE2 100,000/4=25000 100,000/12=833 10,000/5=2000
>
>My question is
>Why select performance for TABLE1 and TABLE2 from the DSTDB1 is about
>the same (500 ws 833), but from the DSTDB2 the results are so
>drammatically different ( 14 ws 2000)? I can not find any reasonable
>explanation.
>
>Thanks for any idea.
>Alexander.
Received on Sat Nov 23 2002 - 10:29:45 CST
