Re: V$active session history
Date: Thu, 26 Mar 2015 02:30:45 -0500
Message-ID: <CAMHX9JJ5Yj4dFFbghrd+=zviVTi985pu_NH1Bp_NVNquNAS36Q_at_mail.gmail.com>
So was this dabase completely idle otherwise? Troubleshooting with two different time ranges is a bit of a slippery slope. I have seen issues though with (recursive) SQL ASH records not showing up in SQL Monitoring reports, but don't remember seeing completely missing ASH records themselves. Or of course it could be a V$SESSION or Snapper problem too (but snapper wouldn't "invent" an IO wait event for a session unless the session actually was waiting for that event).
So next time this happens, I'd run full snapper on the session (to get v$sesstat counters like recursive calls/recursive CPU) and I'd also query V$SQLSTATS for that SQL_ID to see if its elapsed_time, buffer_gets etc counters also increase and save some raw V$SESSION samples too to see the data right at its source.
On Mon, Mar 23, 2015 at 3:16 PM, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:
> 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-lReceived on Thu Mar 26 2015 - 08:30:45 CET