I/O Performance Tuning Tools for Oracle Database 11gR2

July 28, 2010

Synopsis. Tuning the I/O performance of an Oracle 11gR2 database is still just as much art as science. Oracle 11g Release 2 (11gR2) makes it easier than ever, however, to isolate and identify potential root causes of poor I/O performance. This article – the second in this series – focuses on the various Oracle and non-Oracle tools to generate sample workloads to provide sufficient metrics for detection and eventual resolution of performance bottlenecks within the database’s underlying input/output (I/O) subsystem.

The prior article in this series discussed much of the theory behind I/O performance tuning, especially as it related to Oracle 11gR2 database applications. This article will address several key practical concepts, including how to:

• Measure expected and actual I/O performance using established I/O monitoring tools
• Create pre-database and post-database I/O performance workloads

Let’s start off with a discussion of how to monitor all of the crucial components of any I/O subsystem via a plethora of available tools – some more sophisticated than others. I’m going to focus on the Linux operating system for this article series, but corollary utilities in most cases exist in the Windows OS environment and certainly exist in all UNIX OS environments.

I/O Performance Monitoring Tools

Most of the tools that Oracle DBAs continue to use for I/O performance monitoring offer the ability to reveal sufficient I/O tuning information from deep within the operating system. These tools break down into two basic groups:

Command-Line Tools. These reliable standard command-line tools have been around since the dawn of computing (well, at least since green characters were typed upon black backgrounds). Since they are character-based, however, they don’t offer much in the way of slick display; on the other hand, they tend to present a relatively small memory footprint - and that may be crucial when trying to analyze an I/O performance problem that’s masquerading as a memory-bound or CPU-bound issue. Here are a few of my favorites:

• Probably the best-known utility, iostat lets me view basic or extended statistics about the physical storage devices that my server is aware of. For example, I can instruct iostat to look at the number of transactions per second (TPS), the number of OS blocks read or written, and the rate at which they’re written (blocks per second). I can also tell iostat to constantly monitor specific devices for a specific number of repetitions. I’ve captured some samples of iostat execution in Listing 2.1.1.
• Likewise, the vmstat utility offers the capability to see total reads and writes to physical devices (via the –d option) and for a specific partition (via the –p option). A few samples of vmstat execution are shown in Listing 2.1.2.
• Though used less frequently, the sar utility (via the –b option) can also be used to dump historical information about transactions per second, reads and writes per second, and block reads and writes per second from the system architecture history files. I’ve shown results from a sample sar -b execution in Listing 2.1.3.

Oracle-Based Tools. Not surprisingly, the Oracle Database software itself also offers several I/O performance monitoring tools.

• Enterprise Manager (EM). ASM was first introduced in Oracle 10gR1. Concurrent with its release, Oracle DBAs were finally able to graphically view I/O activity and statistics with full drill-down capability to the ASM disk group and ASM disk level. Figure 2.1 below shows a summary screen from Oracle 11gR2 EM to show the level of I/O performance detail for ASM:

Figure 2.1: Enterprise Manager ASM I/O Performance Details.

Starting in Oracle 11gR1, EM also offered the ability to view I/O statistics from several different perspectives, including within background and foreground process type, I/O type, and even resource consumer group. I’ve included a few sample screens below to show this 11g feature, and I’ll be using this extensively in future articles to investigate and drill down through real-time I/O performance metrics.

Figure 2.2: Enterprise Manager I/O Performance Statistics Within I/O Function.

Figure 2.3: Enterprise Manager I/O Performance Statistics Within I/O Type.

