Oracle Orion tips Oracle Database Tips by Donald Burleson

Oracle Orion is a tool to help predict performance of I/O loads on Oracle databases. Orion is specifically designed for simulating and predicting I/O bottlenecks against Oracle databases.

Oracle Orion is available for free download at this link below. Please note that Oracle Orion is not supported by Oracle:

The unsupported Oracle Orion disk I/O load utility only seems to work for raw devices on Linux and only runs on Windows and Linux, and this severely limits Orion's usefulness as the majority of clients seem to be using OCFS or a CFS such as Veritas on Linux.

We tested Orion against the RAID0 set on our server, and against two regular file system disks, both gave back "Non test error occurred - Orion exiting" after the initial startup messages stating how long the test would take.

You can only do Orion read tests with disks you don't want to write over as the write test will write over all data on the disk volumes you want to test according to the documentation. Orion appears to be a very dangerous tool for example if you specified the write test at a client site you could ruin their installation. Also, clients probably won't have RAW disk devices just laying around to test against.

The Orion utility is more for setting up new systems, but only if they get it to use regular file systems instead of RAW only.

In the previous chapter, the iperf utility was used to obtain performance metrics of the Cluster Interconnect. However, many other network performance tools exist. For Oracle databases, there is one disk performance tool that today's Oracle database administrators typically use, called "Orion". While many utilities can measure disk performance, Orion is the only tool engineered to simulate Oracle database disk calls using the same I/O software as the Oracle database engine. Prior to Orion, one used other available disk performance utilities. However, those utilities never quite come close to mimicking Oracle database I/O. So the next tactic was to use a performance tool that would generate a load on an Oracle instance. However, things like the Buffer Cache can often skew the results as to make the test metrics unusable for measuring just disk performance. Orion combines the best of both worlds. Oracle Corporation supplies Orion for a very good price, free.

Orion stands for ORacle I/O Numbers. The utility does not need an Oracle database created on the server. The documentation says that Orion does not need the Oracle RDBMS software installed.  However the easiest way to obtain the utility is by installing Oracle 11.2 and higher.

Orion can simulate a variety of workloads including:

Small random I/O similar to OLTP applications.

Large sequential I/O similar to data warehouse applications.

Large random I/O to test striping across multiple disk units.

A mixed workload of small random I/O and either large sequential I/O or large random I/O.

We use Orion to test the storage system that will be most representative of the application type to be deployed against the database on that storage.

An easy way to understand Orion is to see it in action. This first example will test NFS disk storage. The NFS storage must be mounted to the server. A file needs to be created to use in the test. The file should be at least 1 gigabyte in size. If the file is too small, it can negatively impact the results. The dd command can be used to pre-create a file for testing.

[oracle@host01 orion_test]$ dd if=/dev/zero of=test.file bs=1G count=1

1+0 records in

1+0 records out

1073741824 bytes (1.1 GB) copied, 114.455 seconds, 9.4 MB/s

[oracle@host01 orion_test]$ ls -l

total 1052712

-rw-r--r-- 1 oracle oinstall 1073741824 Jul 24 22:16 test.file

If there are multiple NFS mounts to be tested, create a file on each mount point. Orion needs to know the test file names so a parameter file will be created containing the file names, one to a line. The parameter file's name must be of the format "testname.lun" where testname is defined at runtime. If no test name is provided, it defaults to "orion". The file created above is the only line in the file named "firsttest.lun" as can be seen below.

[oracle@host01 ~]$ cat firsttest.lun

/u01/app/oracle/orion_test/test.file

All that's left is to start Orion. The first test will simulate a small random I/O workload so the run type will be "oltp". The test system is Linux but does not have huge pages configured. Orion's default is to use huge pages. So Orion needs to be instructed to not use huge pages with the –hugenotneeded parameter. For systems that do have huge pages configured, use the default setting. Running the Orion utility produces output similar to the following.

[oracle@host01 ~]$ $ORACLE_HOME/bin/orion -run oltp \

> -testname firsttest -hugenotneeded

ORION: ORacle IO Numbers -- Version 11.2.0.4.0

firsttest_20140724_2232

Calibration will take approximately 22 minutes.

