Re: Database link. Select performance mystery

azemerov_at_yahoo.com (Alexander Zemerov) wrote in message news:<3ba925c8.0211211225.6332adda_at_posting.google.com>... ...
> 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.

I think you have a user error in your output. How did you get 100,000 loops for TABLE1 run on SRC and DSTDB1? Your PL/SQL code shows rownum < 1001 regardless where you run it.

Yong Huang Received on Fri Nov 22 2002 - 14:11:05 CST