Database link. Select performance mystery

From: Alexander Zemerov <azemerov_at_yahoo.com>
Date: 21 Nov 2002 12:25:44 -0800
Message-ID: <3ba925c8.0211211225.6332adda@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 Thu Nov 21 2002 - 14:25:44 CST