SQL*Net more data to client event wait

SQL*Net more data to client event wait

In Oracle We generally got the wait “SQL*Net more data to client” in our performance reports.

Wait event:
SQL*Net data to client
SQL*Net more data to client

This wait event has generally misconception that the SQLNET wait events are like the other database wait events and would require tuning. The events are simply to track the time spent moving data and to track the time not being used on the server. In most cases this time will be high if the application remains connected to the server. This wait event does in fact include any idle wait time by the client. In short any time spent by the processes where it is not performing any server side actions. So any time spent by the application or user is clocked under this timer.

Note:
If you still want to try the fixed issue, you can enable trace on application and check it for more detail.

Fixed SQLNET more data wait event
You have one parameter in SQLNET.ora file: For 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. You can increase the SDU size. The following document can help with that:
Document 44694.1 SQL*Net Packet Sizes (SDU & TDU Parameters)
https://docs.oracle.com/database/121/NETAG/performance.htm#NETAG0141

Setting the SDU size at Database Server Side
Note:
1. SDU size can range from 512 bytes to 2 MB.
2. A high SDU size value requires more memory.
3. Default SDU size for the client and a dedicated server is 8192 bytes.
4. Default SDU size for a shared server is 65535 bytes.


-- In SQLNET.ora file of Server side
DEFAULT_SDU_SIZE=32767

-- You can set at initialization parameter if using shared server process
DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))"

Setting the SDU size at Client Side

--SQLNET.ORA file at client side
DEFAULT_SDU_SIZE=32767

--TNSNAMES.ORA file entry
sales=
(DESCRIPTION=
(SDU=11280)
(ADDRESS=(PROTOCOL=tcp)(HOST=RAC1.Server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales))
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.