ORACLE - IOPS and MBPS

There are two ways to measure I/O performance:  IOPS and MBPS.

1.IOPS : Small I/O operations and putting stress on the overhead associated with a single read or write. Throughput is not the main concern when you measure IOPS. This is most relevant on transactional systems.

2. MBPS : Large I/O operations and putting stress on the overall throughput. Latency is not the main concern when you measure MBPS. This is most relevant on warehouse and analytical systems.

spool IOPS.txt

set linesize 100
set head off

SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') FROM DUAL;

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
;

spool off