Generate TPC Workloads For Oracle Databases Using Benchmark Factory

November 29, 2010

Even though Oracle 11g Release 2 (11gR2) makes it easier than ever, to isolate and identify potential root causes of poor I/O performance, most Oracle DBAs have found that tuning the I/O performance of an Oracle 11gR2 database is still just as much art as science. This article – the fifth in this series – explains how to use Quest’s Benchmark Factory tool to construct and execute TPC-E and TPC-H workloads against a previously-loaded Oracle database, as well as how to capture the necessary database and system performance metrics to isolate the sources of I/O performance bottlenecks within the database’s underlying I/O subsystem.

The prior article in this series, Oracle Database I/O Performance Tuning: Generating Intra-Database Workloads, demonstrated how Quest Software’s tool, Benchmark Factory, made it extremely simple for any Oracle DBA to:

• Choose appropriate database schema(s) for realistic testing
• Create example TPC-E and TPC-H database schemas
• Perform initial loading of these schemas

Now that sufficient data for the TPC-E and TPC-H schemas have been loaded into my test database, I’ll shift my focus in this article to actually generating a reproducible application workload so that I can evaluate my Oracle 11gR2 database’s actual I/O performance versus its expected potential response time and throughput capabilities. I’ll demonstrate in this article how to:

• Generate realistic database application workloads using Benchmark Factory
• Capture appropriate metrics from my Oracle database and the operating system to quantify I/O response times and throughput

Preparing the TPC-E Benchmark Workload

As I’ve mentioned in prior discussions about TPC benchmarks, the TPC-E performance benchmark is still relatively new. Generating a TPC-E workload involves a much more realistic and complex set of transactions than for its predecessor benchmark, TPC-C. Since there are four times as many tables (33, versus only eight for TPC-C) against which any realistic workload would be generated, writing workload-generating code is decidedly non-trivial. In addition, the TPC-E benchmark standards are rather specific on where the workload generation code itself should reside; therefore, unlike the TPC-C benchmark, the transactions that comprise a TPC-E workload are not generated via PL/SQL stored procedures. The good news is that Benchmark Factory makes it extremely simple to construct and generate a TPC-E application workload. Creating the new workload involves the same initial steps in the immediately prior article in this series in which I used Benchmark Factory to create and load the TPC-E schema.

To demonstrate, I’ll first make a copy of the original TPC-E schema loading script, and then I’ll edit that script into a new application workload generation script. As Figure 5.1.1 through Figure 5.1.7 below show, I selected the copy of the original script and clicked the right mouse button to choose the New Load Scenario(s) option to insert a new set of TPC-E load generation activities. My final step (Figure 5.1.8) is to remove the creation and deletion of the TPC-E schema objects from the copied script, leaving just the TPC-E application workload generation step (Figure 5.1.8). Note that I’ve also renamed the script to reflect its future application workload: TPC-E 50 Users Test.

Figure 5.1.1. Benchmark Factory: Editing a Copied Script.

Figure 5.1.2. Benchmark Factory: Adding a New Workload.

Figure 5.1.3. Benchmark Factory: Choosing an Industry Standard Benchmark As Workload.

Figure 5.1.4. Benchmark Factory: Choosing the TPC-E Standard Benchmark.

Figure 5.1.5. Benchmark Factory: Assigning the TPC-E Profile.

Figure 5.1.6. Benchmark Factory: Picking the Benchmark Scale.

Figure 5.1.7. Benchmark Factory: TPC-E Workload Script Completion.

Figure 5.1.8. Benchmark Factory: Removing Create Objects / Delete Objects Actions.

Figure 5.1.9. Benchmark Factory: The Finished Script.

Augmenting the TPC-E Benchmark Workload

To guarantee that I’ll generate a sufficiently extreme workload during a relatively short duration for the TPC-E benchmark, I’ll make a few adjustments to the finished script:

• First, I’ll limit the time that Benchmark Factory will perform pre-sampling and sampling activities to three and 12 minutes, respectively (see Figure 5.1.10.1). This will allow me to run shorter (yet not insignificant!) simulated OLTP workloads against my test database.
• I’ll also increase the number of users that will be generating the workload from its default of one (1) to fifty (50) (see Figure 5.1.10.2).
• Finally, I’ll make sure that AWR snapshots will be generated at the beginning and end of each workload generation cycle by adding an invocation of DBMS_WORKLOAD_REPOSITORY. CREATE_SNAPSHOT at the start and end of each run (see Figure 5.1.10.3). The MS-DOS batch files and SQL statements I added to the appropriate Oracle net client directory to generate the AWR snapshots are shown in Listing 5.1.

