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

From: Anjo Kolk <anjo_at_oraperf.com>
Date: Tue, 20 Aug 2002 01:13:25 -0800
Message-ID: <F001.004B9A3E.20020820011325@fatcity.com>

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:

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.
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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Anjo Kolk
INET: anjo_at_oraperf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Aug 20 2002 - 04:13:25 CDT