Generate TPC Workloads For Oracle Databases Using Benchmark Factory
November 29, 2010
Even though Oracle 11g Release 2 makes it easy to identify potential root causes of poor I/O performance, most DBAs have found that tuning the I/O performance of an Oracle database is still just as much art as science. Learn how to use Quest’s Benchmark Factory to construct and execute TPC-E and TPC-H workloads against an Oracle database, and how isolate the sources of I/O performance bottlenecks.
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:
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:
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.
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:
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:
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.
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.
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:
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
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:
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