After my last post about SQL*Net message to client wait event I had a follow-up question about what’s the difference between SQL*Net message to client and SQL*Net more data to client wait events. I’ll post the answer here:
The first session data unit (SDU) bufferful of return data is written to TCP socket buffer under “SQL*Net message to client” wait event.
If Oracle needs to write more data for a call than fits into the “first” SDU buffer, then further writes for that call are done under SQL*Net more data to client event.
So, whether and how much of the “SQL*Net more data to client” vs “SQL*Net message to client” waits you see depends on two things:
- Amount of data returned to client per call
- Oracle Net SDU size
A quick test with my Snapper performance tool illustrates this:
In one session run following:
SQL> set arraysize 1
SQL>
SQL> select rownum from dba_source;
ROWNUM
----------
1
2
3
4
5
6
...many rows returned...
And I measure the waits in the other session:
SQL> @snapper out,gather=w 5 1 141 -- Session Snapper v1.06 by Tanel Poder ( http://www.tanelpoder.com ) --------------------------------------------------------------------------------------------------------------------------------------------- HEAD, SID, SNAPSHOT START , SECONDS, TYPE, STATISTIC , DELTA, D/SEC, HDELTA, HD/SEC --------------------------------------------------------------------------------------------------------------------------------------------- DATA, 141, 20080210 14:59:55, 5, WAIT, SQL*Net message to client , 6750, 1350, 6.75ms, 1.35ms DATA, 141, 20080210 14:59:55, 5, WAIT, SQL*Net message from client , 4668258, 933652, 4.67s, 933.65ms -- End of snap 1
No SQL*Net more data to client is evidenced above as due the arraysize = 1 setting Oracle fetches only 2 rows at a time. Therefore the returned resultset per fetch call always fits into the first SDU sized packet. Note that the reason why Oracle fetches 2 rows when arraysize is 1 is due an OCI optimization which tries to be clever and proactively detect end-of-resultset conditions when fetching a single row – that way there’s a chance to automatically cancel a cursor and release its pins without an explicit cursor cancel or close call. This should have positive effect reducing shared pool fragmentation in some conditions.
Anyway, now I set arraysize to 5000 and run the same SQL again:
