8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Measuring Storage Performance For Oracle Systems

Storage vendors offer endless amounts of performance data on their products, but the information doesn't necessarily reflect how the storage will perform when it is used by an Oracle database. This article outlines some easy ways to test the performance of your storage systems, including some utilities provided by Oracle.

Remember, the Oracle utilities use a simulated load to mimic the type of operations performed by an Oracle database. There is no guarantee your applications will be able to achieve the level of performance they produce.

DD

For a quick and dirty test of your disk performance, you can time writes performed by the dd command.

$ time sh -c "dd if=/dev/zero of=dd-test-file bs=8k count=1000000 && sync"
1000000+0 records in
1000000+0 records out

real    0m18.42s
user    0m0.70s
sys     0m16.77s
$ ls -l ddfile
-rw-r--r--   1 oracle     oinstall   8192000000 Nov  2 16:11 dd-test-file
$ rm dd-test-file
$

This is no reflection of how disk performance will look when being accessed by Oracle systems.

ORION

ORION (ORacle IO Numbers) mimics the type of I/O performed by Oracle databases, which allows you to measure I/O performance for storage systems without actually installing Oracle. It used to be available to download for a number of platforms from OTN, but that download is no longer available. Instead, it is included in the "$ORACLE_HOME/bin" directory of Grid Infrastructure (GI) and database installations.

The usage information is displayed with the following command.

# export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
# $ORACLE_HOME/bin/orion -help

To run the test you need to know the LUNs you will be using for Oracle. In this case I am running it on a little VM, so I'm just going to fake the LUNs using the ext3 filesystem.

# mkdir /luns
# dd if=/dev/zero of=/luns/lun1 bs=1024k count=10
# dd if=/dev/zero of=/luns/lun2 bs=1024k count=10
# dd if=/dev/zero of=/luns/lun3 bs=1024k count=10
# dd if=/dev/zero of=/luns/lun4 bs=1024k count=10

Next, create a file to hold the LUN configuration. In this case I will call my test "ob-test", so my LUN configuration file must be called "ob-test.lun". It is assumed it is present in the current directory. The file should contain a list of the luns used in the test.

/luns/lun1
/luns/lun2
/luns/lun3
/luns/lun4

If you don't specify a test name, the utility assumes the test is called "orion" and looks for the presence of the "orion.lun" file.

Next we run a test. I'm going to do a full grid of tests, comparing the performance of a range of small I/O operations (8K) against a range of large I/O operations (1M). Doing the run using the "normal" option can take a long time, so you may want to try using the "basic" option first.

# $ORACLE_HOME/bin/orion -run normal -testname ob-test
ORION: ORacle IO Numbers -- Version 12.1.0.2.0
ob-test_20140828_1804
Calibration will take approximately 190 minutes.
Using a large value for -cache_size may take longer.

Maximum Large MBPS=1672.01 @ Small=0 and Large=2

Maximum Small IOPS=16262 @ Small=13 and Large=1
Small Read Latency: avg=796 us, min=111 us, max=4927 us, std dev=254 us @ Small=13 and Large=1

Minimum Small Latency=148 usecs @ Small=1 and Large=0
Small Read Latency: avg=148 us, min=0 us, max=82630 us, std dev=209 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:             18                      0
        2 - 4           us:             0                       0
        4 - 8           us:             0                       0
        8 - 16          us:             3                       0
       16 - 32          us:             4                       0
       32 - 64          us:             5                       0
       64 - 128         us:             64513                   0
      128 - 256         us:             325674                  0
      256 - 512         us:             5759                    0
      512 - 1024        us:             139                     0
     1024 - 2048        us:             21                      0
     2048 - 4096        us:             3                       0
     4096 - 8192        us:             1                       0
     8192 - 16384       us:             0                       0
    16384 - 32768       us:             1                       0
    32768 - 65536       us:             0                       0
    65536 - 131072      us:             3                       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
#

On completion of the test, the directory with the ORION executable will contain several new files containing information about the IOPS, MBPS and latency gathered for each test. Here are the files produced by this run.

Obviously, the parameters you use for your runs will have to be tailored to reflect your setup.

Remember, no simulation is ever perfect, but the results give you an idea of what your storage system is capable of delivering.

DBMS_RESOURCE_MANAGER.CALIBRATE_IO

Introduced in Oracle Database 11g Release 1, the CALIBRATE_IO procedure gives an idea of the capabilities of the storage system from within Oracle. There are a few restrictions associated with the procedure.

You can check your current asynchronous I/O setting for your datafiles using the following query.

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

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/DB11G/system01.dbf         ASYNC_OFF
/u01/app/oracle/oradata/DB11G/sysaux01.dbf         ASYNC_OFF
/u01/app/oracle/oradata/DB11G/undotbs01.dbf        ASYNC_OFF
/u01/app/oracle/oradata/DB11G/users01.dbf          ASYNC_OFF
/u01/app/oracle/oradata/DB11G/example01.dbf        ASYNC_OFF

5 rows selected.

SQL>

To turn on asynchronous I/O, issue the following command and restart the database.

ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;

Provided your storage supports asynchronous I/O, the ASYNC_IO flag should now have changed.

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

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/u01/app/oracle/oradata/DB11G/system01.dbf         ASYNC_ON
/u01/app/oracle/oradata/DB11G/sysaux01.dbf         ASYNC_ON
/u01/app/oracle/oradata/DB11G/undotbs01.dbf        ASYNC_ON
/u01/app/oracle/oradata/DB11G/users01.dbf          ASYNC_ON
/u01/app/oracle/oradata/DB11G/example01.dbf        ASYNC_ON

5 rows selected.

SQL>

You can now call the procedure by running the following code.

CONN / AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
  l_latency  PLS_INTEGER;
  l_iops     PLS_INTEGER;
  l_mbps     PLS_INTEGER;
BEGIN
   DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1, 
                                       max_latency        => 20,
                                       max_iops           => l_iops,
                                       max_mbps           => l_mbps,
                                       actual_latency     => l_latency);
 
  DBMS_OUTPUT.put_line('Max IOPS = ' || l_iops);
  DBMS_OUTPUT.put_line('Max MBPS = ' || l_mbps);
  DBMS_OUTPUT.put_line('Latency  = ' || l_latency);
END;
/

Max IOPS = 95
Max MBPS = 449
Latency  = 20

PL/SQL procedure successfully completed.

SQL>

In addition to appearing on screen, the results of a calibration run can be displayed using the DBA_RSRC_IO_CALIBRATE view.

SET LINESIZE 100
COLUMN start_time FORMAT A20
COLUMN end_time FORMAT A20

SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time,
       TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time,
       max_iops,
       max_mbps,
       max_pmbps,
       latency,
       num_physical_disks AS disks
FROM   dba_rsrc_io_calibrate;

START_TIME           END_TIME               MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY      DISKS
-------------------- -------------------- ---------- ---------- ---------- ---------- ----------
20-JUL-010 18:07:21  20-JUL-010 18:13:55          95        449        461         20          1

SQL>

Calibration runs can be monitored using the V$IO_CALIBRATION_STATUS view.

SLOB

In addition to the methods shown above, it is worth taking a look at the SLOB utility by Kevin Closson.

For more information see.

Hope this helps. Regards Tim...

Back to the Top.