Do you want to check the I/O performance of your database machine?

Sometimes, we need to check the I/O performance of our database machine. For example, after migrating to a new machine, specific file I/O moves too slow and etc. In that case, what is the most useful tool? Maybe, ORION is the best tool to satisfy all your needs. ORION stands for ORacle IO Numbers. And ORION was included in Oracle 11gR2. So, you can find ORION executable file under $ORACLE_HOME/bin directory.
The usage of ORION is very simple. With “-help” command wil give detail usage how to use ORION.
Now, let’s try to test.

1) First of all, we must edit orion.lun file which is located in $ORACLE_HOME/bin.
In my case, I want to check the I/O performance of undo datafile only. So, edit orion.lun file like follwing.

$ cd $ORACLE_HOME/bin
$ vi orion.lun
/oradata/undotbs01.dbf

2) Run orion.
Types of workload to run are simple, normal, advanced, dss, olpt. You can find the explanations of these options, with “-help” command. I choose the simple option.

$ ORACLE_HOME/bin/orion -run simple

ORION: ORacle IO Numbers -- Version 11.2.0.3.0
orion_20130521_1540
Calibration will take approximately 9 minutes.
Using a large value for -cache_size may take longer.

3) Analysis the output files
The number of output files is 5. You can also find the explanations of these files, with “-help” command. Anyway, The files that i interested are “orion__mbps.csv” and “orion__iops.csv”. The maximum Large MBPS=14.28 which is Small=#0 and Large=#2. MBPS stands for Mega Bits Per Second.

$ cat orion_20130521_1417_mbps.csv
Large/Small, 0, 1, 2, 3, 4, 5
1, 13.02
2, 14.28

The maximum small IOPS=219 which is Small=#5 and Large=#0. IOPS stands for IO counts Per Second.

$ cat orion_20130521_1417_iops.csv
Large/Small, 1, 2, 3, 4, 5
0, 138, 166, 194, 206, 219
1
2

The another tool for I/O calibration is DBMS_RESOURE_MANAGER.calibrate_io procedure. The usage and ouput are like following. The unit of actual_latency is millisecond (1/1,000). And, I recommand that this procedure don’t run in working time. Because, when you issue this procedure, oracle forks many background processes and run simultaneously.

SQL> 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 = 156
Max MBPS = 10 
Latency  = 18