Host and Database Performance Monitoring - Oracle Exadata Recipes: A Problem-Solution Approach - page 428

(sum(decode(event,null,0,dbtw)) over
(partition by to_char(xdate,'DD-MON-RR HH24:MI:SS'))) smpcnt,
(sum(decode(event,null,0,dbtw)) over
(partition by to_char(xdate,'DD-MON-RR HH24:MI:SS')))/60/&&interval_mins aas,
nvl(event,'*** IDLE *** ') tc,
decode(event,null,0,dbtw) dbtw,
decode(event,null,0,dbtw)/60/&&interval_mins aas_comp,tw
from (
select s1.xdate,event,count(*) dbtw,sql_id,count(ash.smpcnt) smpcnt,sum(time_waited) tw
from (
select ash.sample_id,
ash.sample_time,
ash.session_state,sql_id,
decode(ash.session_state,'ON CPU','CPU + CPU Wait',ash.event) event,
(count(ash.sample_id) over (partition by ash.sample_id)) smpcnt,
(sum(ash.time_waited/1000000) over (partition by sample_id)) time_waited
from v$active_session_history ash
where to_date(to_char(ash.sample_time,'DD-MON-RR HH24:MI:SS'),'DD-MON-RR
HH24:MI:SS')
between to_date('&_bt') and to_date('&_et')
and ash.event like 'cell%block%') ash,
(select xdate from xtimes ) s1
where ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_mins/1440)
group by s1.xdate,event,sql_id)
order by 1,dbtw desc)
where tc like 'cell%'
/
Begin Time SQL ID Event AshDBTime AAS TimeWaited
------------------ ------------- ------------------------------ ---------- ----- ----------
09-NOv-12 14:11:16 fgdz37rn4bf4b cell multiblock physical read 74.00 .12 140.90
0a3su0q0mxfqt cell multiblock physical read 36.00 .06 12.25
0a3su0q0mxfqt cell single block physical read 28.00 .05 11.97
fgdz37rn4bf4b cell single block physical read 6.00 .01 10.82
fgdz37rn4bf4b cell list of blocks physical read 6.00 .01 13.07
3626j8qpgf20s cell single block physical read 1.00 .00 .20
ajymgnp1qnruw cell list of blocks physical read 1.00 .00 .02
cbaqspgygz94n cell list of blocks physical read 1.00 .00 .37
09-NOv-12 15:21:16 bt4gw911p5vf0 cell multiblock physical read 39.00 .07 7.81
bt4gw911p5vf0 cell single block physical read 11.00 .02 1.43
... Ouptut omitted
In this output, notice the discrepancy between the ASH database time and the TIME_WAITED value from
v$ACTIvE_SESSION_HISTORY . This occurs because Oracle only populates the last sample after event completes.
For these reasons, it is not useful to perform any calculations with the TIME_WAITED column from ASH; we have
constructed the query in Listing 14-10 to include this column to demonstrate why it should not be trusted when
querying ASH data.