Get Oracle iops

oracle_iops.sql

select 'orcl', sample_hour, (rps+wps) IOPS
from ( 
with snaps as (
select hiof1.snap_id, sum(hiof1.value) reads, sum(hiof2.value) writes
from sys.WRH$_SYSSTAT HIOF1, sys.WRH$_SYSSTAT HIOF2
where HIOF1.stat_id in (select stat_id from v$statname where name like '%physical read total IO%')
  and HIOF2.stat_id in (select stat_id from v$statname where name like '%physical write total IO%')
  and HIOF1.snap_id=hiof2.snap_id
group by hiof1.snap_id
),
my_snaps as
(select snap_id, instance_number, begin_interval_time, end_interval_time,
 extract(second from (end_interval_time-begin_interval_time))+
 (extract(minute from (end_interval_time-begin_interval_time))*60)+
 (extract(hour from (end_interval_time-begin_interval_time))*60*60) seconds
 from dba_hist_snapshot)
select s1.snap_id snap_1, s2.snap_id snap_2, to_date(to_char(begin_interval_time,'MM/DD/YYYY HH24'),'MM/DD/YYYY HH24') sample_hour, sum(s2.reads-s1.reads) reads, sum(s2.writes-s1.writes) writes, 
  trunc(sum(s2.reads-s1.reads)/sum(seconds)) rps, trunc(sum(s2.writes-s1.writes)/sum(seconds)) wps
from snaps s1, snaps s2, my_snaps ms
where s1.snap_id=ms.snap_id
  and s1.snap_id=(s2.snap_id-1)
  and (s2.reads-s1.reads)>1
  and (s2.writes-s1.writes)>1
group by s2.snap_id, to_date(to_char(begin_interval_time,'MM/DD/YYYY HH24'),'MM/DD/YYYY HH24'), s1.snap_id 
) order by 3 desc;