Re: Database link. Select performance mystery

From: Alexander Zemerov <azemerov_at_yahoo.com>
Date: 25 Nov 2002 12:39:36 -0800
Message-ID: <3ba925c8.0211251239.9770d1a@posting.google.com>

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0211221211.6faaf5cb_at_posting.google.com>...
> azemerov_at_yahoo.com (Alexander Zemerov) wrote in message news:<3ba925c8.0211211225.6332adda_at_posting.google.com>...
> ...
> > for x in (select * from TABLE1_at_SRCDB where rownum<1001) loop

> > 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.

Output is correct. I used different rownum limiters becouse of different performance. If I set up 100,000 limit for TABLE1 from the DSTDB2 it will take a huge amount of time. Received on Mon Nov 25 2002 - 14:39:36 CST