RE: SQLNET More data from client and 11g wait interface

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 20 Jan 2014 19:37:18 +0000

"SQL*Net more data from client" would specifically NOT be waiting for a fetch 
call from the client, that would just be "SQL*Net message from client".

A typical example of "more data from client" would be for an array insert where 
the insert statement and part of the array gets sent to the server and the 
server is aware that there is more data in the array and therefore sends an 
acknowledgement and request for the rest of the data to the client.  You can 
also see this on a single row insert with bind variables, of course, where the 
number of bind variables is large and the size is large, or a query with a long 
IN list of bind variables.

I suppose that, in theory, you might be able to write a simple SQL statement so 
long that it took a couple of SDU to send, and get "more data" that way.  But 
that's not an idea I've ever checked.

When I've seen a session hung on this in the past I've invariable had to assume 
that there's been a lost message somehow with the server waiting for the client 
and the client waiting for the server and no way to tell after the event what 
message went missing.


Regards
Jonathan Lewis




Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle

________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf 
of Dba DBA [oracledbaquestions@xxxxxxxxx]
Sent: 20 January 2014 19:12
To: oracle-l@xxxxxxxxxxxxx
Subject: SQLNET More data from client and 11g wait interface

Note title change since this is more about a specific wait and the 11g
wait interface.

Already saw Tanel's page. SDU is not the issue here. Its definitely
100 KB. All of the wait time is in 1 MAX_WAIT for this event. Total
session data transfer is 850 KBs total. I think this is more of a
networking/application server question than straight oracle. However,
I'm still a bit stumped.

Thanks for what you posted below, but I don't quite understand it.
So 'more data from client' can mean
1. Waiting for the application server to issue a 'fetch' to get more data
2. or what? It doesn't need more data. It should all end up in 1 pass.

My undertsanding is that with a TCP/IP connection your client does a
final 'ack' back to the server when it is done. If I remember
correctly from networking books that is the big difference between TCP
and UDP. TCP requires an 'ack' so data transfer is more reliable where
as UDP does not. So since this is a TCP connection, I 'think', Oracle
is waiting on 'more data from client' because it expects an 'ack' back
right?


I am thinking that when Oracle accumulates 'wait' time, there are
different triggers in the source code. When certain actions occur
Oracle accumulates time for that action. Most of the time when you
have application think time, Oracle is accumulating time under one of
the idle SQLNET wait events. So I am thinking that in the source code
oracle is waiting for a final 'ack' from the client before triggering
wait time accumulating under an idle wait.

anyone know? This is important it will help me provide more detailed
information to the client to help them track down the issue.


On 1/18/14, Radoulov, Dimitre <cichomitiko@xxxxxxxxx> wrote:
> Hi,
> it doesn't seem like connection leek to me and yes, there is a
> difference between:
>
> SQL*Net message from client
> The server process (foreground process) waits for a message from the
> client process to arrive.
>
> and:
>
> SQL*Net more data from client
> The server is waiting on the client to send more data to its client
> shadow process, in an _already initiated_ operation.
> Wait Time: The time waited depends on the time it took to receive the
> data (including the waiting time)
>
> Double check the actual bytes received reported with the wait event(you
> said the query should return only 100 bytes).
> And bugs are always possible, of course (Bug 11704187 : SESSION HUNG IN
> FUTEX CALL, just an example)
> so an SR could be appropriate as well.
>
> There are some hints about "more data _from_ client" event/tweaking the
> SDU size in the comments section of this post from Tanel:
> http://blog.tanelpoder.com/2008/02/10/sqlnet-message-to-client-vs-sqlnet-more-data-to-client/
>
>
> Regards
> Dimitre
>
>
> On 17/01/2014 22:46, Dba DBA wrote:
>> can this happen because developers are not closing their connections
>> to the connection pool? and its basically a connection leak at the
>> application server?
>>
>>
>> On Fri, Jan 17, 2014 at 4:42 PM, Dba DBA <oracledbaquestions@xxxxxxxxx
>> <mailto:oracledbaquestions@xxxxxxxxx>> wrote:
>>
>>     thank you. What does 'more data from client' mean? Most of the
>>     time the application is hung up we get an idle SQLNET wait. This
>>     is a query that returns less than 100 bytes, so it should fit in 1
>>     network pass.
>>
>>     Is oracle expecting an 'ack' saying i got it from the client?
>>
>>
>>     On Thu, Jan 16, 2014 at 4:11 PM, Austin Hackett
>>     <hacketta_57@xxxxxx <mailto:hacketta_57@xxxxxx>> wrote:
>>
>>         Hi
>>
>>         ASH has a "fix-up" mechanism. For long events the last sample
>>         gets it's TIME_WAITED value populated, and all the other
>>         samples stay at 0.
>>
>>         If you are seeing sum(time_waited) = 0 then my assumption
>>         would be that you have an on-going  long wait on SQL*Net more
>>         data from client.
>>
>>         I've previously seen "SQL*Net more data from client" when a
>>         client process has died mid-stream. If DCD (dead connection
>>         detection) is disabled, the individual wait can go on for
>>         days. I think the SEQ# column would allow you to validate this
>>         - if the SEQ# is always the same, it should be the same wait.
>>
>>         Hope that helps
>>
>>         Austin
>>
>>
>>
>>         --
>>         //www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>
>
--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: