Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Net more data from client description

Re: SQL*Net more data from client description

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 26 Jan 2007 11:52:30 -0800
Message-ID: <1169841150.559596.293980@s48g2000cws.googlegroups.com>


On Jan 26, 2:20 pm, "Steve Howard" <stevedhow..._at_gmail.com> wrote:
> 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_cha...
>
> ...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.
>
> <This wait event is likely close to meaningless on a LAN>
>
> 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

I misread "from" as "to" in the wait event name - it is essentially the same concept, just that the client sent a fragmented packet (or a case where the data payload is too large to fit into the MTU size of a single packet) and Oracle is waiting for the next portion of the fragmented packet so that it may be reassembled. Note that a mismatch in the ACK frequency between client and server can cause problems - a file that should be able to be copied in 3 seconds on a gigabit connection may require 45 minutes before the copy completes. This problem can be observed using a packet capture utility (Ethereal/Wireshark), where 0.2 second delays appear between the last packet sent and the corresponding ACK packet from the other computer. Search for Nagle and delayed ACKs for additional information.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Jan 26 2007 - 13:52:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US