• Automatic Workload Repository (AWR) Reports. Also released in Oracle 10gR1, AWR reports offer the ability to see read and write I/O statistics at the tablespace and datafile level. And the same I/O profiling information present in Oracle 11gR2 EM is also captured within every AWR report in this release, as shown in this sample AWR report. (Note that AWR’s older and feebler brother, StatsPack, also offers similar but more limited views into tablespace and datafile I/O activity.)
• ASMCMD: iostat. Starting in Oracle 11gR2, the ASMCMD command-line utility offers the iostat subcommand for any ASM file systems that are available. Remember that ASMCMD must be invoked from a terminal session that’s logged as the owner of the Oracle 11gR2 Grid Infrastructure home (typically, the grid user account) – not as the owner of the Oracle database home. I’ve pulled together a few examples of how this information can be captured and displayed in Listing 2.2.
• SQL*Plus Queries. Since the inception of ASM in Oracle 10gR1, the Oracle ASM instance has always exposed all ASM-related information through several dynamic views. I’ve often written reports directly against these views to obtain the information I wanted in the format desired. I’ve included several queries in Listing 2.3.1 and corresponding sample output from these queries in Listing 2.3.2.

I/O Generation Tools: Extra-Database

Several tools are useful for what I like to call “extra-database” analysis. In other words, these tools can be used to generate an I/O workload from outside an Oracle database environment. However, these tools must be used carefully – and probably never within a production database environment! – because they literally could care less about the state of any extant Oracle database files.

• The venerable dd command can actually be used as a crude I/O load generation tool. It’s simple to use, comes free with Linux (and with most flavors of Unix). Note that while there is no such corollary tool as dd for the Windows OS, it’s possible to use Linux emulators like MKS Toolkit to provide similar functionality.
• vdbench is a free load generation tool originally created by Sun Microsystems that’s favored by many storage administrators and I/O performance gurus among the largest storage vendors (e.g., EMC, Hitachi, IBM, NetApp) for generating large, predictable, and sophisticated I/O workloads. It can also be run against either Windows-based, UNIX-based, or Linux-based “cooked” file systems, as well as “raw” file systems, and it also offers a rudimentary GUI for performance test execution. Best of all, it’s extremely well-documented. Vdbench also offers some excellent I/O performance reports and real-time performance monitoring capabilities. I’ll demonstrate some examples of using vdbench to generate extreme I/O workloads in future articles in this series.
• ORION. The Oracle Input/Output Numbers calibration tool is a rather bare-bones, freely-supplied set of tools available from Oracle Technology Network. ORION has been around for many years, and it supports every major OS and hardware environment that Oracle databases run on, including UNIX, Linux, and Windows. It’s also simple to install – on Linux, it’s just an executable file – and relatively easy to configure. The documentation provided is a bit sparse, but more than sufficient for setting up ORION simply and quickly.

[oracle@11gR2Base ~]$

./orion_x86_64

ORION: ORacle IO Numbers -- Version 11.1.0.7.0 Parse error: View help screen using ./orion_x86_64 -help. [oracle@11gR2Base ~]$ ./orion_x86_64 -help ORION: ORacle IO Numbers -- Version 11.1.0.7.0 ORION runs IO performance tests that model Oracle RDBMS IO workloads. It measures the performance of small (2-32K) IOs and large (128K+) IOs at various load levels. Each Orion data point is done at a specific mix of small and large IO loads sustained for a duration. Anywhere from a single data point to a two-dimensional array of data points can be tested by setting the right options. An Orion test consists of data points at various small and large IO load levels. These points can be represented as a two-dimensional matrix: Each column in the matrix represents a fixed small IO load. Each row represents a fixed large IO load. The first row is with no large IO load and the first column is with no small IO load. An Orion test can be a single point, a row, a column or the whole matrix. The 'run' parameter is the only mandatory parameter. Defaults are indicated for all other parameters. For additional information on the user interface, see the Orion User Guide. <testname> is a filename prefix. By default, it is "orion". It can be specified with the 'testname' parameter. <testname>.lun should contain a carriage-return-separated list of LUNs The output files for a test run are prefixed by <testname>_<date> where date is "yyyymmdd_hhmm". The output files are: <testname>_<date>_summary.txt - Summary of the input parameters along with min. small latency, max large MBPS and/or max. small IOPS. <testname>_<date>_mbps.csv - Performance results of large IOs in MBPS <testname>_<date>_iops.csv - Performance results of small IOs in IOPS <testname>_<date>_lat.csv - Latency of small IOs <testname>_<date>_tradeoff.csv - Shows large MBPS / small IOPS combinations that can be achieved at certain small latencies <testname>_trace.txt - Extended, unprocessed output WARNING: IF YOU ARE PERFORMING WRITE TESTS, BE PREPARED TO LOSE ANY DATA STORED ON THE LUNS. Mandatory parameters: run Type of workload to run (simple, normal, advanced, dss, oltp) simple - tests random 8K small IOs at various loads, then random 1M large IOs at various loads. normal - tests combinations of random 8K small IOs and random 1M large IOs advanced - run the workload specified by the user using optional parameters dss - run with random 1M large IOs at increasing loads to determine the maximum throughput oltp - run with random 8K small IOs at increasing loads to determine the maximum IOPS Optional parameters: testname Name of the test run num_disks Number of disks (physical spindles). Default is the number of LUNs in <testname>.lun size_small Size of small IOs (in KB) - default 8 size_large Size of large IOs (in KB) - default 1024 type Type of large IOs (rand, seq) - default rand rand - Random large IOs seq - Sequential streams of large IOs num_streamIO Number of concurrent IOs per stream (only if type is seq) - default 4 simulate Orion tests on a virtual volume formed by combining the provided volumes in one of these ways (default concat): concat - A serial concatenation of the volumes raid0 - A RAID-0 mapping across the volumes write Percentage of writes (SEE WARNING ABOVE) - default 0 cache_size Size *IN MEGABYTES* of the array's cache. Unless this option is set to 0, Orion does a number of (unmeasured) random IO before each large sequential data point. This is done in order to fill up the array cache with random data. This way, the blocks from one data point do not result in cache hits for the next data point. Read tests are preceded with junk reads and write tests are preceded with junk writes. If specified, this 'cache warming' is done until cache_size worth of IO has been read or written. Default behavior: fill up cache for 2 minutes before each data point. duration Duration of each data point (in seconds) - default 60 num_small Number of outstanding small IOs (only if matrix is point, col, or max) - no default num_large For random, number of outstanding large IOs. For sequential, number of streams (only if matrix is point, row, or max) - no default matrix An Orion test consists of data points at various small and large IO load levels. These points can be represented as a two-dimensional matrix: Each column in the matrix represents a fixed small IO load. Each row represents a fixed large IO load. The first row is with no large IO load and the first column is with no small IO load. An Orion test can be a single point, a row, a column or the whole matrix, depending on the matrix option setting below (default basic): basic - test the first row and the first column detailed - test the entire matrix point - test at load level num_small, num_large col - varying large IO load with num_small small IOs row - varying small IO load with num_large large IOs max - test varying loads up to num_small, num_large verbose Prints tracing information to standard output if set. Default -- not set Examples For a preliminary set of data -run simple For a basic set of data -run normal To evaluate storage for an OLTP database -run oltp To evaluate storage for a data warehouse -run dss To generate combinations of 32KB and 1MB reads to random locations: -run advanced -size_small 32 -size_large 1024 -type rand -matrix detailed To generate multiple sequential 1MB write streams, simulating 1MB RAID0 stripes -run advanced -simulate RAID0 -stripe 1024 -write 100 -type seq -matrix col -num_small 0

I/O Generation Tools: Freeware

What if I need to capture and then regenerate a specific application workload? There are several tools available:

• The Transaction Processing Council (TPC) provides a series of scriptsthat can be used to construct and load an Oracle database of an intended size and application workload “intent” (e.g. OLTP, OLAP). Once a database of an appropriate type is created, TPC does provide extensive documentation for its numerous workloads, including its TPC-C (OLTP), TPC-E (Extended OLTP), and TPC-H (data warehousing / decision support) application database models. The TPC-E However, TPC only provides one tool that can be publicly downloaded for TPC-E application workload generation, and that needs to be compiled using Microsoft’s Visual Studio and C++. I’ll demonstrate how to set up these schemas in the very next article, and I’ll demonstrate the use of a similar load generation tool in future articles as well.

