Re: V$active session history

From: Dragutin Jastrebic <orahawk_at_gmail.com>
Date: Thu, 26 Mar 2015 07:31:43 +0100
Message-ID: <CANGCQwkQ97FyJkQ2JHBBnysBsQUqbE5753PutMh6v0CMeb2ckg_at_mail.gmail.com>

=>
V$ session is actually the ultimate source of session performance information (there's no such thing a "refreshing" v$session) as each session directly and immediately updates what they've up to right in the session state object (that's externalized in v$session). There are a few fields in the session state object that don't get externalized by the v$ and since 11g a special place called AshLoc where sessions continuously update on which execution plan line the SQL execution happens to be. <=

This behaviour of v$session can sometimes even be misleading to the DBA, in the case
of the short database polling queries (keepalive)

I remember the situation , (couple of years of ago and with Oracle 10g) , I was looking for a problematic query that the client module was executing.

I have started with v$session but every time I have queried it, I could not find anything but the idle sessions with "SQL*NET from client" as the waiting event.
But of course the look at v$sql.executions field value that was constantly progressing , confirmed that this short query was indeed executing. And v$active_session_history confirmed it as well.

Dragutin

2015-03-22 19:48 GMT+01:00 Tanel Poder <tanel_at_tanelpoder.com>:

> Indeed, not checking STATE column first seems to be the #1 cause why
> people end up troubleshooting the wrong thing. So easy to list current
> sessions using Toad, SQL Developer or even OEM session page and drill into
> whatever EVENT you see there... and then half an hour later find that oops,
> the session wasn't actually waiting for anything at all :-)
>
> And it has happened to me more than once even though I know that I should
> check the STATE first - my eyes just focus on that interesting cache
> buffers chains latch event section first and not some boring WAITING /
> WAITED part :-)
>
> That's why I've built this logic into my script (s.sql
> <http://blog.tanelpoder.com/files/scripts/s.sql> for example) and of
> course Snapper too. Is the STATE says anything but WAITING, it's actually
> on CPU. What confuses things a lot is that states like WAITED FOR
> KNOWN|SHORT TIME mean "ON CPU" despite that word WAIT.. in the beginning.
> Actually it's simple - WAIT*ED *is in past tense, meaning that the
> session waited for something in past, therefore is not waiting for anything
> right now, therefore is on CPU right now (or at least trying to be on CPU,
> but that's up to the OS to decide).
>
> V$ session is actually the ultimate source of session performance
> information (there's no such thing a "refreshing" v$session) as each
> session directly and immediately updates what they've up to right in the
> session state object (that's externalized in v$session). There are a few
> fields in the session state object that don't get externalized by the v$
> and since 11g a special place called AshLoc where sessions continuously
> update on which execution plan line the SQL execution happens to be.
>
> Tanel.
>
> On Fri, Mar 20, 2015 at 8:21 AM, Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>
>> You need to check the STATE column - the session is waiting if and only
>> if the state = 'WAITING'.
>> For other values of this column (and other relevant columns) see:
>> http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223
>>
>>
>>
>> Regards
>> Jonathan Lewis
>> http://jonathanlewis.wordpress.com
>> _at_jloracle
>> ------------------------------
>> *From:* Ramnivas Chaurasia [ramnivaschaurasia_at_gmail.com]
>> *Sent:* 20 March 2015 13:06
>> *To:* dombrooks_at_hotmail.com
>> *Cc:* Jonathan Lewis; ORACLE-L
>> *Subject:* Re: V$active session history
>>
>> I am comparatively new to this.
>>
>> But I have observed that event column in V$session does not refresh
>> frequently. If a session was waiting on some events in the past and is not
>> waiting currently on anything, it still shows the past wait in "event"
>> column. If you check the same in v$active_session_history view, you will
>> see that the event shown in v$session was much in the past. I find it a bit
>> misleading as I can not relay on v$session to check which sessions are
>> waiting on what event. Oracle 11.2.0.4
>>
>> Regards,
>> Ramniwas
>>
>>
>> On Fri, Mar 20, 2015 at 4:38 PM, Dominic Brooks <dombrooks_at_hotmail.com>
>> wrote:
>>
>>>
>>> V$ACTIVE_SESSION_HISTORY.IS_AWR_SAMPLE makes it very easy to see what
>>> will be flushed to DBA_HIST_A..S..H..
>>> It is every 10 samples. If nothing was active in that 10th sample, then
>>> nothing stored, the next sample for AWR will be in another 10
>>> seconds/samples time.
>>>
>>
>>
>

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

Received on Thu Mar 26 2015 - 07:31:43 CET