DB Link performance degradation

[Chen Yingjie]

We have 1 HP9000 (N4000) server located in New York. This server is connected to another server (AIX server Hitachi EP8000) which i moved to Tokyo from New York.

The 2 servers is using FTP and DB Link to transfer data. (Both database is Oracle)

During the data trasferring testing, we found the performance of FTP is OK but the DB Link is degraded before AIX server relocation.

While transfer same size data by DB Link, before the relocation, it spend 2 hours. But now it spend 2 days.

We confirmed the network width is no problem.
We tried to extend the data packet size, but it does not improve the DB Link performance.

Can someone advise me how to solve this problem?

[Bill Hassell]

The problem is that DB Link is not designed for a WAN (long distance) link. Check the ping time between your locval servers (which will be in the 1-3 millisecond range). Your Tokyo link will run dozens to hundreds of milliseconds. A data transger cannot take place at wire speed (even if you can afford a T3 link). For a simple file copy, the data is broken up into small pieces, then each piece is acknowledged. On a local network, the overhead is the time from the end of a transmitted packet to the beginning of another packet. During this overhead, the local end requests an acknowledgement and remote end sends a reply.

On a local link, this overhead is a few milliseconds, essentially the same as ping time. But on a WAN, the line turnaround time (going from transmit to receive) plus the physical distance all add up to dozens to hundreds of milliseconds. So the more times the line must be turned around, the slower the throughput.

So all simple file copy techniques (NFS, rcp, scp, DB Link, etc) will have massively large overhead times (as you have seen already). So why is ftp fairly quick? It is a rather complex, adaptive transfer protocol that changes packet size and acknowledgement reqests to match the link (from a simple modem to a WAN to a LAN). ftp will minimize acknowledgement overhead by overlapping data transmission and acknowledgement sequences and acknowledging multiple packets at one time.

So unless the transfer protocol is written specifically for a WAN, the transfers will almost always be slower, sometimes massively slower.

[Yogeeraj_1]

hi,

quite difficult to compare the performance of FTP with that of your applications (db_link).

Below a list of delay estimates that should be considered:
â ¢ Client: Delayed ACKs, CPU processing and user activity

â ¢ Server: Delayed ACKs and CPU processing

â ¢ Network: bandwidth, latency, distance, congestion, packet loss, senderâ s window and
resends

â ¢ Application: Chatter, frame size and block structure

For more scientific analysis, you may consider one software that i recently came across: NETPredictor


You may also look into other solutions at the database level. If you are using Oracle 9i or 10g, ORACLE STREAMS may help you achieve the same...

hope his helps too!

kind regards
yogeeraj