RE: Poor performance on bulk transfer across db link.

I'm assuming you're migrating data from the 9ir2 to 10gr2 environment. In the mid-nineties I migrated a 20 Gbyte (a lot way back then) database from oracle7 (old server) to oracle7 (new server). For reasons I've forgotten we did a fresh create database on the new servers and then wrote a whole bunch of sql-scripts to move the data from the old-server to the new-server using db-links too. In order to fit the time-window we were given, we were forced to parallelize this: the network was the bottle-neck in a single stream. So we divided the x tables across say 10 streams, in such a way that every stream had about the same amount of 'segment-size' to migrate. In this way we were able to migrate within the timewindow. Maybe you can parallelize your job too.
My experience with tweaking with network settings, is about the same as tweaking with init.ora parameters: unless you're hitting a serious bottleneck, it won''t give you much.  

Cheers,
Toon

-----Oorspronkelijk bericht-----

Van: David Aldridge [mailto:david_at_david-aldridge.com] Verzonden: woensdag 7 november 2007 20:01 Aan: Koppelaars, Toon
Onderwerp: RE: Poor performance on bulk transfer across db link.

An anonymous block calls a stored procedure which constructs the query dynamically (it has to exclude some columns) and then uses execute immediate on the resultant string literal.

"Koppelaars, Toon" <T.Koppelaars_at_centraal.boekhuis.nl> wrote:

David,  

How are you doing this?
In sqlplus, with insert into ... select ... from .._at_dblink? Or have you written a pl/sql routine for this?  

Toon

-----Oorspronkelijk bericht-----

Van: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]Namens David Aldridge Verzonden: woensdag 7 november 2007 18:53 Aan: oracle-l_at_freelists.org
Onderwerp: Poor performance on bulk transfer across db link.

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.

--
http://www.freelists.org/webpage/oracle-l