Hitting Oracle with a Hammer

Recently, I had an opportunity to participate in a stress-testing exercise. By stress-testing, I mean simulating an expected peak load on the database and observing how the database performs. The objective is to make sure the particular hardware can handle the expected load and also to test where the physical limit of the machine lies if we keep increasing the number of concurrent users.

I searched for tools available out there. On forums, most people were talking about Hammerora, and although some people recommended high end tools for big bucks, I decided to try this free tool that promised to do exactly what I needed it to do. The open source Hammerora turned out to be excellent tool for the purpose of simulating a typical transactions load in any number of threads. It’s built with the Tcl scripting language and it can simulate a real workload with as many client user sessions as your system can handle.

So if you’re looking for an easy way to measure throughput performance of your database, keep reading.

Installation

Start by installing the ActiveState Tcl and oratcl package:

After unpacking, install ActiveTcl 8.5.5.0, by running the install.sh script found in the main directory.The interactive text installer will guide you through the rest of installation. You can perform the installation as an oracle user, and choose the install directory.

You may need to modify your path environment if you have another Tcl installation on the system:

The installation of Hammerora itself is pretty much self-explanatory, just allow execute on downloaded package and execute it. The whole process is best described on Hammerora’s installation web page, so I won’t go into more detail here.Note: The installation itself uses a GUI. It has a silent mode, but since the tool works best in GUI mode, you may need to enable vnc access to your server, or even choose to do the load-testing remotely from your desktop via TNS*Net (the network may be a bottleneck, however). The GUI has the significant advantage of allowing you to run individual sessions as threads, thus saving system resources.

After installation I faced a nasty problem. On my OEL 5.1, everything was working fine; on RHEL5, however, with a minimal installation, I struggled with annoying “Segmentation Fault” error when starting Hammerora.

[oracle@blackbox hammerora-2.2]$ ./hammerora.tcl
Segmentation fault

I tried tracing the issue with strace. Here’s the very tail of the trace:

Creating a script to simulate your transaction

Before I started to work with Hammerora, I was afraid I’ll have to learn Tcl to be able to use it. Fortunately this hasn’t been the case, and I was able to find my way through using “common scripting sense”.

What you actually need is a raw SQL trace file with binds, which the tool can then convert into a Tcl script and “replay” it on the database.

To obtain the tracefile, I created a logon trigger which set event 10046 for any session that interested me. For example:

Be careful—if there’s a load on the instance, at the moment you create the trigger it will immediately start generating trace files into USER_DUMP_DEST. If you want to better distinguish your files, you can also set a trace file identifier.

I enable the trigger and periodically checked the UDUMP tracefiles to see whether I had the desired sample. I searched all the generated files for interesting INSERT statements.

If you control your application, you can simply enable a 10046 event on your database session and perform just those steps you want to simulate. For setting trace in particular session you can use:

execute dbms_system.set_sql_trace_in_session(SID,SERIAL#,false);

Once I identified the right trace file, I loaded it into Hammerora and let the tool covert it to Tcl. To do that, just take the raw trace file and put it somewhere visible to Hammerora:

Click on FILE > Open > locate your *.trc file and open it.

The tracefile will get loaded into the main window. Now just click on the pencil-and-notepad icon that reads “Convert trace to TCL”.

The Tcl script is generated.

The next steps depend on what kind of load you want to generate. SELECT queries will likely get converted without any further work. On the other hand, UPDATE and INSERT queries might be a bit tricky.

I needed to simulate a complicated INSERT statement containing the DATE datatype. Having no prior knowledge of Tcl, I had to get creative.

The problem with the DATE datatype is that SQL trace does not record the DATE datatype bind variable value in readable form because it would lose its precision. Therefore, we’ll need to take care of all DATE columns by hand. You can probably define a date variable in Tcl and substitute it into right place. I did a workaround—I simply substituted SYSDATE for all the DATE bind variable values. This was fine for this case, as I didn’t have too care much about the actual value of the date.

If you see in your SQL trace file things like memory dumps instead of the bind variable value, it is most likely a DATE datatype and you’ll have to deal with it manually.

If you think your script is ready, you can test it by clicking the Computer icon to select “Test Tcl code”.

This will create a single virtual user and attempt to execute the Tcl script you have created. If you have an INSERT statement, it will actually insert a row, so make sure you are not running this in a production database.

Unfortunately, the errors Hammerora gives you during the script troubleshooting are not too informative. For example, the one below tells you that thread with the specified identifier returned an ORA error.

It does’t tell you at which line of your script the error occurred. So I found it useful to enable output in Virtual Users definition -> Check the “Show output” check box. That way, you can at least see where your script stopped, or put some debugging comments into your script.

Configuring Hammerora for your database

There are several use cases for Hammerora. The first is benchmarking your database with TPC-C and TPC-H industry standard benchmarks. Hammerora can actually create the official schema and populate it with data. Then you can benchmark your database and compare it with industry data.

In my case, I will be using a custom schema, so I will completely ignore the TPC benchmarking options in this post. Hammerora can be configured from the GUI or from its configuration file, called config.xml, which is located in the top your Hammerora install directory.

The configuration is fairly easy, requiring you to know only the database connect string and some credentials. Basically, there are two things you need to configure:

Database credentials. We have already updated the script with the connect string in the previous section.

Create virtual users. There are a few options for your virtual users. Click on Virtual Users > Vuser Options, and add values for:

Virtual users -> in other words, the number of sessions

User Delay -> how long to wait before next session is created

Repeat Delay -> how long to wait before one session runs another iteration of the script

Iterations -> how many times each session should execute the script

If you want to have a visual display of transaction counts, you will also need a system password.

Under TX Counter > TX Counter Options, you can set your system credentials and refresh rate. Hammerora will then inform you about actual transaction throughput-per-second.

You can also run multiple instances of hammerora in master/slave mode, and modify scripts on slaves to diversify the load. Everything is nicely described in Hammerora’s documentation.

Hammer time

Now we’re ready for the simulation. I strongly suggest that you start incrementally. For example, if you want to test 100 users with 1000 iterations, start with 1 user and 1 iteration. If that works, try 100 users with single iteration, and if that proves to be working go with your target goal.

A quick run through the prerequisites:

You have set your credentials in the Tcl script (main window).

You have tested the Tcl script and it completed with a green check for your single user.

You have setup Virtual Users options.

You have set the system password for TX counter.

You have disabled the logon trigger (if you created one) on your target database (because this could put your system under a lot of stress).

You have set your statspack/awr to the desired sampling period.

You have enough free space to handle INSERTs.

Now you’re three clicks away from running your test.

click on create Virtual Users – persons icon.

click on test Run Hammerora loadtest – the tools icon

Enable your TX throughput display by clicking the red pen button.

And now, watch your database go crazy:

I hope you have enjoyed this. I’d love to hear what other method of stress-testing you may have used, and if the test results turned out to be relevant to a real life environment.

Lukas,
I’ve been using Swingbench (http://www.dominicgiles.com/swingbench.php) with most of Oracle database deployment/migration. The “Hammerora” seems to be more flexible in terms of what it could do. I will try it out from your instructions.

Glad you find hammerora useful. Just a quick note that on Windows, Linux x86 and Linux x86-64, TCL, TK and Oratcl are already included in the self-extracting installer so you don’t need Activestate TCL or a separate build of Oratcl unless of course you really want to, though the screenshot shows you are using the included one :-) For the TPC-C based workload I have put some information here http://hammerora.sourceforge.net/tpc-c.htm. Also thanks for the comments on debugging, comments are always welcome I’ll see what can be done here to make it more helpful.

Wonderful tool! I am using the windows version. I have two problems! One as soon as i add more than 2 virtual users the application dies and second problem is the transaction display graph i can see on the screen only for 3 minutes! Can we not see the graph for 10 minutes testing? How can we save the graph?

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.