Re: V$active session history
Date: Thu, 26 Mar 2015 02:08:19 -0500
Message-ID: <CAMHX9JKUp+yf39FsU3K6iZPhF8VfdNt1-iGzHt0GXnWedan6Cg_at_mail.gmail.com>
Yep, sampling, especially infrequent sampling has its limitations. But the problem is not really in V$SESSION itself (where ASH also gets its info from internally), but how people *use* it. ASH regularly samples the session state objects and even the infrequent short-running queries get eventually caught in a sample, so when aggregating ASH data over minutes and hours, the query will show up.
V$SESSION tends to (often incorrectly) get used by just glancing at it once and assuming that whatever showed up there at that particular point in time must be what this problem session is stuck with. This approach only works if you see some wait that actually has been stuck for minutes, hours (as shown in seconds_in_wait column), but is not enough in other cases like many frequently changing wait events. That's why snapper samples v$session ~50x per second when it's set to run for short periods of time.
V$SQLSTATS etc do provide another view on the same data (different angle, different detail) and of course SQL trace which is very useful for tracing all activity regardless of how fast it ran. When sampling individual session waits - another interesting view is V$SESSION_WAIT_HISTORY that stores a "trace" of last 10 wait events for every session.
Tanel.
On Thu, Mar 26, 2015 at 1:31 AM, Dragutin Jastrebic <orahawk_at_gmail.com> wrote:
> =>
> 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-lReceived on Thu Mar 26 2015 - 08:08:19 CET