The “SQL*Net message to client” Oracle metric indicates the server
(foreground process) is sending a message to the client, and it can be used to
identify throughput issues over a network, especially distributed databases with
slow database links.
The SQL*Net more data to client event happens when Oracle writes
multiple data buffers (sized per
SDU) in a single
logical network call.
Network bottlenecks are very common in distributed systems
and databases with high network traffic:
Top 5 Wait Events
% Total
Event Waits Time (cs) Wt Time
--------------------------------- ------------ ------------ -------
SQL*Net more data to client 3,914,935 9,475,372 99.76
db file sequential read 1,367,659 6,129 .06
db file parallel write 7,582 5,001 .05
rdbms ipc reply 26 4,612 .05
db file scattered read 16,886 2,446 .03
Respected Oracle guru
Tanel Poder has a great note that explains why “SQL*Message from client” is
a good indicator of throughput (e.g. by TCP/IP based database links), but that
“SQL*Net Message” wait events cannot be used to measure network latency because of the
architecture of the Transparent Network Substrate (TNS):
“So, if you’re sending loads of
data over a slow link or mis-configured TCP connection, the “SQL*Net message to
client” wait time can be used as a low-confidence indicator of your SQL*Net
throughput (in conjunction with “bytes sent via SQL*Net to client”), but never a
measure of network latency!”
[You can] query v$sesstat or v$mystat to get the actual
bytes & roundtrips sent through SQL*Net.
Most important, Tanel notes that you can’t use “SQL*Net message to client”
for network latency measurements at all, due how TCP stack works and how Oracle
uses it.
“SQL*Net message to client” does
NOT measure network latency! It merely measures how long it took to put the
response message into TCP send buffer on the server!
Once the response packet is put
into TCP send buffer, Oracle server process continues on and starts waiting for
“SQL*Net message FROM client” again. It’s up to TCP stack to deliver this packet
from this point and Oracle server process has no way for measuring directly when
did the packet arrive (it would have to start intercepting TCP ACK packets at
kernel level for that).”
Tanel also notes the differences between the "SQL*Net
Message to Client" and "SQL*Net more data to client", and
What’s the difference between SQL*Net message to
client and SQL*Net more data to client wait
events.
The first session data unit (SDU) buffer, full 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
For more, see my notes on
monitoring network latency with Oracle. Also see my related notes:
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
