Oracle Database Load Testing Tools – Overview

Jun 13, 2013 / By Yury Velikanov

Tags: , , ,

At Pythian’s internal forum, someone just asked the following question: “The team here is evaluating DB load testing tools (Hammerdb, Orion, SLOB, and Swingbench) and was wondering about our experience in using different tools and what is our opinion?” I have experience using some of the tools mentioned. Therefore, decided to answer using this public blog post, as it could be useful for you too and easier for me to reference in the future. :)

The right tool for the right job

Most of the load testing tools could be grouped in the following categories:

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

Each category addresses a wider range of business testing problems/tasks than the one preceding it. However, each will require slightly more resources and are slightly more challenging than the last. You should clearly define your testing goals before starting to consider the right tool for the job. I would suggest that you consider tolls from the first category first rather than move to the next category if the tools in the first category don’t satisfy your business requirements. I hope that the overview will give you enough input to start putting your testing plans together.

IO subsystem testing tools

Those tools are easy to configure and run. However, those are doing nothing but sending “simple/meaningless/no real data” IO requests to a storage device and measure response times. Most tools have the ability to run read-only tests, read/write tests, random IO tests, sequential IO test, increase readers/writers count, warm up storage’s cache, and do a mixture of the tests mentioned.

Pros:

  • 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

Cons:

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

Representatives:

  • 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:
  • 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:
  • 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: http://www.iometer.org/

RDBMS Level testing tools

This group of tools works from within an Oracle Instance. Therefore, some people (I bet if they read this post they know exactly who I am referencing  to) say these groups of tools could reproduce the RDBMS IO calls much more closely than the fist group. I may not agree completely that the first group doesn’t imitate RDBMS calls close enough, but there are other benefits that this group of tools provides. Some of them could be used to test other system resources’ performance like CPU and memory.

Pros:

  • 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)

Cons:

  • 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.

Representatives:

  • 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:
  • 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.

Application Level testing tools (DB based)

Before going any further, I must mention that this category includes tools that require more time to setup, test and get comparable results. I have been part of several projects that strove to mimic the application behavior. Depending on a tool, application complexity, and results, archiving a project from this category may take anything from ~20 hours to several months.

For the interest of keeping this blog post reasonably short, I’ll mention some of the tools from this category below with a few comments. If you consider using one of the tools from this category, I would encourage you to run a pre-study, which can take from several hours to several days.

  • 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)

Testing tools in this category mimics end users’ behavior as users would work with the system from their workstations. That means that tools like HP LoadRunner interact with application servers, using a variety of protocols (e.g. HTTP, HTTPS, Oracle Forms, etc.) to test all components of the system at the same time. Needless to say that in order to do it, a test scenario need to be scripted and test data prepared, tested, and maintained based on the changes in the application side. I was personally part of such a project. It lasted several months. However, we have archived good results and spotted several critical inefficiencies in the custom application code.

Conclusions

As I mentioned at the beginning of this post, you need to find the right testing tool for the task you have.  I hope that this overview helped you get some idea on options available and resources you may lineup for your testing activities. I would appreciate if you shared your experience with any of the tools mentioned or, even better, mention some good tools that I haven’t listed in this blog post. Please share in the comments section below!

Yury

Connect with me on LinkedIn!

 


Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Oracle expertise. or find more Oracle Database-related blog posts.

3 Responses to “Oracle Database Load Testing Tools – Overview”

  • Kiran says:

    Thanks for providing many testing tools for an Apps DBA. Well described and segregated with pros, and Cons..

    It would take hours to get such idea from google/ wikepedia.. Thanks a lot for sharing and keeping all in single place.

    ~kiran.

  • Kevin says:

    One tool that isn’t mentioned here is Benchmark Factory for Databases from Dell Software(formerly Quest Software). It’s a part of the Toad DBA and Developer Suites and available standalone. It includes industry standard benchmark testing (i.e. TPC) and capture and replay of workloads.

  • Silviu says:

    Thanks a lot for the nice presentation of these categories.Yet I would like to ask you for some more details regarding “Application Level testing tools (Apps based)” since it is not very clear to me. Thanks a lot !

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>