IOPS & MPS Workload for Oracle

使用下面SQL，在两个时间点上分别采集数据，之后按下述公式计算
可以得到数据库的IOPS和MBPS。

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)),sysdate
  FROM gv$sysstat

First collect (TA)              First collect (TB)
---------------------------     -----------------------------
Number of Small Reads : (A1)    Number of Small Reads  : (B1) 
Number of Small Writes: (A2)    Number of Small Writes : (B2) 
Number of Large Reads : (A3)    Number of Large Reads  : (B3) 
Number of Large Writes: (A4)    Number of Large Writes : (B4) 
Total Bytes Read    : (A5)    Total Bytes Read     : (B5) 
Total Bytes Written  : (A6)    Total Bytes Written   : (B6)

Small Read IOPS =(B1-A1)/(TB-TA) 
Small Write IOPS =(B2-A2)/(TB-TA) 
Total Small IOPS =(Small Read IOPS + Small Write IOPS)/(TB-TA)
I/O Percentage of Reads to Writes = Small Read IOPS : Small Write IOPS

Large Read IOPS =(B3-A3) /(TB-TA)
Large Write IOPS =(B4-A4)/(TB-TA)
Total Large IOPS =(Large Read IOPS + Large Write IOPS) / (TB-TA) = 179 IOPS
I/O Percentage of Reads to Writes = Large Read IOPS : Large Write IOPS

Total MBPS Read =((B5-A5) /(TB-TA))/(1024*1024)
Total MBPS Written =((B6-A6)/(TB-TA))/(1024*1024)
Total MBPS =((Total MBPS Read+Total MBPS Written)/(TB-TA))/(1024*1024)

data collect at 2010-10-27 15:35:03      data collect at 2010-10-27 15:45:03   
-------------------------------------    ------------------------------------- 
Number of Small Reads   :64164492        Number of Small Reads   :64245677     
Number of Small Writes  :76697185        Number of Small Writes  :76797034     
Number of Large Reads   :8480670         Number of Large Reads   :8481086      
Number of Large Writes  :135162266       Number of Large Writes  :135262659    
Total Bytes Read     :2262090980352   Total Bytes Read     :2263096722944
Total Bytes Written   :3110310104576   Total Bytes Written    :3112703476736 

Small Read IOPS =(64245677-64164492)/(10*60)  = 135 IOPS
Small Write IOPS =(76797034-76697185)/(10*60) = 166 IOPS
Total Small IOPS                = 301 IOPS
I/O Percentage of Reads to Writes             = 81.3%

Large Read IOPS =(8481086-8480670) /(10*60)     = 0.69 IOPS
Large Write IOPS =(135262659-135162266)/(10*60) = 167 IOPS
Total Large IOPS                 = 168  IOPS
I/O Percentage of Reads to Writes               = 1:240

Total MBPS Read =((2263096722944-2262090980352) /(10*60))/(1024*1024)   = 1.6 MBPS
Total MBPS Written =((3112703476736-3110310104576)/(10*60))/(1024*1024) = 3.8 MBPS
Total MBPS =((3576699760+18044017953)/(10*60))/1048576= 5.4 MBPS