LAN, WAN, Bam
Duke Ganote, July 20, 2007 - 10:37 am UTC
Reminds me of a discussion between a guy responsible for corporate Enterprise Application Integration (EAI) and me some years back. I disparaging noted their EAI implementation only handled 2.5 messages-a-second going database-to-database on our LAN. Mr. EAI said it was Oracle could only handle that INSERT rate. I set up a test (the details will be fuzzy, it's been a few years now) where I would pull from one database (in the midwest), across a database link to a view on a remote database (in NJ, so it used the company WAN), then database linked that NJ view back to a view on 3rd database elsewhere in the midwest. Then I pulled messages (rows) from the 1st via the 2nd DB and INSERTed them via a quick-and-dirty PL/SQL proc on the 3rd database, committing after each row. It ran circles around the EAI implementation. As I recall, it handled dozens (if not more) messages per second. There really wasn't a lot Mr EAI could say after that.
Try FTP
Stew Ashton, July 20, 2007 - 12:37 pm UTC
Do a few tests with FTP, which is network "friendly" and will tend to use throughput to the max. This takes Oracle out of the equation. If the WAN is slower then, no one can blame the DB ! Be sure to test both ways : send and receive.
If FTP is slower, maybe the TCP buffers are too small on the receiving end. If FTP is about as fast, then it is more likely to be a latency problem and you need to reduce the number of round trips.
Thanks all for your answers
horvilleur, July 25, 2007 - 5:15 am UTC
I did some tests (unfortunately, I can't test FTP):
ping LAN : <1ms
ping WAN : 2 ms, latency doens't seem to be a huge problem.
set arraysize 15, WAN 2'16" and LAN 49"
set arraysize 5000, WAN 48" and LAN 29"
Definitely, arraysize seems to be the right track, i just have to find the same setting in our others 3rd party products (Business Objects for instance !)
Thanks again for your precious help.
Fabrice
Arraysize works well
Rajnish, July 18, 2012 - 5:30 pm UTC
My db job spools 30GB of data everyday to 10 flat files..
5 threads runs concurrently to do this job.
I am allowed to pull the data from a remove server, which doesn't have much stronger CPU.
after changing the arraysize, i see good gain of processing time ( 2 hrs dropped down to 1hrs and 15Mins ).