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:16:53 +0100
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>
Other related posts:
