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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/611609/viewspace-692643/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/611609/viewspace-692643/