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