Re: Database link. Select performance mystery

On 21 Nov 2002 12:25:44 -0800, azemerov_at_yahoo.com (Alexander Zemerov) wrote:

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

Obviously you need first to exclude it's a network problem.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Nov 21 2002 - 14:40:22 CST