Using a large value for -cache_size may take longer.

Maximum Small IOPS=13018 @ Small=15 and Large=0

Small Read Latency: avg=1152 us, min=312 us, max=11984 us, std dev=390 us @ Small=15 and Large=0

Minimum Small Latency=921 usecs @ Small=11 and Large=0

Small Read Latency: avg=921 us, min=304 us, max=85692 us, std dev=462 us @ Small=11 and Large=0

Small Read / Write Latency Histogram @ Small=11 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:       0                   0

       64 - 128         us:       0                   0

      128 - 256         us:       0                   0

      256 - 512         us:    2102                   0

      512 - 1024        us:  603335                   0

     1024 - 2048        us:   91864                   0

     2048 - 4096        us:   16386                   0

     4096 - 8192        us:    2090                   0

     8192 - 16384       us:      71                   0

    16384 - 32768       us:      10                   0

    32768 - 65536       us:       3                   0

    65536 - 131072      us:       1                   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

Right after the Orion test starts running, it will provide an estimate of how long the test will take to complete. After the test is complete, metrics on IOPS and latency are provided. The last part of the output on the screen shows a histogram of latency.

In the output above, the latency for read operations fell between 256 microseconds to 131072 microseconds with the bulk in the 512 to 1024 microsecond range. The OLTP test did not perform any write operations. While the output on the screen is good information to know, the best output is provided in some in the comma-separated values (CSV) files Orion creates. The file names will contain the test name and the date and time of the test. Two important CSV files are the one that shows IOPS and the other that shows megabytes per second. Visualization of data points is often better than raw data. The IOPS CSV file contents are loaded into a spreadsheet and the output is graphed as seen in the graph below.

Throughput graphs often level off as the data points move to the right side of the graph. Where the graph levels off, the system being measured is saturated. In the graph above, this disk storage becomes saturated around 13,000 IOPS.

The OLTP test provided IOPS metrics for the disk subsystem. The data warehouse test provides MBPS metrics by performing large sequential I/O operations. The only difference in the way the second test is executed lies with the –run parameter to define the run type.

[oracle@nau-rac01 ~]$ $ORACLE_HOME/bin/orion -run dss \

> -testname secondtest -hugenotneeded

ORION: ORacle IO Numbers -- Version 11.2.0.4.0

secondtest_20140725_0948

Calibration will take approximately 65 minutes.

Using a large value for -cache_size may take longer.

Maximum Large MBPS=66.85 @ Small=0 and Large=6

The output on the screen for the DSS, or data warehouse test, is much smaller than for the random I/O, OLTP test. Only one item of note is provided on the screen, the maximum megabytes per second.  Similar to the OLTP test, a CSV file (comma delimited spreadsheet file) is generated which can be visualized in a Excel spreadsheet graph.

Not surprisingly, the graph levels off as it travels to the right. This shape is a classic throughput profile. This book could have shown the data points, but the visual representation provides a much clearer picture. The figure above clearly shows the throughput levels off once the disk subsystem crosses 60 MBPS.

Orion can be a very useful tool in determining the throughput and performance of the disk subsystem. This section is only meant as a primer on Orion's capabilities. More information on Orion can be found in the Oracle Performance Tuning Guide in the I/O Configuration and Design chapter.

Many storage systems have a variety of configuration parameters to tune the disk access for optimal performance. Use Orion to obtain a performance baseline. As disk system parameters are tweaked, Orion can be run again to determine if the change will improve performance for an Oracle database. For instance, later in this chapter there is a section discussing mount point options for NAS storage. Orion can be used to obtain a baseline of the NAS storage performance. Then change a mount point option and run the same Orion test again. Comparing to the baseline will help understand if the change helped or hurt storage performance for an Oracle RAC database. 

Migrations from a single-instance database system to Oracle RAC can benefit greatly from using Orion before the RAC migration takes place. We use Orion to obtain a baseline on the single-instance disk units. When running Orion, it is important to make sure the disk units have no other activity because  it can skew the Orion results. With the Oracle RAC shared storage up and running, run Orion again and compare the results to the single-instance baseline.  In sum, the RAC shared storage should perform no worse than the single-instance database's disk subsystem.