Skip to Main Content
  • Questions
  • How much data per second can be delivered by my DB server ?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, horvilleur.

Asked: July 19, 2007 - 7:14 pm UTC

Last updated: July 20, 2007 - 8:56 am UTC

Version: 9.2.0.3

Viewed 1000+ times

You Asked

Hi Tom and many thanks for your site and books whom are such great places for an Oracle fan like me !

I'm currently figthing with our system team trying to proove that my Oracle DB server works fine whereas their WAN is a bootleneck.
If I run in sqlplus a simple statement like "select * from big_table", I can see that the traffic on the WAN is about 6% of a 100mb network client card.
The same query executed in LAN mode gave me 30%. (with set autotrace traceonly option in both case)
I'm sure there is a problem on the WAN line, but I've got to proove the system team that the DB server can not deliver much more than 30mb/s on a single connection.
1 - Is there a theorical maximum data transfert rate between the server and a single client ? (dedicated mode)
2 - How can I know if my current transfert rate is quite good or not (regarding the hardware capability) ? Is the Sql*Net more data to client value a good starting point ?
3 - Is there a magic setting (server side) to get more data from server in a single session ( a kind of "set arraysize 5000") without modifying the SDU, MTU and TDU parameters ?

Please forgive my poor english and thanks in advance for your help !

Fabrice

and Tom said...

only limited by your hardware infrastructure - we have no "throttle" that says "no faster than this".

Depends on the server CPU, the networking infrastructure.

SQLPlus will be affected by latency - you could have a high speed network with really long ping times (eg: a satellite connection fits that description). Even though the network can transfer tons of information really fast - the round trip times are so high - that overall throughput is very low.

ping times - they would be useful to compare.

set arraysize 15 - time sqlplus
set arraysize 5000 - time sqlplus

if latency is a huge problem, you would expect the 5000 to be measurably faster as there are less round trips (autotrace shows round trip counts)



there is no magic SERVER setting, the client sets the arraysize, the client must be willing to accept that much data.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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