Categories
Blog

Basic IOPS

I was asked about IOPS, so that a new server could be sized, this could help.

set linesize 100
 set head off
SELECT 'Number of Small Reads :'||
sum(decode(name,'physical read total IO requests',value,0)-
decode(name,'physical read total multi block requests',value,0)),
'Number of Small Writes:'||
sum(decode(name,'physical write total IO requests',value,0)-
decode(name,'physical write total multi block requests',value,0)),
'Number of Large Reads :'||
sum(decode(name,'physical read total multi block requests',value,0)),
'Number of Large Writes:'||
sum(decode(name,'physical write total multi block requests',value,0)),
'Total Bytes Read :'||
sum(decode(name,'physical read total bytes',value,0)),
'Total Bytes Written :'||
sum(decode(name,'physical write total bytes',value,0))
FROM gv$sysstat
/

Your results might look something like:

Number of Small Reads :106883
Number of Small Writes:205903
Number of Large Reads :2791
Number of Large Writes:40298
Total Bytes Read :2009381888
Total Bytes Written :4188587008

Remember that statistics contained in the GV$ views are cumulative and therefore should be sampled. You then subtract your first statistics from the last statistics to give you the throughput.

Number of Small Reads :333474
Number of Small Writes:500211
Number of Large Reads :8010
Number of Large Writes:142981
Total Bytes Read :5586081648
Total Bytes Written :22232604961

It is now easy to calculate the IOPS for small and large I/O and also MBPS. The following calculations will assist in determining the workload for the current database system.
Small Read IOPS =(333474-106883)/(10*60) = 377 IOPS
Small Write IOPS =(500211-205903)/(10*60) = 490 IOPS
Total Small IOPS =(226591+294308)/(10*60) = 868 IOPS
I/O Percentage of Reads to Writes = 44:56
Large Read IOPS =(8010-2791) /(10*60) = 8 IOPS
Large Write IOPS =(142981-40298)/(10*60) = 171 IOPS
Total Large IOPS =(5219+102683) /(10*60) = 179 IOPS
I/O Percentage of Reads to Writes = 4:96
Total MBPS Read =((5586081648-2009381888) /(10*60))/1048576= 5 MBPS
Total MBPS Written =((22232604961-4188587008)/(10*60))/1048576= 28 MBPS
Total MBPS =((3576699760+18044017953)/(10*60))/1048576= 34 MBPS
We could conclude from these figures that this is more of a OLTP as there is more writes occurring.