Re: Active Session History count(*) vs. sum(time_waited)

From: Austin Hackett <hacketta_57_at_me.com>
Date: Sat, 18 Jan 2014 10:36:41 +0000
Message-id: <E046BE20-EF86-40A6-A0F9-E9B9185E9C84_at_me.com>

In addition to what Dimitre said, I'd suggest passing on details of the session(s) machine, program, module, action etc. onto your application support people and have them do some checks at their end. The application could have problems unrelated to the DB (in my case it was some "stuck" threads in our bespoke OCCI middle tier app)

You should be able to give them a time period to zoom in on by looking at when it all started in DBA_HIST_ACTIVE_SESS_HISTORY. This view only has 1 in 10 samples, but since these waits are long running and span many samples (time_waited = 0), this should be more than sufficient.

On 18 Jan 2014, at 09:40, "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> 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> 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 Sat Jan 18 2014 - 11:36:41 CET