IOPS & MPS Workload for Oracle

最新推荐文章于 2021-04-13 10:51:18 发布

转载最新推荐文章于 2021-04-13 10:51:18 发布 · 117 阅读

使用下面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/