Figure 5.1.10.1. Picking the Pre-Sampling and Sampling Time Periods.

Figure 5.1.10.2. Benchmark Factory: Specifying the Number of Users.

Figure 5.1.10.3. Benchmark Factory: Adding AWR Snapshot Generation.

Preparing the TPC-H Benchmark Workload

Benchmark Factory also makes short work of constructing a TPC-H workload. I followed the same procedures I used to create the TPC-E workload to:

• Copy the existing TPC-H schema loading script to a new script
• Add a new TPC-H workload to the copied script, choosing TPC-H Benchmark as the benchmark workload
• Select a corresponding profile (TPCH) under which to execute the benchmark
• Remove the TPC-H schema loading step from the new workload generation script
• Rename the new TPC-H workload generation script as TPC-H Minimal Workload

Augmenting the TPC-H Benchmark Workload

After I successfully created the new TPC-H workload script, I modified it slightly to include only the first ten TPC-H queries. I also restricted the workload to execute within a fixed 15-minute time period by specifying the Execute by time option. Finally, I added the invocation of the beginning and ending AWR snapshot generation MS-DOS batch files into the workload script. The ultimate version of the TPC-H workload generation script is shown in Figure 5.2.1 below.

Figure 5.2.1. Benchmark Factory: Final TPC-H Load Generation Script.

Generating a TPC-E Benchmark Workload

Benchmark Factory also makes short work of generating the TPC-E and TPC-H workloads. Once both workload generation scripts were complete, I submitted each workload against the database with just one mouse click.

The TPC-E 50 Users Test first performs a short pre-sampling set of transactions to “ramp up” the database to a stable state during which more accurate measurement can be taken; it then performs a longer series of transactions during which the actual sample measurement is captured at “steady state.” I’ve shown both the pre-sampling and sampling states of the generated workload Figure 5.3.1 and Figure 5.3.2, respectively.

Figure 5.3.1. Benchmark Factory: Monitoring the TPC-E Workload Execution’s Pre-Sampling Phase.

Figure 5.3.2. Benchmark Factory: Monitoring the TPC-E Workload Execution’s Sampling Phase.

Once the TPC-E workload generation was complete, it also recorded its results within Benchmark Factory’s message log:

TPC-E 50-User Test
Job Started 
Started test TPC-E Transaction Mix
Executing PreIteration file.
Test completed successfully.
** Job finished.
Total Time to perform the job:
  Start Time: 11/17/2010 8:28:51 PM
  End Time  : 11/17/2010 8:51:46 PM
  Elapsed   : 0 Days 00:22:55.069

One word of warning: I found that it was necessary to create one missing PL/SQL type to enable Benchmark Factory to execute the TPC-E workload properly. I’ve reproduced the code to drop and recreate the TYPE below:

DROP TYPE tpce.e_tradelist;
CREATE TYPE tpce.e_tradelist IS TABLE OF NUMBER;

Generating a TPC-H Benchmark Workload

I then submitted the TPC-H Minimal Workload load generation script for execution and monitored its progress until it completed in just a bit longer than the 15 minutes allotted to the workload generation. Figure 5.4.1 shows Benchmark Factory’s progress reporting window as the TPC-H workload neared completion:

Figure 5.4.1. Benchmark Factory: Monitoring the TPC-H Workload Execution.

Here are the results that are recorded into the completed TPC-H workload’s log file. Note that the scripts that generate a manual AWR snapshot at the beginning and end of the workload also executed successfully:

TPC-H Minimal Workload
Job Started 
Started test Execute 'C:\oraclient\10201\BeginningAWRSnapshot.bat'
Executing 'C:\oraclient\10201\BeginningAWRSnapshot.bat'
Test completed successfully.
Started test TPC-H Power Test
Test completed successfully.
Started test Execute 'C:\oraclient\10201\EndingAWRSnapshot.bat'
Executing 'C:\oraclient\10201\EndingAWRSnapshot.bat'
Test completed successfully.
** Job finished.
Total Time to perform the job:
  Start Time: 11/17/2010 7:52:00 PM
  End Time  : 11/17/2010 8:07:45 PM
  Elapsed   : 0 Days 00:15:45.140

Next Steps

Now that I’ve at last successfully generated both TPC-H and TPC-E workloads, it’s time to turn my attention to how well my database’s I/O infrastructure is actually performing … and that means it’s time to pore over the resulting performance metrics that Oracle 11gR2 is quite excellent at capturing. The next article is this series will focus on how to:

• Evaluate AWR reports for indicators of any I/O performance bottlenecks
• Formulate initial hypotheses on how well my database’s storage infrastructure is responding to different application workloads

References and Additional Reading

Before you proceed to experiment with any of these 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