SQL*NET MORE data to client等待事件：
意味着server process服务进程正在发送更多 数据/信息给client, wait time代表SEND发送这个操作实际完成的时间。
driver id
In Oracle8i onwards P1RAW can be decoded into ASCII characters to give a clue as to which Net driver is used.
Eg: P1RAW=0x62657100 = ‘beq\0’ , P1RAW=0x54435000 = ‘TCP\0’ etc.
In earlier releases the value here is the value of the disconnect function of the Net driver being used (which is not much use).
#bytes
The number of bytes expected to be sent by the server process to the client. Note that in some cases the bytes value may be misleading and may not reflect the actual number of bytes sent.
对该等待时间的一般建议是检查和调整网络或数据库连接
与相关的bug:
NB Bug Fixed Description 14249402 12.1.0.1 Poor RAT replay performance with LOBS – “SQL*Net more data to client” waits 8477973 11.2.0.2, 12.1.0.1 Multiple open DB links / ORA-2020 / distributed deadlock / ORA-600 possible using DB Links
Wait Time:
This wait blocks until the message is sent (or until an abnormal end of file condition occurs on the underlying Net transport layer). There is not usually an Oracle timeout on the wait, although the Oracle Net layer can be configured to time out if required.
Finding Blockers:
The blocker is generally due to any network delay.
Systemwide Waits:
This event normally denotes time outside of the server waiting to send more data to the client so is treated as a “Network” wait when looking at systemwide timings.
Reducing Waits / Wait times:
This is a normal wait but if the times are excessive one should look at the Net transport to the client process and determine if the time is due to:
Is there an excessive amount of data being sent from server to client
time in the network between the server and the client
(SQL*Net trace (with TIMESTAMPS) can be helpful to check out the time in the network between the client and server. See Note:16658.1 for details of Net tracing).
For the ‘SQL*Net more data to client’ event wait, Oracle uses SDU (Session Data Unit) to write to the SDU buffer which is written to the TCP socket buffer. If data is larger than the the initial size of Session Data Unit then multiple chunks of data need to be sent. If there is more data to send then after each batch sent the session will wait on the ‘SQL*Net more data to client’ wait event.
The following note describes SDU:
Document 44694.1 SQL*Net Packet Sizes (SDU & TDU Parameters)
How to Diagnose Waits for the ‘SQL*Net message to client’ and ‘SQL*Net more data to client’ events
The best way to diagnose the wait is by running 10046 trace. A process or a single sql can be traced using 10046 trace:
Document 376442.1 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
For example, in the following we are running a select from from SQL*Plus:
SELECT * FROM emp;
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.02 0 3 0 0
Fetch 2 0.00 0.06 0 0 0 14
——- —— ——– ———- ———- ———- ———- ———-
total 4 0.00 0.10 0 3 0 14
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 2 0.00 0.00
PX Deq: Execute Reply 13 0.01 0.01
SQL*Net message from client 2 26.06 26.06
PX Deq: Signal ACK RSG 8 0.01 0.01
PX Deq: Signal ACK EXT 8 0.02 0.02
PX Deq: Slave Session Stats 8 0.00 0.00
*****************************************************************************
Once the trace is obtained, you can TKProf it to see the timings and waits. Individual waits for ‘SQL*Net message to client’ are usually of very short duration (in this case the total wait is < 1 microsecond). The wait is recorded but since it has taken ‘zero’ time, the wait is not necessarily a performance issue or cause for concern.
If you notice unusually high waits for these events, for example as a top wait in statspack or AWR, then start the tuning process by tracing the process or the sql.
Potential Solutions
1. SDU size
Remember that ‘SQL*net message to client’ is normally not a network issue, as the throughput is based on the TCP packet. The first session is sent the contents of the SDU buffer which is written to TCP buffer then the session waits for the ‘SQL*net message to client’ event. The wait is associated with the following factors:
Oracle SDU size
Amount of data returned to the client
One solution is to increase the SDU size. The following document can help with that:
Document 44694.1 SQL*Net Packet Sizes (SDU & TDU Parameters)
2. Arraysize
If the application is using large amount of data, consider increasing the arraysize in the application. If small arraysize is used to fetch the data, then the query will use multiple fetch calls, each of these will wait for the ‘SQL*net message to client’ event. With a small arraysize and a large amount of data, the number of waits can become significant.
If running SQL from sqlplus, the arraysize can be increased using the sqlplus “set” command:
set arraysize 1000
From the raw 10046 tracefile, the fetch buffer size or the arraysize can be seen from the r (rows) of the fetch line:
FETCH #18446744071490060104:c=0,e=17086,p=2,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=872636971,tim=28473178755694
Here the r=1 is indicating arraysize of 1. 1 may be too low; so try increasing it if the wait for ‘SQL*net message to client’ events is large.
There is more information on arraysize and how to increase it in different applications in the following document:
Document 1419023.1 Row Prefetching and its impact on logical reads and fetch calls
3. TCP
Tune TCP connections and make sure the TCP is configured correctly. The following note may help:
Document 1037210.1 How to tune TCP parameters for better performance
Tuning TCP is outside the scope of Oracle Support. If you have difficulties please consult with your network team.
Leave a Reply