article better formatted at
Of the three waits, only "more data" is possibly related to network issues and that's not even clear, the other two are simply the time it takes to pack a message before sending it.
SQL*Net message to client - time to pack a message (no network time included) possibly tune SDUSQL*Net more data from client - possible network issues, possibly tune SDUSQL*Net more data to client - time to pack a message (no network time included) possibly tune SDU
SQL*Net message to dblinkSQL*Net more data from dblink - possible network issues, possibly tune SDUSQL*Net more data to dblink
Idle EventWaiting for work from ClientIncludes network transmission times for messages coming from shadowTypically indicative of Client “think time” or “processing time”
Doesn’t include network timingsee Tanel Poder's analysis of SQL*Net message to client
The only SQL*Net wait that can indicate a possible NETWORK problemClient is sending data to shadow that spans packets (think large data inserts, possibly large code blocks, large SQL statements)Shadow waits for next packet.Can indicate network latency.Can indicate a problem with the client toolHere is an example with ASHMON where the application server died mid-stream on inserts. The shadow processes were left waiting for completion of the message. You can see the regular load on the database on the left, then just past the middle the load crashes, and all that's left is waits on "SQL*Net more data from client"
Possibly set SDU=32768 as well as setting RECV_BUF_SIZE and SEND_BUF_SIZE to 65536.
CREATE TABLE T1 (C1 NUMBER);ALTER TABLE T1 ADD(CONSTRAINT T1_CHECK1 CHECK (C1 IN ('J','N')));ALTER SESSION SET EVENTS'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';INSERT INTO T1 VALUES (1);
09 ad='8e6a7c10'INSERT INTO T1 VALUES (1)END OF STMT=1 p3=0 obj#=-1 tim=5009300581549=0 p3=0 obj#=-1 tim=5009300581662
These waits are the same asSQL*Net message to dblinkSQL*Net more data from dblinkSQL*Net more data to dblinkSQL*Net break/reset to dblink
The default SDU can be set in the sqlnet. oraIf it's not set, the default is 2048The max is 32768The default,or the value in sqlnet.ora, can be overridden in the tnsnames. ora and the listener.ora. The client and server negotiate the size aggreeing on the smaller of the two settings.(TDU – Transmission Data Unit – see note 44694.1 The TDU parameter has been deprecated in the Oracle Net v8.0 and beyond and is ignored. It is only mentioned here for backward compatibility.)tnsnames.ora
V10G = (DESCRIPTION =(SDU=32768)(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = v10g)) )
SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SDU=32768)(SID_NAME = v10g)(ORACLE_HOME = /export/home/oracle10)))
trace_unique_client = true
client_3582.trc:[12-JAN-2008 11:37:39:237] nsconneg: vsn=313, gbl=0xa01, sdu=32768, tdu=32767
see: http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/performance.htm (or local copy)The recommended size for these buffers (from Oracle's docs) is at leastNetwork bandwidth * roundtrip = buffer min sizeFor example if the network bandwidth is 100mbs and the round trip time (from ping) is 5ms then
100,000,000 bits 1 byte 5 seconds
---------------- x ------ x --------- = 62,500 bytes
1 second 8 bits 1000tnsnames.ora
V10G = (DESCRIPTION =(SEND_BUF_SIZE=65536)(RECV_BUF_SIZE=65536)(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = v10g)) )
SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SEND_BUF_SIZE=65536)(RECV_BUF_SIZE=65536)(SID_NAME = v10g)(ORACLE_HOME = /export/home/oracle10)))