Wait Event History

Wait class

col w for a29
select *
from (
select inst, w ,to_char(round(sum(cnt)/15,2),999.99) avrg ,to_char(round(sum(decode(mins_hist, 1,cnt,null)),2),999.99) t_1 ,to_char(round(sum(decode(mins_hist, 2,cnt,null)),2),999.99) t_2 ,to_char(round(sum(decode(mins_hist, 3,cnt,null)),2),999.99) t_3 ,to_char(round(sum(decode(mins_hist, 4,cnt,null)),2),999.99) t_4 ,to_char(round(sum(decode(mins_hist, 5,cnt,null)),2),999.99) t_5 ,to_char(round(sum(decode(mins_hist, 6,cnt,null)),2),999.99) t_6 ,to_char(round(sum(decode(mins_hist, 7,cnt,null)),2),999.99) t_7 ,to_char(round(sum(decode(mins_hist, 8,cnt,null)),2),999.99) t_8 ,to_char(round(sum(decode(mins_hist, 9,cnt,null)),2),999.99) t_9 ,to_char(round(sum(decode(mins_hist,10,cnt,null)),2),999.99) t_10 ,to_char(round(sum(decode(mins_hist,11,cnt,null)),2),999.99) t_11 ,to_char(round(sum(decode(mins_hist,12,cnt,null)),2),999.99) t_12 ,to_char(round(sum(decode(mins_hist,13,cnt,null)),2),999.99) t_13 ,to_char(round(sum(decode(mins_hist,14,cnt,null)),2),999.99) t_14 ,to_char(round(sum(decode(mins_hist,15,cnt,null)),2),999.99) t_15
from (
select inst, mins_hist, w, count(*) / 60 cnt
from
(select inst_id inst, sysdate sdt, trunc(( sysdate - to_date(to_char(sample_time,'YYYYMMDDHH24MI'),'YYYYMMDDHH24MI')) * 24 * 60) mins_hist,
--trunc((sysdate - sample_time) * 24  * 60) mins_hist,
decode(session_state, 'ON CPU', 'CPU', wait_class) w
from gv$active_session_history a
where sample_time >= sysdate - 0.25/24
--and sample_time <= to_date(to_char(sysdate,'YYYYMMDDHH24MI'),'YYYYMMDDHH24MI')
) a group by inst, mins_hist, w ) group by inst, w order by nvl(avrg,0) desc )
where avrg > 0 and rownum <= 25 order by inst, avrg desc;

--Wait event 

col w for a29
select *
from (
select inst, w ,to_char(round(sum(cnt)/15,2),999.99) avrg ,to_char(round(sum(decode(mins_hist, 1,cnt,null)),2),999.99) t_1 ,to_char(round(sum(decode(mins_hist, 2,cnt,null)),2),999.99) t_2 ,to_char(round(sum(decode(mins_hist, 3,cnt,null)),2),999.99) t_3 ,to_char(round(sum(decode(mins_hist, 4,cnt,null)),2),999.99) t_4 ,to_char(round(sum(decode(mins_hist, 5,cnt,null)),2),999.99) t_5 ,to_char(round(sum(decode(mins_hist, 6,cnt,null)),2),999.99) t_6 ,to_char(round(sum(decode(mins_hist, 7,cnt,null)),2),999.99) t_7 ,to_char(round(sum(decode(mins_hist, 8,cnt,null)),2),999.99) t_8 ,to_char(round(sum(decode(mins_hist, 9,cnt,null)),2),999.99) t_9 ,to_char(round(sum(decode(mins_hist,10,cnt,null)),2),999.99) t_10 ,to_char(round(sum(decode(mins_hist,11,cnt,null)),2),999.99) t_11 ,to_char(round(sum(decode(mins_hist,12,cnt,null)),2),999.99) t_12 ,to_char(round(sum(decode(mins_hist,13,cnt,null)),2),999.99) t_13 ,to_char(round(sum(decode(mins_hist,14,cnt,null)),2),999.99) t_14 ,to_char(round(sum(decode(mins_hist,15,cnt,null)),2),999.99) t_15
from (
select inst, mins_hist, w, count(*) / 60 cnt
from
(select inst_id inst, sysdate sdt, trunc(( sysdate - to_date(to_char(sample_time,'YYYYMMDDHH24MI'),'YYYYMMDDHH24MI')) * 24 * 60) mins_hist,
--trunc((sysdate - sample_time) * 24  * 60) mins_hist,
decode(session_state, 'ON CPU', 'CPU', event) w
from gv$active_session_history a
where sample_time >= sysdate - 0.25/24
--and sample_time <= to_date(to_char(sysdate,'YYYYMMDDHH24MI'),'YYYYMMDDHH24MI')
) a group by inst, mins_hist, w ) group by inst, w order by nvl(avrg,0) desc )
where avrg > 0 and rownum <= 25 order by inst, avrg desc;

--Order by sessions

col module for a29
select *
from (
select *
from (
select inst, module ,to_char(round(sum(cnt)/15,2),999.99) avrg ,to_char(round(sum(decode(mins_hist, 1,cnt,null)),2),999.99) t_1 ,to_char(round(sum(decode(mins_hist, 2,cnt,null)),2),999.99) t_2 ,to_char(round(sum(decode(mins_hist, 3,cnt,null)),2),999.99) t_3 ,to_char(round(sum(decode(mins_hist, 4,cnt,null)),2),999.99) t_4 ,to_char(round(sum(decode(mins_hist, 5,cnt,null)),2),999.99) t_5 ,to_char(round(sum(decode(mins_hist, 6,cnt,null)),2),999.99) t_6 ,to_char(round(sum(decode(mins_hist, 7,cnt,null)),2),999.99) t_7 ,to_char(round(sum(decode(mins_hist, 8,cnt,null)),2),999.99) t_8 ,to_char(round(sum(decode(mins_hist, 9,cnt,null)),2),999.99) t_9 ,to_char(round(sum(decode(mins_hist,10,cnt,null)),2),999.99) t_10 ,to_char(round(sum(decode(mins_hist,11,cnt,null)),2),999.99) t_11 ,to_char(round(sum(decode(mins_hist,12,cnt,null)),2),999.99) t_12 ,to_char(round(sum(decode(mins_hist,13,cnt,null)),2),999.99) t_13 ,to_char(round(sum(decode(mins_hist,14,cnt,null)),2),999.99) t_14 ,to_char(round(sum(decode(mins_hist,15,cnt,null)),2),999.99) t_15
from (
select inst, mins_hist, module, count(*) / 60 cnt
from
(select inst_id inst, sysdate sdt, trunc(( sysdate - to_date(to_char(sample_time,'YYYYMMDDHH24MI'),'YYYYMMDDHH24MI')) * 24 * 60) mins_hist,
--trunc((sysdate - sample_time) * 24  * 60) mins_hist,
nvl(module, program) as module
from gv$active_session_history a
where sample_time >= sysdate - 0.25/24
--and sample_time <= to_date(to_char(sysdate,'YYYYMMDDHH24MI'),'YYYYMMDDHH24MI')
) a group by inst, mins_hist, module ) group by inst, module order by nvl(avrg,0) desc )
where avrg > 0 and rownum <= 25 ) order by inst, avrg desc;