Disk IOPS concept and IOPS calculation and test

Disk IOPS concept and IOPS calculation and test

http://ce.sysu.edu.cn/hope/Item.aspx?id=12630

echo "The SYSTEM Metric of the" last $start days to $end days: "
Sqlplus-s "/As SYSDBA" <<!| Grep-v "Session Altered"
Set Lines 180
Set pages 70
Alter session set nls_date_format= ' Mm-dd hh24:mi:ss ';
Col phyread_total_mbps heading "phyr| MBps format 999.99
Col phywrite_total_mbps heading "phyw| MBps format 999.99
Col redo_mbps heading "redo| MBps Format 99.99
Col phyread_iops heading "phyr| IOPS ' Format 9999999.99
Col phywrite_iops heading "phyw| IOPS ' Format 9999999.99
Col phywrite_redo_iops heading "redo| IOPS ' Format 9999999.99
Col os_load heading "Load" format 99.99
Col db_cpu_usage_per_sec heading "DB cpu|cs/s" format 999.99
Col host_cpu_util heading "cpu|%" format 99.99
Col network_mbps heading "network| Mbps format 999.99
Col hp_ps heading "hp|ps" format 99.99
Col logons_ps heading "log|ps" format 99.99
Col enqueue_waits_ps heading "enq|ps" format 99.99
Select Max (end_time) time,
Trunc (sum (case metric_name when ' physical Read total Bytes the Sec ' then average end)/1024/1024,2) phyread_total_mbps,
Trunc (sum (case metric_name when ' physical Write total Bytes the Sec ' then average end)/1024/1024,2) phywrite_total_mbps,
Trunc (sum (case metric_name when ' Redo generated the Sec ' then average end)/1024/1024,2) redo_mbps,
Trunc (sum (case metric_name when ' physical Read total IO Requests per Sec ' then average), 2) Phyread_iops,
Trunc (sum (case metric_name when ' physical Write total IO Requests The Sec ' then Average end), 2) Phywrite_iops,
Trunc (sum (case metric_name when ' Redo writes the Sec ' then Average end), 2) Phywrite_redo_iops,
Trunc (sum (case metric_name when ' Hard Parse Count of Sec ' then Average end), 2) Hp_ps,
Trunc (sum (case metric_name when ' OS Load ' then Average end), 2) Os_load,
Trunc (sum (case metric_name when ' logons to Sec ' then average end), 2) Logons_ps,
Trunc (sum (case metric_name when ' CPU Usage/Sec ' then Average end), 2) Db_cpu_usage_per_sec,
Trunc (sum (case metric_name when ' Host CPU utilization (%) ' then average end), 2) Host_cpu_util,
Trunc (sum (case metric_name when ' network traffic Volume per Sec ' then average)/1024/1024,2) network_mbps,
Trunc (sum (case metric_name when ' Enqueue Waits per Sec ' then average), 2) Enqueue_waits_ps
From Dba_hist_sysmetric_summary
where end_time>=sysdate-$start
and end_time<=sysdate-$end
GROUP BY snap_id
Order BY snap_id;

Http://www.itpub.net/thread-1224372-1-1.html

Oracle IOPS and Mbps

http://blog.itpub.net/9240380/viewspace-758994/