Re: Poor performance on bulk transfer across db link.

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Wed, 7 Nov 2007 22:15:44 +0100
Message-ID: <4ef2fbf50711071315n6fd8fd42ke3a5b57ba7834885@mail.gmail.com>

David, it seems that the FETCH size used by the dblink is sqlplus' arraysize, but for CTAS and inserts, it is not, it is much bigger (probably "infinite") at least in my test case:

9.2.0.4:
create user link_user identified by link_user temporary tablespace temp default tablespace users;
grant connect, resource, alter session to link_user; create table link_user.t as select rownum x from dual connect by level <= 10000; create trigger link_user.activate_trace
after logon on link_user.schema
begin
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
execute immediate 'alter session set events ''10051 trace name context forever, level 1''';
end;
/

10.2.0.3 (using 11.1.0.6 sqlplus):
create public database link target_db connect to link_user identified by link_user using 'oracle9i';

select * from t_at_target_db;
(default sqlplus arraysize of 15)
a series of
FETCH #1:c=10015,e=8517,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=4,tim=7386308205

set arraysize 147
select * from t_at_target_db;
a series of
FETCH #1:c=0,e=4611,p=0,cr=2,cu=0,mis=0,r=147,dep=0,og=4,tim=7815077002

set arraysize 156
create table local_t as select * from t_at_target_db; FETCH 1:c=290418,e=384630,p=0,cr=16,cu=0,mis=0,r=9999,dep=0,og=4,tim=7951647605

set arraysize 156
insert into local_t(x) select * from t_at_target_db;

FETCH #1:c=10014,e=18096,p=0,cr=2,cu=0,mis=0,r=666,dep=0,og=4,tim=8125609104
FETCH #1:c=50072,e=97190,p=0,cr=9,cu=0,mis=0,r=5333,dep=0,og=4,tim=8125756847
FETCH #1:c=60086,e=74140,p=0,cr=7,cu=0,mis=0,r=4000,dep=0,og=4,tim=8125899128

set arraysize 156
insert /*+append */ into local_t(x) select * from t_at_target_db; same as insert above
(note: I haven't showed the first FETCH with r=1 in all cases)

I have also increased the row length:
create table link_user.t as select lpad (rownum,1000,'x') x from dual connect by level <= 10000;
And the results have been the same (actually the inserts both showed r=9999).

For CTAS and inserts, the wait event has been always WAIT #1: nam='SQL*Net more data to client' ela= 59 p1=1413697536 p2=2022 p3=0 where p2 (number of bytes being sent) almost always around 2000 plus/minus 30, whatever the row length.

I'm myself network challenged; anyway 2000 is close to default SDU=2K, don't know whether there's any relation though.

Windows XP, sqlplus, source and target db on the same machine.

HTH
Alberto

On 11/7/07, David Aldridge <david_at_david-aldridge.com> wrote:
> I'm reading many hundreds of gigabytes from a 9iR2 database to a 10gR2
> database through a database link. Some of the tables I am reading are rather
> wide, with average column lengths of between 500 and 850 bytes.
>
> Performance appears to be constrained at the network level, with bandwidth
> in the order of 5Mbytes/sec on a gigabit network which demonstrates a
> 44MByte/sec ftp speed. There are no hops between the databases, with
> traceroute showing a direct server-to-server transfer.
>
> I've been googling around and came across
> http://www.fors.com/velpuri2/PERFORMANCE/SQLNET.pdf which
> explains the relationship between array size, row lengths, MTU, SDU etc..
>
> Statspack on the source db shows the following for a one hour snapshot:
>
> SQL*Net more data to client:
> 1,336,548 waits
> 0 timeouts
> 2,885 total wait time
> 2 Avg Wait (ms)
> 2,069.0 waits/txn
>
> So firstly, am I right in thinking that the default arraysize for database
> links is 15 rows?
>
> If so, given that the MTU is 1500, the SDU is the default 2Kb, and the
> average row length is 600, is that data transfer rate of 4MBytes/sec
> surprising? If the MTU and SDU were adjusted skywards to the 15*600 range
> (say 10kb) would I expect to get much of an improvement?
>
> Thanks in advance for any help -- I'm a network idiot.

-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 07 2007 - 15:15:44 CST