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
/

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

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

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.