Re: V$active session history

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Mon, 23 Mar 2015 21:16:53 +0100
Message-ID: <CAJu8R6ixtaae=tFB=+p7F1o4L+aa5OR5_F1BMfiKgcu5VsH5nw_at_mail.gmail.com>

Here are below what I have obseverd and what have motivated this current question:

The following select has been done on 18/03/2015 between 16h30 and 17h

SQL> select event, count(1)

        from gv$active_session_history
        where sample_time between to_date(‘18032015 16:30:00′, ‘ddmmyyyy
hh24:mi:ss’)
                                          and to_date(‘18032015 17:00:00′,
‘ddmmyyyy hh24:mi:ss’)
        group by event
       order by 2 desc;

 no rows selected

SQL> _at_snapper ash 5 1 all

—————————————————————————————————- Active% | INST | SQL_ID | SQL_CHILD | EVENT | WAIT_CLASS —————————————————————————————————- 100% | 1 | 3t5qhb3whnnr4 | 1 | db file sequential read | User I/O

— End of ASH snap 1, end=2015-03-18 15:40:44, seconds=5, samples_taken=44

 And the following selects have been done on 20/03/2015 at 12:32

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 20 12:31:56 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select event, count(1)

        from gv$active_session_history
        where sample_time between to_date(‘18032015 16:30:00′, ‘ddmmyyyy
hh24:mi:ss’)
                                         and to_date(‘18032015 17:00:00′,
‘ddmmyyyy hh24:mi:ss’)
        group by event
         order by 2 desc;

EVENT                                                COUNT(1)
—————————————————————- ———-
                                                          32
null event                                             1
os thread startup                                    1

3 rows selected.

SQL> select event, count(1)

        from dba_hist_active_sess_history
        where sample_time between to_date(‘18032015 16:30:00′, ‘ddmmyyyy
hh24:mi:ss’)
                                         and to_date(‘18032015 17:00:00′,
‘ddmmyyyy hh24:mi:ss’)
        group by event
       order by 2 desc;

EVENT                COUNT(1)
————————— ———-
                         3

1 row selected.

I don't know why the fist select against gv$active_session_history on 18/03/2015 was not showing db file sequential read or CPU

Best regards

Mohamed Houri

2015-03-22 20:42 GMT+01:00 Mohamed Houri <mohamed.houri_at_gmail.com>:

> Hi Tanel,
>
> I gathered things directly from sqlplus (copy/past) and I will post this
> here tomorrow evening as I have no access to gmail in this client site.
>
> Best regards
> Mohamed Houri
>
> 2015-03-22 19:53 GMT+01:00 Tanel Poder <tanel_at_tanelpoder.com>:
>
>> So you didn't have any other filters on ASH data except the *sample_time
>> between*? Or did you have any more filters like on session_id or sql_id
>> ? I'm asking for this as sometimes there are things like recursive sessions
>> with different session_ids in use or recursive SQL changes the sql_id that
>> shows up in ASH.
>>
>> Actually recursive SIDs should not show up in ASH as they are "caused" by
>> your user session - and since some 11.2.0.x version Oracle started ignoring
>> some recursive SQL_IDs too (and showed the user SQLs in ASH despite some
>> recursive activity at a deeper level). But there have been bugs around this
>> stuff ....
>>
>> On Fri, Mar 20, 2015 at 1:02 AM, Mohamed Houri <mohamed.houri_at_gmail.com>
>> wrote:
>>
>>> I queried ash just using
>>> Select event ,count
>>> Where sample_time between x and y group by event order by 2 desc
>>> Will check again and update you if i can reproduce the case
>>> Le 19 mars 2015 19:11, "Tanel Poder" <tanel_at_tanelpoder.com> a écrit :
>>>
>>> How did you query ASH ... which filters - by SQL_ID or by the SID of the
>>>> session (and a known time range)?
>>>>
>>>> --
>>>> Tanel.
>>>>
>>>> On Wed, Mar 18, 2015 at 11:57 AM, Mohamed Houri <
>>>> mohamed.houri_at_gmail.com> wrote:
>>>>
>>>>> Dears
>>>>> This morning i was monitoring an insert/select using sql monitor in
>>>>> 11.2.0.3. The insert was busy reading a unique index 3 millions times
>>>>> showing cpu and db file sequential read at this particular line. Tanel
>>>>> poder snapper shows that the correspinding sql_id id on cpu and db file
>>>>> sequential read. But gv$active_session_history shows no rows for the same
>>>>> moment
>>>>> Is it because each unique index scan takes less than 1 sec that it is
>>>>> not stored in gv$active_session_history?
>>>>> Thanks
>>>>>
>>>>
>>>>
>>
>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Member of Oraworld-team <http://www.oraworld-team.com/>
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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

Received on Mon Mar 23 2015 - 21:16:53 CET