Oracle Disk I/O Throughput Speed   Oracle Tips by Burleson Consulting

In a nutshell, an Oracle database is a mechanism to store and retrieve large volumes of data, and many end-users visualize Oracle as a big electronic filing cabinet.

This filing cabinet must be fast and people have come to expect sub-second response time.  These instant response time expectations are driven by tools like Google that can scan a billion web sites for keywords in a fraction of a second. 

But how do you make your Oracle system as fast as Google?  Speed is all about delivering the goods, and serving-up the data.

Also see rows per second Oracle throughput Tips.

Even while 64-bit technology and the accompanying large data buffers has reduced disk I/O bottlenecks, many database remain I/O-bound.  You can easily know if your database has significant disk I/O if your AWR of STATSPACK reports shows top-5 wait events that contain "db file sequential reads" (single I/O fetches) or "db_file_scattered_reads" (db_file_multiblock_read_count I/O fetches).

The standard measurement for disk is megabytes per second (MBps) and gigabytes per second (GBPS) and you can make your I/O faster by moving to a faster disk, using SSD, or caching the data inside large data buffers.

Let's explore the relative speeds of disk storage.  A PC laptop transfers data at about 15 MBps and the fastest disk for a PC is solid-state disk at 400 MBps.  In the larger server arena, we see SSD twice as fast as platter disk, with platter-based disk speeds ranging from 200-800 MBPS.

Large Server disk I/O speeds:

This is a list of disk I/O speeds in descending order, and, SSD leads the way for tertiary storage devices:

• 50 MBps - IEEE1394 is the standard designation for a very fast external bus standard that supports data transfer rates of up to 400 Mbps (in 1394a) and 800 Mbps over copper (in 1394b). IEEE1394 a, in its peer-to-peer bus topology, allows 100 to 400 Mbps data transfer rates for up to 63 nodes, allowing 16 hops at 4.5  meters per hop.
   

• 100 MBps - IEEE1394 b allows for higher speeds and longer distances. IEEE1394 b is completely compatible with IEEE1394 a providing up to 800 Mbps to 3200 Mbps data transfer over 4.5 meter copper and 100 Mbps over 100 meter copper. If used with Category 5 UTP wiring you can get 400 Mbps over 100 meter plastic optical fiber and 3200 Mbps over 100 meters of glass optical fiber.
   

• 266 MBps - The Apple Xserve RAID’s list price for 5.6 TB is $12,999, or $2.32 per GB. Its performance is excellent. For a workload of 8KB random reads, it can sustain 1100 I/Os per second (IOPS). For a multi-user sequential workload, it can sustain 266 MBps of read throughput.
   

• 1,600 MBps - SSD leads the pack in data transfer rates and speed (500,000 I/O's per second)
   

PC Oracle disk speeds:

• 400-500 MBps - See Texas Memory Systems SSD devices.
   

• 50-70 MBps - PC laptop SSD - Samsung says the SSD's outperforms a comparably sized HDD by more than 150 per cent. The company quotes the storage disk as reading data at 57 Mbytes per second (MBps) and writes at 32MBps.

• 20-30 MBps - These reports from a Windows XP Professional, SP2 laptop.  "The hard disk in a cheap laptop is unlikely to be producing nearly double the number of IOs per second of even a previous generation U160 SCSI drive :-"

Maximum Large MBPS=18.27

Maximum Large MBPS=25.65

In sum, most disk deliver data in the range of 200-800 MBPS and SSD can often halve the disk I/O latency.

 Disk I/O speed references:

 select
    to_char(sn.end_interval_time,'yyyymmddhh24') the_date,
    sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)) reads,
    sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value)) writes,
    (sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-oldreads.value),newreads.value)))+
    (sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-oldwrites.value),newwrites.value)))
total
from
    dba_hist_sysstat  oldreads,
    dba_hist_sysstat  newreads,
    dba_hist_sysstat  oldwrites,
    dba_hist_sysstat  newwrites,
    dba_hist_snapshot sn,
    dba_hist_snapshot sn2,
    dba_hist_snapshot sn3
where
see code depot for full script
group by
       to_char(sn.end_interval_time,'yyyymmddhh24')
order by
       to_char(sn.end_interval_time,'yyyymmddhh24')
;

Here is another PL/SQL script so calculate the I/O per second:

rem FUNCTION: PL/SQL to calculate IO/sec data
rem Mike Ault
rem

set serveroutput on
declare
cursor get_io is select
nvl(sum(a.phyrds+a.phywrts),0) sum_io1,to_number(null) sum_io2
from sys.gv_$filestat a
union
select
to_number(null) sum_io1, nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from
sys.gv_$tempstat b;
now date;
elapsed_seconds number;
sum_io1 number;
sum_io2 number;
sum_io12 number;
sum_io22 number;
tot_io number;
tot_io_per_sec number;
fixed_io_per_sec number;
temp_io_per_sec number;
begin
see code depot for full script
open get_io;
for i in 1..2 loop
fetch get_io into sum_io1, sum_io2;
if i = 1 then sum_io12:=sum_io1;
else
sum_io22:=sum_io2;
end if;
end loop;
select sum_io12+sum_io22 into tot_io from dual;
select sysdate into now from dual;
select ceil((now-max(startup_time))*(60*60*24)) into elapsed_seconds from gv$instance;
fixed_io_per_sec:=sum_io12/elapsed_seconds;
temp_io_per_sec:=sum_io22/elapsed_seconds;
tot_io_per_sec:=tot_io/elapsed_seconds;
dbms_output.put_line('Elapsed Sec :'||to_char(elapsed_seconds, '9,999,999.99'));
dbms_output.put_line('Fixed IO/SEC:'||to_char(fixed_io_per_sec,'9,999,999.99'));
dbms_output.put_line('Temp IO/SEC :'||to_char(temp_io_per_sec, '9,999,999.99'));
dbms_output.put_line('Total IO/SEC:'||to_char(tot_io_Per_Sec, '9,999,999.99'));
end;
/

The following sample output from io_sec.sql displays the total elapsed seconds since the database was started, the I/O per second for both temporary I/O and fixed I/O as well as the total I/O per second. The report should be run periodically to determine if I/O rates are fluctuating. A version of this report that uses STATSPACK data is also available so that interval I/O rates can be determined.

Elapsed Sec : 2,183,399.00
Fixed IO/SEC: 190.96
Temp IO/SEC : .00
Total IO/SEC: 190.96

PL/SQL procedure successfully completed.