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

• DBMS_RESOURCE_MANAGER.CALIBRATE_IO
• 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.

DBMS_RESOURCE_MANAGER.CALIBRATE_IO

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
------------------------------------------------ ---------
D:\APP\ORACLE\ORADATA\ORA11G\SYSTEM01.DBF        ASYNC_ON
D:\APP\ORACLE\ORADATA\ORA11G\SYSAUX01.DBF        ASYNC_ON
D:\APP\ORACLE\ORADATA\ORA11G\UNDOTBS01.DBF       ASYNC_ON
D:\APP\ORACLE\ORADATA\ORA11G\USERS01.DBF         ASYNC_ON
D:\APP\ORACLE\ORADATA\ORA11G\EXAMPLE01.DBF       ASYNC_ON
D:\APP\ORACLE\ORADATA\ORA11G\USERS_ASSM01.DBF    ASYNC_ON
D:\APP\ORACLE\ORADATA\ORA11G\USERS_NOASSM01.DBF  ASYNC_ON

This procedure should be started

DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
--(<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;
/

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

ORION

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
/test_dev/dev1
/test_dev/dev2
/test_dev/dev3
/test_dev/dev4

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 11.2.0.4.0
orion_test_20160913_1121
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
orion_test_20160913_1121_hist.txt
orion_test_20160913_1121_iops.csv
orion_test_20160913_1121_lat.csv
orion_test_20160913_1121_mbps.csv
orion_test_20160913_1121_summary.txt
orion_test_20160913_1121_trace.txt
orion_test.lun

<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 🙂
Tomasz