'sQL*Net message to client? and tuning network throughput

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

"SQL*Message from client" is a good indicator of throughput (e.g. by TCP/IP based database links), but 'sQL*Net Message? wait events cannot be used to measure network latency because of the architecture of the Transparent Network Substrate (TNS).

"SQL*Net message to client" wait time can be used as a low confidence indicator of throughput when data is transmitted over a slow link or a poorly or incorrectly configured TCP connection.  The throughput is directly related to "bytes sent via SQL*Net to client".  This is not, however, an accurate assessment or quantification of network latency.  v$sesstat or v$mystat can be queried to get the statistics on bytes and roundtrips handled through SQL*Net.

'sQL*Net message to client? cannot be used for network latency measurements based on the way TCP stack works and how Oracle uses it.  "SQL*Net message to client" measures only how much time it took for the response message to be placed into the TCP send buffer on the server.

Once the response message is in the TCP send buffer, the Oracle server process continues and then begins its wait for the next "SQL*Net message to client".  Delivery of the packet from this point in time is the responsibility of the TCP stack to deliver the packet.  The Oracle server has no mechanism for measuring when the message packet actually arrived; therefore, it is not possible to correlate this process to network latency measurements.

It is also important to note that there are differences between "SQL*Net Message to Client" and "SQL*Net more data to client" wait events.

Initially, the first session data unit (SDU) buffer containing the return data is sent and written to the TCP buffer as a "SQL*Net message to client" wait event.

Sometimes Oracle will need to write more data for a call than will fit into the first SDU buffer.  In these circumstances, any additional writes for that call are sent as a "SQL*Net more data to client" wait event.  The relationship of the frequency or number of "SQL*Net message to client" waits to "SQL*Net more data to client" waits is dependent on the following:

• Oracle Net session data unit size

• Per call data amounts returned to the client

For more, see my notes on monitoring network latency with Oracle.

Also see my related notes: