Sunday, June 22, 2014

Oracle IO Consumption - Throughput (IOPS) and Transfer Rate (MBPS)

Oracle Database IO Requirements (Throughput/IOPS and Transfer Rate/MBPS)

If we plan to migration an Oracle database to a new/different hardware, planning for storage is also a very important thing. It is good to find out the IO requirements of the database so that a better decision could be made while purchasing the storage. Storage speed is measured for Throughput (IOPS or IOs Per Second) and Transfer Rate(MBPS or Megabytes Per Second)
Within the Oracle database, 2 Metrics are available to have a look for "I/O Requests per Second"; which is for Throughput, and "I/O Megabytes per Second"; which is for the Transfer Rate.


In Oracle 10g and above, to measure the IO requirements; we can use DBA_HIST_SYSMETRIC_HISTORY (This view contains snapshots of V$SYSMETRIC_HISTORY) and DBA_HIST_SYSMETRIC_SUMMARY (This view contains snapshots of V$SYSMETRIC_SUMMARY) to analyze the IO requirement of current database by looking into the values of aforementioned metrics.

DBA_HIST_SYSMETRIC_HISTORY contains information in details, but querying DBA_HIST_SYSMETRIC_SUMMARY would suffice.

How DBA_HIST_SYSMETRIC_SUMMARY Works

Please note that using %HIST% views needs Oracle Diagnostic Pack license.


