Re: how to reduce SQL*Net more data to client wait event

[Anjo Kolk]

SQL*Net ships data (in TCP and BEQ) in packets of 2K (default TDU and SDU size, with a few exceptions on that for some platforms and protocol adapters). If you select to the client many large columns or multiple rows (array fetch), this 2K is filled up quickly and SQL*net will send the next 2K to the client until the full packet has been sent.   Now what can be done about this ? There are two basic solutions:   1) do we need that amount of data ? for example, programmers writing select * from <tab> and the * translates to many (large) columns. Solution here is to select     the columns that you need. Another problem could be that the array fetch size is set to large and that you are fetching too many rows (like 100 but you need only 10). Solution reduce the array fetch size.   2) If we need the amount of data we should make it cheaper to get the data. So a larger TDU and SDU  might help but could move the problem from problem from the SQL*Net layer to the underlying protocol (TCP/IP in this case and ethernet ?). TDU should be adopted to the underlying transport layer (fibre 4500 or so).  The most common problem is that before each send there is a delay (Naggle algorithme) and that can be fixed with tcp.nodealy = true on both the server (that should be enough in this case) and the client.   Now there are some issues in the SQL*net with SDU and TDU and tcp.nodelay depending on version. There were a lot of issues in 7.3 (like were to set SDU and TDU size and the location of the protocol.ini/ora file for setting tcp.nodelay =true). Also make sure that the client software uses the SQL*net layer that is 7.3 based and not 7.0/7.1 which is very common.   Anjo. ----- Original Message ----- From: Suhen Pather To: Multiple recipients of list ORACLE-L Sent: Tuesday, August 20, 2002 4:08 AM Subject: how to reduce SQL*Net more data to client wait event Hi,   I am tuning a system at a client site and notice lots of waits for SQL*Net more data to client (97%) for a fraction of the CPU consumed by the system.   I know this is not to be characterized as an idle wait event and can yield better performance if we increase the packet size. The database is Oracle 7.3.4 (SQL Net 2.3).  What effect will increasing TDU and SDU have on this wait to increase packet size.   It seems that if we can reduce this wait then we can save lots of time (I Think).   Will using BEQ protocol help at all.    Regards Suhen