Measure storage performance in Oracle

This article presents methods to check storage performance IOPS and MBPS in Oracle:

  • ORION tool

There are many requirements when design I/O system like:

Striping, mirroring, online reconfiguration, dynamic re-balancing, concurrency of I/O request, random read-writes, sequential reads, oracle database parameters important for storage performance: db_block_size, db_file_multiblock_read_count, filesystemio_options or separation of database datafiles and others.

However once storage is configured then it need to be tested to check response IOPS(I/O  per second) and throughput MBPS(Megabytes per second).That’s the article is about.


Easy method to calibrate the I/O capabilities storage is to call procedure CALIBRATE_IO from package DBMS_RESOURCE_MANAGER.

Following parameters are used

  • num_physical_disk – approximate number of physical disks in the database storage
  • max_latency – maximum tolerable latency in miliseconds for database-block-sized I/O request
  • max_iops – maximum sustained I/O request per second (out parameter)
  • max_mbps – maximum sustained throughput I/O per seconds, in megabytes (out parameter)
  • actual_latency – average latency for database-block-sized I/O request at amx_iops rate in miliseconds (out parameter)

Prerequisites to run it

  • user that starts it must be granted SYSDBA privilege
  • TIMED_STATISTICS must be set to TRUE
ALTER SYSTEM SET timed_statistics=true SCOPE=BOTH;
  • asynchronous I/O must be enabled by setting parameter FILESYSTEMIO_OPTIONS to SETALL. If not set already requires restart of the database.
ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;

Verify async is on after restart – ASYNC_ON

SELECT name, asynch_io 
  FROM v$datafile f,v$iostat_file i
 WHERE f.file#=i.file_no
   AND filetype_name='Data File';

NAME                                             ASYNCH_IO
------------------------------------------------ ---------

This procedure should be started

  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
--(<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);

Information about running calibration process is visible in V$IO_CALIBRATION_STATUS. Once completed all results are visible in view DBA_RSRC_IO_CALIBRATE.


Oracle Orion is simple tool that can allow to predict I/O performance of an Oracle database without having to create a database. It can simulate Oracle database I/O workloads using the same I/O software stack as Oracle plus effect of striping performed by Oracle Automatic Storage Management.

Orion I/O workload support

  • small random I/O
  • large sequential I/O
  • large random I/O
  • mixed workloads

Orion is dedicated for character devices that supports asynchronous I/O

  • SAN (storage area network)
  • DAS (direct attached storage)
  • NAS (network attached storage)

Orion tool is stored in directory $ORACLE_HOME/bin and have many parameters to use

export ORACLE_HOME=/ora01/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
$ORACLE_HOME/bin/orion -help

Following example shows how to use orion

In my case I have created test devices

mkdir /test_dev

dd if=/dev/zero of=/test_dev/dev1 bs=10M count=10
dd if=/dev/zero of=/test_dev/dev2 bs=10M count=10
dd if=/dev/zero of=/test_dev/dev3 bs=10M count=10
dd if=/dev/zero of=/test_dev/dev4 bs=10M count=10

create configuration file as <testname>.lun where testname is test name used later

vi orion_test.lun

Run test. It takes a while to finish.

[root@oel6 Desktop]# 
$ORACLE_HOME/bin/orion -run normal -testname orion_test -hugenotneeded
ORION: ORacle IO Numbers -- Version
Calibration will take approximately 190 minutes.
Using a large value for -cache_size may take longer.

Maximum Large MBPS=1519.24 @ Small=0 and Large=1

Maximum Small IOPS=10964 @ Small=20 and Large=0
Small Read Latency: avg=1821 us, min=432 us, max=5444 us, 
std dev=176 us @ Small=20 and Large=0

Minimum Small Latency=110 usecs @ Small=1 and Large=0
Small Read Latency: avg=110 us, min=44 us, max=29728 us, 
std dev=58 us @ Small=1 and Large=0
Small Read / Write Latency Histogram @ Small=1 and Large=0 
    Latency:        # of IOs (read)      # of IOs (write) 
        0 - 1        us:        0            0
        2 - 4        us:        0            0
        4 - 8        us:        0            0
        8 - 16        us:        0            0
       16 - 32        us:        0            0
       32 - 64        us:        178            0
       64 - 128        us:        469144            0
      128 - 256        us:        59908            0
      256 - 512        us:        1157            0
      512 - 1024    us:        926            0
     1024 - 2048    us:        12            0
     2048 - 4096    us:        2            0
     4096 - 8192    us:        2            0
     8192 - 16384    us:        1            0
    16384 - 32768    us:        1            0
    32768 - 65536    us:        0            0
    65536 - 131072    us:        0            0
   131072 - 262144    us:        0            0
   262144 - 524288    us:        0            0
   524288 - 1048576    us:        0            0
  1048576 - 2097152    us:        0            0
  2097152 - 4194304    us:        0            0
  4194304 - 8388608    us:        0            0
  8388608 - 16777216    us:        0            0
 16777216 - 33554432    us:        0            0
 33554432 - 67108864    us:        0            0
 67108864 - 134217728    us:        0            0
134217728 - 268435456    us:        0            0

results are saved to files

[root@oel6 Desktop]# ls
<testname>_<date>_hist.csv Histogram of I/O latencies.
<testname>_<date>_iops.csv Performance results of small I/Os in IOPS.
<testname>_<date>_lat.csv Latency of small I/Os in microseconds.
<testname>_<date>_mbps.csv Performance results of large I/Os in MBPS.
<testname>_<date>_summary.txt Summary of the input parameters, along with the minimum small I/O latency (in secs), the maximum MBPS, and the maximum IOPS observed.
<testname>_<date>_trace.txt Extended, unprocessed output.

Other examples for ORION

  • Evaluate storage for OLTP
orion -run oltp
  • Evaluate storage for warehouses
orion -run dss
  • Evaluate storage performance with a mixed small and large random I/O workload
orion -run normal
  • Generate combinations of 32KB and 1MB reads to random locations
orion -run advanced -size_small 32 \
-size_large 1024 -type rand -matrix detailed
  • Generate multiple sequential 1 MB write streams, simulating 1 MB RAID-0 stripes
orion -run advanced -simulate raid0 \
-stripe 1024 -write 100 -type seq -matrix col -num_small 0
  • Generate combinations of 32 KB and 1 MB reads to random locations
orion -run advanced -size_small 32 \
-size_large 1024 -type rand -matrix detailed
  • Generate multiple sequential 1 MB write streams, simulating RAID0 striping
orion -run advanced -simulate raid0 \ 
-write 100 -type seq -matrix col -num_small 0

Have a fun 🙂

2 thoughts on “Measure storage performance in Oracle

  1. Hi Tomasz,
    The parameters strike me as odd and out of touch with the times. The most of time I want to know the performance of my LUN provided by the SAN. I usually have no idea about the number of disks involved. This tool doesn’t strike me as useful. Also, CALIBRATE_IO is used to determine automatic DOP (“Degree of Parallelism”) and I remember it well from and, when it messed up application performance, by launching a massive amount of parallel queries. I am not sure that either tool is ready for being used.

Leave a Reply

Your email address will not be published. Required fields are marked *