select snap_id, end_interval,
round((mf_total/mt_total)*100,1)
as "FC Read Hit PCT",
TO_CHAR(mt_total,
'999,999,999,999') as "Total read IO",
TO_CHAR(mf_total,
'999,999,999,999') as "Read Cache IO",
TO_CHAR(mwio_total,'999,999,999,999') as " Total Write IO
Req",
TO_CHAR(wb_total,
'999,999,999,999') as "WBFC Total"
from
(with snap as
(select
snap_id, end_interval, stat_name, total - lag(total,1,0) over(partition by
stat_name order by snap_id) as total
from
(select
st.snap_id, TO_CHAR((cast(sp.end_interval_time as date)),'MON-DD-YYYY hh24 mi')
End_interval, st.stat_name, sum(st.value) Total
from
dba_hist_snapshot sp,dba_hist_sysstat st
where
sp.snap_id=st.snap_id and sp.instance_number=st.instance_number
and
st.stat_name in ('cell flash cache read hits'
,'physical read
total IO requests'
,'cell writes
to flash cache'
,'physical
write IO requests')
and cast(sp.end_interval_time as date) >
(sysdate -&num_hrs/24 - 1/96)
group by
st.snap_id, TO_CHAR((cast(sp.end_interval_time as date)),'MON-DD-YYYY hh24
mi'),st.stat_name
order by
st.snap_id)
)
select * from snap
where snap_id
> (select min(snap_id) from snap)
and total >
0
)
pivot (sum(total) as total for (stat_name) in ('physical
read total IO requests' as mt
,'cell flash cache read hits'
as mf
,'physical write IO
requests' as mwio
,'cell writes to flash cache'
as wb))
order by snap_id;
