Thanx for the explanation!

What impact does the TCP.NODELAY option have on the sqlnet message to|from client response times?

Hi Tanel,

GREAT NOte!

The “SQL*Net message to client” is not the exact same event as “SQL*Net more data to client”, right?

Can you explain the distinction?

Thanks Tanel for interesting articles/coments.
Question on “SQL*Net message from Client” :

My database server is located in Data Center1. I have applications which run from Datacenter1(DC1) and DataCenter2(DC2) which access the DB in DC1. DC1 and DC2 are physically apart (over 100 miles).

A query from App1 (run from DC1) takes 5 minutes. The same query from App2 (run from DC2) takes 9 minutes. I ran extended trace and did TKprof on the 2 queries and found that the Parse/Execute/Fetch times are almost same. I found the difference of 4 minutes from the wait events. Both the queries had the same # of “SQL*Net message from client”, but the query from DC2 had 240 extra seconds on the Time waited.

Since DC1 and DC2 are physically apart, ping from DC2 to DC1 has a lag of 2.8 msec. This probably could explain the longer wait times(sql*net message from client) in case 2.

Now, the question is – How can I reduce the run times for the query run from DC2? Is it possible to tune this?
Does TDU, MTU, TCP.NODELAY or any of these setting help my situation ?

Appreciate your response
-Srav

Hi Srav,

Yep, good diagnosis. If the parse,exec,fetch times are the same, the extra time likely comes from something external to database like network. It looks like the reports are returning lots of data.. right?

Before looking into the Oracle parameters above I would first check two things:

1) Whether the application is using too low fetch buffer size (arraysize). This info you can get from sql trace file (the r= column in FETCH lines). If its too low (like 1, 10 or even 100) you should try to change the application settings to increase the arraysize.

2) Is there a network transport throughput bottleneck? This can be due overloaded link somewhere between two endpoints but often it’s just default TCP send window size settings as OS level which constrain the throughput when network roundtrip time is high (on solaris there are kernel tunables tcp_xmit_hiwat/tcp_recv_hiwat for example)

I would approach the step 2 the way that I would measure what throughput I’m getting first. SCP is not a good option (if its OpenSSH based) as it fiddles around with TCP socket buffer sizes at application level. There are tools for measuring the max throughput, but I would start from sqlplus and oracle.

1) Start sqlplus and set arraysize to 5000
2) run a script which does select * from dba_source (and doesnt display it on screen – set termout off in the script)
3) take snapshots from v$sesstat of that session fetching to measure how many bytes per second your throughput is (you can do it using my Snapper tool)

If the bytes/s is significantly lower than what you’d expect from your network link then there might be some TCP tuning needed.

Appreciate your advice.

I already did the arraysize analysis and as expected it did show improvements in the response time. Tremendously. But, as you know the developers are hesitant to make the code change at this time (guess, will have to force them to), but in the mean time I will try your 2nd recommendation.

So, do you do think SDU/TDU/MTU settings would help? I tried the tcp.nodelay but to noavail.

I love reading your articles/blog and find your responses very intelligent and efficient.

Thanks

Hi Srav, thanks :)

TCP.NODELAY wouldnt help as it’s already enabled (Nagle’s buffering algorithm disabled) by default in recent Oracle versions (including 9i).

Tuning SDU TDU settings would only help if current settings are lower from their optimal value (which is their max values in my opinion).

But if I recall correctly the default values in recent Oracle versions already are set to their max (2kB and 32kB).

Check this doc, it has some relevant test results:

http://www.sun.com/blueprints/1002/817-0370-10.pdf

More about TCP.NODELAY – this buffering algorithm was designed to reduce number of network packets sent when a lot of small messages needed to be sent (like someone typing in telnet, you could pack multiple “keystrokes” into a single TCP packet).

In case of large network transfers this should not affect you as your packets will be packed full (this is unless you have ridiculously small SDU/TDU sizes..)

Thanks Tanel.
The current settings of SDU/TDU were set at default(2k for 9i). I increased them to 32k, but still did not see any difference.

The dev team will be making the change to the arraysize in their OCI calls.

Another comment on TCP.nodelay. The setting of YES did not help my case either.

Thanks for your help

Glen Fawcett wrote a SUN Blue Print paper in 2003 (Sun Part # 817-1781-10) and he says the SDU and TDU must be sized according to the MTU for the interface. On SUN Solaris the default MTU size for the NIC is 1500 and the SDU and TDU should be set to integral values of the MTU setting for the NIC. For example for an MTU size of 1500, SDU and TDU can be set to 15000. This is recommend to reduce the number and ave time waited for SQLNet wait events. Glen also says it is best to set the TDU = SDU. I have tried this method and I see it decreased the number and ave time waited for the SQLNet waits by half.

Hi! I was surfing and found your blog post… nice! I love your blog. :) Cheers! Sandra. R.

Sign: dgcph Hello!!! mxzsb and 8912htudqtpfqb and 9065 : Hi! I was surfing and found your blog post! nice! I just came across your blog and wanted to say that Ive really enjoyed it.

In all lines, #bytes=1. So why bytes=1?

WAIT #7: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16418644515
WAIT #5: nam=’SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16428803213
W

Tanel,
sql*net message to client and from client are the two top wait events in one of my SQL trace file. In the TKPROF formatted trace file, I have the following:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 37776 0.00 0.11
SQL*Net message from client 37776 0.56 21.76

What’s the relationship between “Total Waited” and “Max Waited”? Is either the 0.56 seconds or 21.76 seconds (?) a measurement of one single execution?

Thanks,
Amos

Hi Tanel,
Thanks for your reply.
Do you know your 2010 US seminars schedule?
Happy New Year!
Amos

Hi Tanel,
on our production database Oracle Enterprise Edition 11.1.0.7 64 bit on Windows server 2003 due to the
bug 6319685 LGWR posts do not scale on some platforms, we enabled the fix for this bug
with setting undocumented parameter “_FG_SYNC_SLEEP_USECS”=1. Once we enable this parameter problem with the log file sync is solved but now we have the wait event SQL*Net message to client which is 20% of the total wait time.
How SQL*Net message to client wait event is connected with the parameter _FG_SYNC_SLEEP_USECS ? How can we tune this ?
Thanks
Marjan

It was false alarm. Wrong presentation on the Quest Performance Analysis. In fact setting this parameter help with removing the log file sync wait events completely and reduced the oracle cpu utilization by 40%. In the top 5 wait events redo waits were replaced with network waits :) so we tought that we are hitting some side effect of the change. After comparing the AWR reports before and after the change we realized that there is no change in the network waits.
Thanks and Best Regards
Marjan

Hi Tanel,
where can I get you snapper script ?