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

John,

That event can happen for a couple of reasons, but the most common one is that the serverside has returned an error for the client side for an operation. The client side isn't really expecting it so the server sends a break/reset to the client. That gives the client a change to resync.

Anjo.

On Tuesday 20 August 2002 13:18, you wrote:
> Anjo,
>
> On a related topic: I always wanted to know what the 'SQL*Net break/reset
> to client' (or to dblink) means... There isn't much out there :(
>
> John Kanagaraj
>
> -----Original Message-----
> Sent: Tuesday, August 20, 2002 2:13 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> 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 -----
> To: Multiple <mailto:ORACLE-L_at_fatcity.com> recipients of list ORACLE-L
> Sent: Tuesday, August 20, 2002 4:08 AM
>
>
> Hi,
>
>
>
> I am tuning a system at a client site and notice lots of waits for SQL*Net
> <http://213.46.46.10/cgi-bin/yappweb206#SQL*Net more data to client#SQL*Net
> more data to client> 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).