Re: SQL*Net more data from client description

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 26 Jan 2007 11:20:46 -0800
Message-ID: <1169839246.768408.193660@a75g2000cwd.googlegroups.com>

On Jan 26, 1:41 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote: The description make sense to me.

> If the amount of data that Oracle must transmit to the client exceeds
> the MTU (maximum transmission unit) size for a single packet, the data
> will be broken up into multiple packets. Depending on the setting that
> determines the number of such packets that may be transmitted before a
> confirmation packet is received from the client (that allows delayed
> ACKs), Oracle may be able to send one to twenty (or some arbitrary
> number) packets before pausing for the confirmation packet. Once the
> confirmation packet is received, Oracle may send the next group of
> packets.

Thanks, Charles.

My understanding of this event has always been that the client has indicated it has more to send (large anonymous PL/SQL block, for example), and the underlying network transport (MTU, in sql*net land) is not large enough to send it, so it makes multiple passes to the shadow process on the database server. We don't have these large type of statements being sent.

I have heard Jonathan Lewis and Cary Milsap use similar verbiage (although not exact).

http://www.oreillynet.com/pub/a/network/excerpt/optimizing_oracle_chap12/index.html?CMP=ILC-FV7511446129&ATT=947

...and Jonathan Lewis...

<quote>sql*net more data from client is not an idle event because the client software is "trying" to send a large packet of data and had to break it into bits and (from its perspective) is waiting for the request from the server for the next installment</quote>

The background is we are going to an incredibly granular level of tuning an application prior to going live on a two node RAC. We have tuned the SQL (to a nauseating degree :-)).

We have in house written load stressing software that keeps track of application response times. The only event we can statistically correlate to spikes in application response time is "SQL*Net more data from client". The correlation is .7, and nothing else even comes close to that tight of a relationship (I/O, latches, etc.).

As a result, we are trying to iron out the small blips in performance differences tied to this.

We set tcpNoDelay on the application side which almost double averaged response time from eight centiseconds to four. Now, we are just trying to eliminate the somewhat unpredictable bursts (if it's even possible, we realize it may not be) in application response time when no other waits are correlated (no RAC events, I/O, fill in the blank, etc.).

As I noted, the "SQL*Net more data _from_ client" event just seems to have an odd description in the documentation. "SQL*Net more data _to_ client" makes sense, though.

Thanks again,

Steve Received on Fri Jan 26 2007 - 13:20:46 CST