【等待事件】SQL*Net more data from client

Keyword：

Wait Event 、SQL*Net more data from client、SQL*Net 、等待事件、性能问题、网络、SDU

■SQL*Net more data from client的含义：

通过在线文档可以看到,等待事件SQL*Net more data from client表示

服务器进程等待客户端发送更多的数据或者消息。

参考：

Home / Database / Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration Database Reference https://docs.oracle.com/database/121/REFRN/GUID-5C743D2B-036E-4B0B-9417-52958DEFADDA.htm#REFRN00638 >C.3.152 SQL*Net more data to client >The server process is sending more data/messages to the client. The previous operation to the client was also a send. >Wait Time: The actual time it took for the send to complete

客户端发送数据传输的过程，大体而言如下：

1.服务器进程变成开始接收数据的状态

2.客户端开始发送数据

3.通过网络传输

4.服务器进程接收数据完成

由于Oracle Net通过SDU (Session Data Unit) 来控制着发送和收接数据的大小，如果传输数据时，数据大于用于传输的SDU大小的话（SDU默认大小为DEFAULT_SDU_SIZE：8K），就需要多次2～4的传输(more Data的含义所在)。

发生了多次传输，从1开始到4结束之间的等待就是SQL*Net more data from client。

一般导致多次传输，可能包括有2种情况：

1.确实由于客户端发送的信息过多（如SQL过大，Insert时传递的数据过多等），超过SDU大小，导致需要等待客户端的更多的信息

2.由于执行返回的数据多，导致服务器发送数据后，需要等待客户端的返回信息。（非官方）

关于第2种情况，其实也是我的猜测，并无更多官方资料记载，但可以通过以下外国工程师的Blog案例有一点儿了解。

参考：

https://blog.tanelpoder.com/2008/02/10/sqlnet-message-to-client-vs-sqlnet-more-data-to-client/ >I’ll reiterate that both SQL*Net message to client and SQL*Net more data to client waits only record the time it took to write the return data from Oracle’s userland SDU buffer to OS kernel-land TCP socket buffer. Thus the wait times of only microseconds. Thanks to that, all of the time a TCP packet spent “flying” towards the client is actually accounted in SQL*Net message from client wait statistic. The problem here is though, that we don’t know how much of this time was spent on the wire and how much of it was application think time. https://oracleattitude.wordpress.com/2014/08/22/oracle-performance-sqlnet-more-data-from-client/ Oracle Performance: SQL*Net more data from client >The shadow process has completed a database call and is returning data to the client process (for example SQL*Plus). The amount of data being sent requires more than one send to the client. The shadow process waits for the client to receive the last send. This happens, for example, in a SQL statement that returns a large amount of data. http://kerryosborne.oracle-guy.com/2009/04/hidden-sql-why-cant-i-find-my-sql-text/ >Hidden SQL – why can’t I find my SQL Text? >I was surprised to find that the statement was short – only a few lines long. I was expecting a really long string that would not fit in a single SQL*Net packet. After all, we are waiting on the “SQL*Net more data FROM client” event, right? Which is supposed to be the time waited for an additional packet from the client when Oracle knows that more is coming. Clearly this was not the case. ... >However, it appears that time is logged to that event only until the packet is turned over to the network. It appears that Oracle then starts logging time to the “SQL*Net more data FROM client” event while it waits for the next response from the client. Therefore, the actual network transfer time is clocked to the wrong event. You can understand why the developers made this choice, because Oracle has no way of knowing how long it actually takes the packet to get to the client. It only knows how long the round trip takes. So maybe they should have named the event slightly differently, like “SQL*Net more data from client including the entire round trip network time” or something.

■发生原因和解决

综上所述，发生等待事件:SQL*Net more data from client的原因如下：

1.网络有问题

2.应用程序（客户端）的问题，如过于频繁的SQL执行；传递给服务器端的数据过大等。

3.SDU大小过小

4.Oracle 的Bug

■一般解决策如下：

1.网络管理员或服务提供商检查网络状况

2.调整应用程序 （检查SQL中是否有LOB数据，是否有不适当的Loop）

3.SDU的大小

调整SDU大小的方法，可采用如下：

1.在sqlnet.ora中设置DEFAULT_SDU_SIZE

例：

DEFAULT_SDU_SIZE=16384

2.在listener.ora中设置SDU

例：

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SDU = 16384) ★ ....

3.在tnsnames.ora中设置SDU

例：

SERVER = (DESCRIPTION = (SDU = 16384) ....

4.设置dispatchers初始化参数（仅对共有服务器连接有效）

例：

dispatchers='(DESCRIPTION=(SDU=16384)(ADDRESS=(PROTOCOL=TCP) (HOST=SERVER)))(DISPATCHERS=1)'

注意：如果上面都设定的情况下，Oracle会选择最小的所谓SDU大小。

参考：

Home / Database / Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration Database Net Services Administrator's Guide https://docs.oracle.com/database/121/NETAG/performance.htm#NETAG1476 >Configuring Session Data Unit

■关于进一步的调查

如果想进一步调查的话，请考虑设置SQL Trace,NET Trace以及Hang信息等。