Example 1 (Throughput/IOPs)
Use following query to see what were IOs per second over a period of time. For RAC, you can add column INSTANCE_NUMBER in WHERE clause to restrict result to a specific instance.
select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between
to_date('23-jun-17 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('23-jun-17 08:00:00','DD-mon_yy hh24:mi:ss') and METRIC_NAME='I/O Requests per Second' order by begin_time;
| BEGIN_TIME | END_TIME | MAXIMUM_IOPS | AVERATE_IOPS | NUM_INTERVAL |
|---|---|---|---|---|
| 23-JUN-17 00:09:21 | 23-JUN-17 00:19:21 | 136 | 82 | 10 |
| 23-JUN-17 00:19:21 | 23-JUN-17 00:29:21 | 87 | 65 | 10 |
| 23-JUN-17 00:29:21 | 23-JUN-17 00:39:21 | 105 | 71 | 10 |
| 23-JUN-17 00:39:21 | 23-JUN-17 00:49:21 | 144 | 77 | 10 |
| 23-JUN-17 00:49:21 | 23-JUN-17 00:59:22 | 254 | 127 | 10 |
Example 2 (Transfer Rate/MBPS)
Use following query to see what was “transfer rate per second” over a period of time. For RAC, you can add column INSTANCE_NUMBER in WHERE clause to restrict result to a specific instance.
select begin_time,end_time,round(maxval) maximum_iops,round(average) averate_iops,NUM_INTERVAL from DBA_HIST_SYSMETRIC_SUMMARY where begin_time between
to_date('23-jun-17 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('23-jun-17 08:00:00','DD-mon_yy hh24:mi:ss') and METRIC_NAME='I/O Megabytes per Second' order by begin_time;
| BEGIN_TIME | END_TIME | MAXIMUM_MBPS | AVERATE_MBPS | NUM_INTERVAL |
|---|---|---|---|---|
| 23-JUN-17 00:09:21 | 23-JUN-17 00:19:21 | 7 | 2 | 10 |
| 23-JUN-17 00:19:21 | 23-JUN-17 00:29:21 | 25 | 3 | 10 |
| 23-JUN-17 00:29:21 | 23-JUN-17 00:39:21 | 1 | 1 | 10 |
| 23-JUN-17 00:39:21 | 23-JUN-17 00:49:21 | 7 | 1 | 10 |
IO Profile Read+Write/Second Read/Second Write/Second
~~~~~~~~~~ ----------------- --------------- ---------------
Total Requests: 16,853.4 1,090.3 15,763.0
Database Requests: 15,247.2 150.3 15,096.9
Optimized Requests: 0.1 0.0 0.0
Redo Requests: 517.5 1.2 516.3
Total (MB): 1,154.3 643.6 510.7
12c
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 44.1 0.4 0.07 1.56
DB CPU(s): 1.6 0.0 0.00 0.06
Redo size (bytes): 154,034,644.3 1,544,561.0
Logical read (blocks): 154,436.1 1,548.6
Block changes: 82,491.9 827.2
Physical read (blocks): 150.6 1.5
Physical write (blocks): 18,135.2 181.9
Read IO requests: 150.3 1.5
Write IO requests: 15,096.9 151.4
Other Source:
https://gmakino.wordpress.com/2012/11/08/how-to-identify-iops-in-awr-reports/
Details calculaton using iostat
http://www.cmdln.org/2010/04/22/analyzing-io-performance-in-linux/
