Oracle Database Load Testing Tools – Overview

The right tool for the right job

• IO subsystem testing tools
• RDBMS Level testing tools
• Application Level testing tools (DB Side)
• Application Level testing tools (Apps Side)

IO subsystem testing tools

• Easy to run
• Short learning curve ( add 2-4 hours to first few runs for an initial learning)
• Low budget (4-16 hours should be enough for a testing project)
• Easy to get and reproduce results
• Easy to compare results with another platform

• May not represent your RDBMS or Application IO patterns
• Doesn't test anything else but Physical IO (fair enough)

• orion (Oracle IO Numbers) - This tool is developed and maintained by Oracle. It is part of the latest Oracle DB distributions (e.g. 11.2.0.3). Orion was created to test Oracle DB IO workloads. It uses some of the typical system calls that Oracle database kernel uses to retrieve and write data from/to data files.
  • Experience: I do have a lot of experience myself running it and comparing different storage performance. We do have other DBAs at Pythian who experienced running the tool. However, the best guru is my friend and Pythian's CTO Alex Gorbachev. Nobody knows more about orion utility than Alex does. :)
  • References:
    • Alex wrote and published a great presentation on the subject, "Benchmarking Oracle I/O Performance with Orion".
    • Oracle 11GR2 Documentation: "Introduction to the Oracle Orion Calibration Tool"
• winsat - Modern Microsoft Windows distributions have the winsat utility, which is capable to do some basic IO testing.
  • Experience: I have used this utility to confirm orion results on Windows platform.
  • References:
    • Microsoft TechNet Library, winsat disk
    • You may find some references from my experience under the "Hard drive Inner or Outer" blog post.
• iometer - My friend (Artur Gaffanov) suggested iometer as an alternative tool for the same category. This utility was originally developed by Intel and is currently maintained by an Open Source Community.
  • Experience: I do not have personal experience with the tool. However, a quick search though Pythian Knowledge Base (Support Track) retrieved several references. I would say there is some experience at Pythian.
  • References: https://www.iometer.org/

RDBMS Level testing tools

• Can use RDBMS calls to test system resources (uses database the same way as your application does)
• Can be used to test all main system's resources (CPU, Memory, IO)
• Relatively easy to run
• Short learning curve ( add 4-8 hours to first few runs for an initial learning)
• Relatively low budget (1-3 days should be enough for a testing project)

• It may not represent your Application IO patterns.
• You need a good basic understanding of RDBMS concepts.
• You may need to spend a bit more time verifying and adjusting an instance/test's configuration to ensure that it tests exactly what you want to test.
• It would take you more time to run the same number of test than using a tool from the first group.
• You may need to spend a bit more time to configure your Oracle Instance to test the performance the way you want.
• You need an Oracle Database to be up and running to use those testing tools.

• SLOB – The Silly Little Oracle Benchmark from Kevin Closson. The framework uses a simple and typical database operation to put some load on a system. Depending on the amount of memory you allocate to data cache, it would test ether CPU and memory (Logical IOs) or Storage (Physical IO). The tool uses index range scan and table blocks look-ups.
  • Experience: I did spend quite a bit of time running tests using the SLOB utility. There are quite several folks around the world who are using SLOB for testing Oracle systems' performance. From my experiences, I would say that it takes a bit of time to understand the things to be careful with during testing. However, as soon as you know what you are doing, it takes less time time to test and compare different systems' performance.
  • References:
    • SLOB 2
    • Introducing SLOB – The Silly Little Oracle Benchmark
    • LinkedIn SLOB Oracle Platform Testing
    • My SLOB IO Testing Index
• Oracle Database I/O calibration feature - Some people know it as DBMS_RESOURCE_MANAGER.CALIBRATE_IO. Strictly speaking, the procedure tests physical IO only. However, if you add DBMS_STATS.GATHER_SYSTEM_STATS that collaborates CPU speed with a certain skepticism, we can add these tools to this category of testing tools since both procedures work within an Oracle Instance. The biggest disadvantage here is the lack of details on how the tools run tests. Those are limited from a configuration perspective too. For example, you don't have a good control over what data files the IOs are issues against.
  • Experience: I have used the I/O calibration procedure many times. However the configuration limitations restricts its usability.
  • References: Oracle Documentation
    • DBMS_RESOURCE_MANAGER.CALIBRATE_IO
    • DBMS_STATS.GATHER_SYSTEM_STATS

Application Level testing tools (DB based)

• Hammerora - This is a free multi-database testing tool. Originally, it was created to run TPC-C and TPCH application workloads. Today, it supports application activity replay functionality (for Oracle Databases). I have used this tool to run TPC-C tests in the past. A typical use case would be to run a set of tests on two different platforms to get some general idea on the difference in performance characteristics. You may not get results as precise as with the previous group of tools. However, you may get a reasonably good idea on general performance comparison.
• Swingbench - from Dominic Giles (Oracle UK). This is a free tool, similar to Hammerora, that has a set of Supplied Benchmarks you may use to test the performance of your Oracle-based system. It allows you to build your own basic testing scripts. Originally, the tool was developed to test Oracle RAC based systems. Dominic's presentation gives a good overview of the tool. Several folks at Pythian have been using this tool. My team members configured and ran the tests recently to compare source (old Solaris) and target (VM based Linux) system.
• Simora - from James Morle and Scale Abilities. This tool mines Oracle SQL Trace files and Generated SQL to be executed to reproduce the load. Obviously, you can take and replay the application load on a copy of the system where the trace files have been generated. As a good use case, you may consider using it for a testing application across Database version upgrades. Like with Hammerora, such testing projects need careful planning (how and when to recover database to get the right data, how to synchronize data updates to make sure that no application constraints are violated, etc.). I would estimate such a testing project to last for anything starting from a week to 8 weeks, depending on the complexity and other parameters.
• Oracle Real Application Testing - This is an Enterprise Database option from Oracle that allows you to record a load on the source system and reply on the destination environment. I have tried this functionality several times in a test environment. It works well. However, RAT-based projects have challenges in common with the other two tools. It's worth mentioning that the product isn't free and needs additional licences. That being said, I have heard about a few clients who have successfully used the product in their testing/migration projects.

Application Level testing tools (Apps based)

Conclusions