• Swingbench is another popular freeware tool for load generation. Written by well-known author and blogger Dominic Giles, it also provides command-line as well as GUI interfaces for creating some pretty sophisticated workloads for Oracle 9i, 10g, and 11g databases. I’ll demonstrate Swingbench’s capabilities in a future article in this series.

I/O Generation Tools: Not-So-Freeware

What if I need to capture and then regenerate a specific application workload, and I have significant budget to spend doing that? While free tools are great, the old saying that “You get what you pay for” certainly holds true here … especially when support for a technical problem or special testing case warrants outside help. The good news here is that there are several tools available; here are three of my favorite ones:

• Mercury Interactive originally developed the LoadRunner tool many years ago, and Hewlett Packard now owns it. It's definitely the most mature tool in this group. LoadRunner actually captures application keystrokes and allows me to replay a workload using those keystrokes as a user profile, and then crank up multiple versions of that workload – say, 20 OLTP users, five DSS users, three OLAP users, and two DBAs – against an Oracle database. However, the cost for such a sophisticated tool can easily run to six figures; furthermore, it may not be the best way to capture and replay an I/O performance workload because what I really need to duplicate is the I/O profile generated by the SQL statements being executed against the database, not necessarily the exact application keystrokes.

• Quest Software’s Benchmark Factory is one of the more sophisticated tools for generating I/O workloads I’ve encountered. I’ve used both the 30-day trial version (which is surprisingly robust!) as well as a fully-licensed version to generate extremely large I/O performance workloads. It also makes short work of creating and loading an initial TPC-C, TPC-H, or SPC-1 model database that conforms to official standards for correlative comparisons of I/O workloads between completely different I/O subsystem configurations. Benchmark Factory offers quite an intuitive GUI, allows extensive modifications to standard workloads if so desired, and features excellent reporting and graphing capabilities. Again, be aware that Benchmark Factory is anything but free, and its licensing costs - plus the hardware to run Benchmark Factory agents to generate the required I/O performance workloads! - can easily run into six-figure USD ranges.

• Finally, I’d be remiss to ignore Oracle Database Replay (DBR). DBR does require additional Oracle software licensing, but it may well be worth evaluating as an alternative to both LoadRunner and Benchmark Factory in terms of overall cost. DBR was first available in Oracle 11gR1; I’ve written about it extensively in a prior three-part article series. DBR’s basic functionality hasn’t changed dramatically in Oracle 11gR2, with one notable exception: It’s now possible to scale up a query workload based on a supplied multiplier factor. (For obvious reasons, this wouldn’t work very well for DML statements!) I’ll leverage that capability in a future article within this series.

Next Steps

Now that I’ve covered some of the more common I/O load generation and performance testing tools, it’s time to actually tackle some real-world testing. The next article is this series will focus on how to:

• Implement an extra-database I/O performance test
• Measure the results of an extra-database I/O performance test
• Create and load sample TPC-C and TPC-H database schemas prior to intra-database I/O performance tests

References and Additional Reading

Before you proceed to experiment with any of these new features, I strongly suggest that you first look over the corresponding detailed Oracle documentation before trying them out for the first time. I’ve drawn upon the following Oracle Database 11g Release 2 documents for this article’s technical details:

E10881-02 Oracle Database 11gR2 New Features

E10595-05 Oracle Database 11gR2 Administrator’s Guide

E10713-03 Oracle Database 11gR2 Concepts

E10820-02 Oracle Database 11gR2 Reference

E10500-02 Oracle Database 11gR2 Storage Administrator’s Guide

E10577-05 Oracle Database 11gR2 PL/SQL Packages and Types Reference

» See All Articles by Columnist Jim Czuprynski