Re: V$active session history
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Tanel Poder <tanel@xxxxxxxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Mar 2015 21:20:13 +0100
I posted a snapper of a previours execution of the same query the same day
at 15:40. Haven't found a print of a snapper between 16h30 and 17h00
2015-03-23 21:16 GMT+01:00 Mohamed Houri <mohamed.houri@xxxxxxxxx>:
> 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> @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@xxxxxxxxx>:
>
>> 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@xxxxxxxxxxxxxx>:
>>
>>> 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@xxxxxxxxx>
>>> 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@xxxxxxxxxxxxxx> 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@xxxxxxxxx> 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>
>
>
--
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>
Other related posts:
