SQLNET More data from client and 11g wait interface

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Mon, 20 Jan 2014 14:12:08 -0500
Message-ID: <CAE-dsOK_ekZwQWrcSxY9iwtkcekvqnoEAf60AFJCQ2xOC7exTA_at_mail.gmail.com>



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_at_gmail.com> 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_at_gmail.com
>> <mailto:oracledbaquestions_at_gmail.com>> 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_at_me.com <mailto:hacketta_57_at_me.com>> 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
>>
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 20 2014 - 20:12:08 CET

Original text of this message