Time_Waited in ASH vs AWR

From: "sathish balasubramaniam" <sat0789@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Wed, 29 Oct 2008 15:26:33 +0400
Hello All,

We are on 10.2.0.3
Today during 7 to 9 am there was one job that was taking experiencing
significant log file sync wait. I wanted to find out the total time spend
waiting on this event.
This was the  only main job that was running at that time.

I tried to equate the timing that i get from v$session_event/AWR vs
v$active_session_history

I ran this query just a couple of minutes before the session (569) was
suppose to complete..

*select * from v$session_event where sid=569 and event='log file sync' *

Time_waited
Time_waited_micro
       569   log file sync 986399 59 153782 0.16 100 1537823859

As you can see, the time_waited is 153782 which is in centisec ..so that
equates to 1537 sec.
AWR report (awrrpt.sql) for that time period also matches close to 1537 sec.


Now for the same session id if i look into v$active_session_history
(wait_time is 0)

*select   sum(time_Waited)/1000000 "TIME IN SEC" from
v$active_session_history
where TO_CHAR (sample_time, 'DD-MON-YYYY HH24:MI:SS') >
'29-OCT-2008 07:00:19'
and  TO_CHAR (sample_time, 'DD-MON-YYYY HH24:MI:SS') <
'29-OCT-2008 09:00:01' and  event='log file sync'*

TIME IN SEC
237.898096
Not even close..

V$active_session_history has records going back since yesterday night.

Also if i run the query against *dba_hist_active_sess_history* for the same
time period, it turns out with
-- Dont know what the metric is for time_waited in
dba_hist_active_sess_history

select sum(wait_time) , sum(time_Waited) from dba_hist_active_sess_history
where TO_CHAR (sample_time, 'DD-MON-YYYY HH24:MI:SS') >
'29-OCT-2008 07:00:00'
and TO_CHAR (sample_time, 'DD-MON-YYYY HH24:MI:SS') <
'29-OCT-2008 09:00:00' and event='log file sync'

SUM(WAIT_TIME) SUM(TIME_WAITED)
0                          23531101
Any ideas as to what i am missing here ..I am assuming that v$sesstat gives
the right info in terms of wait time but why am i not getting the same or
approx results from either v$active_session_history or
dba_hist_active_sess_history..I understand that ASH writes only every sec so
technically it is not a good tool for session level analysis but still the
numbers seem way off..

Thanks,

Sat
Time_Waited in ASH vs AWR

Other related posts:
