Oracle ASM DISK IO performance

Oracle OEM show a chart of disk performance, as shown in the chart below. when the OEM target database is 10gr2, the number of average disk response time (ms) is 0.01 ms, which unreasonable low. when the target database is 11gr2, the number is about 10ms, which I think is reasonable. it's time to find out where the discrepency comes from.

The backend query is this one:

set linesize 200
col name format a9
col path format a19
--col MB_per_sec format
select t3.name,t2.name,t2.path,t2.reads,t2.read_time,round(t2.read_time/t2.reads*1000,3) rd_rspd_ms,t2.writes,round(t2.write_time/t2.writes*1000,3) wr_rspd_ms,
round((t2.read_time+t2.write_time)/(t2.reads+t2.writes)*1000,3) dsk_rspd_ms,round((t2.bytes_read+t2.bytes_written)/1024/1024/(t2.read_time+t2.write_time))  MB_per_sec from V$asm_disk t2, v$asm_diskgroup t3
where t3.group_number=t2.group_number
order by 1,2
/

NAME      NAME      PATH                     READS  READ_TIME RD_RSPD_MS     WRITES WR_RSPD_MS DSK_RSPD_MS MB_PER_SEC
--------- --------- ------------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
DATA      DATA_0000 /dev/rhdisk4          25349646  202222.88      7.977    4222787      4.233       7.443          5
DATA      DATA_0001 /dev/rhdisk5          26815750  210079.47      7.834    4090426      4.696       7.419          5
DATA      DATA_0002 /dev/rhdisk6          24531304  202632.88       8.26    4032699      4.857        7.78          5
DATA      DATA_0003 /dev/rhdisk7          24317750  206242.12      8.481    3863635      4.732       7.967          5
DATA      DATA_0004 /dev/rhdisk1          26596353  168283.55      6.327    4081421     38.269      10.577          3
FRA       FRA_0000  /dev/rhdisk8           4779426   40681.94      8.512   17409392       .794       2.457         13
FRA       FRA_0001  /dev/rhdisk9           4815968   39794.32      8.263   17267055       .818       2.442         13

Do not compare this query result to the OEM chart because they are from different servers and dbs, i am just showing the idea here, you can play in your own environment.

When I run the query on 11.2.0.3, both ASM instance and RDBMS instance show similiar result.

Wehn I run the query on 10.2.0.4, the number of response time from ASM instance is 1000 time less than that on RDBMS, it maybe a bug. that explains why OEM report the unreasonable low number of "average response time" on 10gr2, because OEM queries the view of ASM instance rather than the RDBMS instance.

If the ASM instance hosts multiple RDBMS instance, then the numbers could be different, do not have to explain this to experienced DBA. :-)

If you want simulate io load to RDBMS, try this on 11gr2 RDBMS:

SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
 
  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/

Then query the view DBA_RSRC_IO_CALIBRATE to get the result.