%HIST% views get information from AWR repository which is based on AWR snapshots. We can specify a time duration between which we want to view the IO related statistics. If your AWR snapshot interval is 10 minutes and you want to get the IO information between 23-jun-14 00:00:00 to 23-jun-14 08:00:00 (8 hours), then you should have roughly 48 rows returned from the view DBA_HIST_SYSMETRIC_SUMMARY- where you can see maximum and average IOs Per Second. The column NUM_INTERVAL will show total intervals in which this IO information was captured. For my case, it is 10 because AWR snapshot interval is set to 10 minutes.

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-14 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('23-jun-14 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-14 00:09:21 23-JUN-14 00:19:21 136 82 10
23-JUN-14 00:19:21 23-JUN-14 00:29:21 87 65 10
23-JUN-14 00:29:21 23-JUN-14 00:39:21 105 71 10
23-JUN-14 00:39:21 23-JUN-14 00:49:21 144 77 10
23-JUN-14 00:49:21 23-JUN-14 00:59:22 254 127 10
23-JUN-14 00:59:22 23-JUN-14 01:09:21 192 127 10
23-JUN-14 01:09:21 23-JUN-14 01:19:22 149 97 10
23-JUN-14 01:19:22 23-JUN-14 01:29:21 116 87 10
23-JUN-14 01:29:21 23-JUN-14 01:39:22 158 96 10
23-JUN-14 01:39:22 23-JUN-14 01:49:22 146 72 10
23-JUN-14 01:49:22 23-JUN-14 01:59:22 329 209 10
23-JUN-14 01:59:22 23-JUN-14 02:09:22 594 467 10
23-JUN-14 02:09:22 23-JUN-14 02:20:22 396 322 11
23-JUN-14 02:20:22 23-JUN-14 02:30:22 405 250 10
23-JUN-14 02:30:22 23-JUN-14 02:40:22 264 211 10
23-JUN-14 02:40:22 23-JUN-14 02:50:22 338 195 10
23-JUN-14 02:50:22 23-JUN-14 03:00:23 98 74 10
23-JUN-14 03:00:23 23-JUN-14 03:10:22 292 124 10
23-JUN-14 03:10:22 23-JUN-14 03:20:23 318 185 10
23-JUN-14 03:20:23 23-JUN-14 03:30:23 200 168 10
23-JUN-14 03:30:23 23-JUN-14 03:40:23 273 185 10
23-JUN-14 03:40:23 23-JUN-14 03:50:23 145 90 10
23-JUN-14 03:50:23 23-JUN-14 04:00:23 171 101 10
23-JUN-14 04:00:23 23-JUN-14 04:10:23 174 117 10
23-JUN-14 04:10:23 23-JUN-14 04:20:24 143 90 10
23-JUN-14 04:20:24 23-JUN-14 04:30:23 215 102 10
23-JUN-14 04:30:23 23-JUN-14 04:40:23 196 91 10
23-JUN-14 04:40:23 23-JUN-14 04:50:23 209 93 10
23-JUN-14 04:50:23 23-JUN-14 05:00:24 78 65 10
23-JUN-14 05:00:24 23-JUN-14 05:10:24 134 67 10
23-JUN-14 05:10:24 23-JUN-14 05:20:23 152 71 10
23-JUN-14 05:20:23 23-JUN-14 05:30:24 201 136 10
23-JUN-14 05:30:24 23-JUN-14 05:40:24 155 121 10
23-JUN-14 05:40:24 23-JUN-14 05:50:24 228 99 10
23-JUN-14 05:50:24 23-JUN-14 06:00:25 291 107 10
23-JUN-14 06:00:25 23-JUN-14 06:10:24 1035 333 10
23-JUN-14 06:10:24 23-JUN-14 06:20:24 565 256 10
23-JUN-14 06:20:24 23-JUN-14 06:30:24 455 321 10
23-JUN-14 06:30:24 23-JUN-14 06:40:24 367 301 10
23-JUN-14 06:40:24 23-JUN-14 06:50:25 372 317 10
23-JUN-14 06:50:25 23-JUN-14 07:00:25 287 188 10
23-JUN-14 07:00:25 23-JUN-14 07:10:25 200 136 10
23-JUN-14 07:10:25 23-JUN-14 07:20:25 126 93 10
23-JUN-14 07:20:25 23-JUN-14 07:30:25 144 115 10
23-JUN-14 07:30:25 23-JUN-14 07:40:25 250 145 10
23-JUN-14 07:40:25 23-JUN-14 07:50:25 219 127 10
23-JUN-14 07:50:25 23-JUN-14 08:00:25 149 120 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-14 00:00:00','DD-mon_yy hh24:mi:ss') and to_date('23-jun-14 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-14 00:09:21 23-JUN-14 00:19:21 7 2 10
23-JUN-14 00:19:21 23-JUN-14 00:29:21 25 3 10
23-JUN-14 00:29:21 23-JUN-14 00:39:21 1 1 10
23-JUN-14 00:39:21 23-JUN-14 00:49:21 7 1 10
23-JUN-14 00:49:21 23-JUN-14 00:59:22 31 4 10
23-JUN-14 00:59:22 23-JUN-14 01:09:21 1 1 10
23-JUN-14 01:09:21 23-JUN-14 01:19:22 7 1 10
23-JUN-14 01:19:22 23-JUN-14 01:29:21 25 3 10
23-JUN-14 01:29:21 23-JUN-14 01:39:22 7 1 10
23-JUN-14 01:39:22 23-JUN-14 01:49:22 1 1 10
23-JUN-14 01:49:22 23-JUN-14 01:59:22 26 8 10
23-JUN-14 01:59:22 23-JUN-14 02:09:22 71 62 10
23-JUN-14 02:09:22 23-JUN-14 02:20:22 72 65 11
23-JUN-14 02:20:22 23-JUN-14 02:30:22 84 66 10
23-JUN-14 02:30:22 23-JUN-14 02:40:22 68 58 10
23-JUN-14 02:40:22 23-JUN-14 02:50:22 62 58 10
23-JUN-14 02:50:22 23-JUN-14 03:00:23 88 63 10
23-JUN-14 03:00:23 23-JUN-14 03:10:22 77 59 10
23-JUN-14 03:10:22 23-JUN-14 03:20:23 62 58 10
23-JUN-14 03:20:23 23-JUN-14 03:30:23 95 58 10
23-JUN-14 03:30:23 23-JUN-14 03:40:23 70 61 10
23-JUN-14 03:40:23 23-JUN-14 03:50:23 70 60 10
23-JUN-14 03:50:23 23-JUN-14 04:00:23 73 59 10
23-JUN-14 04:00:23 23-JUN-14 04:10:23 66 61 10
23-JUN-14 04:10:23 23-JUN-14 04:20:24 58 45 10
23-JUN-14 04:20:24 23-JUN-14 04:30:23 69 38 10
23-JUN-14 04:30:23 23-JUN-14 04:40:23 46 40 10
23-JUN-14 04:40:23 23-JUN-14 04:50:23 25 22 10
23-JUN-14 04:50:23 23-JUN-14 05:00:24 25 4 10
23-JUN-14 05:00:24 23-JUN-14 05:10:24 1 1 10
23-JUN-14 05:10:24 23-JUN-14 05:20:23 9 1 10
23-JUN-14 05:20:23 23-JUN-14 05:30:24 26 6 10
23-JUN-14 05:30:24 23-JUN-14 05:40:24 8 3 10
23-JUN-14 05:40:24 23-JUN-14 05:50:24 1 1 10
23-JUN-14 05:50:24 23-JUN-14 06:00:25 24 3 10
23-JUN-14 06:00:25 23-JUN-14 06:10:24 9 5 10
23-JUN-14 06:10:24 23-JUN-14 06:20:24 11 4 10
23-JUN-14 06:20:24 23-JUN-14 06:30:24 35 11 10
23-JUN-14 06:30:24 23-JUN-14 06:40:24 11 6 10
23-JUN-14 06:40:24 23-JUN-14 06:50:25 11 5 10
23-JUN-14 06:50:25 23-JUN-14 07:00:25 26 6 10
23-JUN-14 07:00:25 23-JUN-14 07:10:25 7 2 10
23-JUN-14 07:10:25 23-JUN-14 07:20:25 1 1 10
23-JUN-14 07:20:25 23-JUN-14 07:30:25 25 4 10
23-JUN-14 07:30:25 23-JUN-14 07:40:25 8 3 10
23-JUN-14 07:40:25 23-JUN-14 07:50:25 8 2 10
23-JUN-14 07:50:25 23-JUN-14 08:00:25 25 4 10 

No comments:

Post a Comment