Re: V$active session history
Date: Sun, 22 Mar 2015 13:48:51 -0500
Message-ID: <CAMHX9J+Bw64gM-xm-RAspOQ5Hio+vQmkZNgZogfKY743TSLu1Q_at_mail.gmail.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-lReceived on Sun Mar 22 2015 - 19:48:51 CET