Some while ago, I needed to get some iops number out of our Oracle databases to help us right-size a network storage array. This script was instrumental in gathering the necessary data.
| 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; |