Time_Waited in ASH vs AWR

From: sathish balasubramaniam <sat0789_at_gmail.com>
Date: Wed, 29 Oct 2008 15:26:33 +0400
Message-ID: <92bacc9f0810290426q78f10638xbf046f1b69a36b8d@mail.gmail.com>

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

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

Received on Wed Oct 29 2008 - 06:26:33 CDT