Swingbench Tips

Swingbench

Swingbench is a load generation benchmark utility created by an Oracle employee, Dominic Giles. The tool is not an official Oracle product. As such, you cannot obtain support for it. But Swingbench is a nifty benchmark utility that many database administrators may find very useful. Swingbench can be found for download by pointing a web browser to http://dominicgiles.com/swingbench.html and clicking on the download link. The download is a simple zip file and can be unzipped with normal OS utilities. Swingbench is a Java program so it can be run on most any platform that supports Java. All of the Swingbench utilities can be invoked with command line options that facilitate scripting for multiple benchmark runs.

Order Entry Benchmark

The best way to learn Swingbench is to jump in and execute one of its benchmark runs. Swingbench includes an Order Entry application for one of its tests, simulating users placing orders for products not that much different than many shopping web sites prevalent on the Internet today. Before the benchmark can be taken, the database administrator needs to create some schema objects. Swingbench includes the oewizard utility in the bin directory to create the tables, indexes, and stored procedures that will be used.  Upon launching the Order Entry installation wizard, the database administrator should see a screen similar to the following.

After pressing the Next button, the benchmark version is defined. Unless there is a need to deviate, the best practice would be to use the latest and greatest version.

After pressing the Next button, there are choices to either create or to drop the Order Entry schema. Since this is the first run, the only sensible option is to create the schema.

After pressing the Next button, the database details can be provided. Since this is an Oracle 12c RAC database, the connection string will use the SCAN VIP and a database service name. A user with the dba role will need to be provided.

After pressing the Next button, the benchmark schema's username and password can be provided. In the screenshots below, the default values for the Swingbench Order Entry (SOE) schema are used. The database administrator should provide a tablespace to hold the schema's segments.

After pressing the Next button, the database administrator can specify certain options to be used including compression, partitioning, and the tablespace type.

After pressing the Next button, the database administrator can define the Swingbench sizing details. As the screen says, a smaller size will lead to a more CPU-intensive test while a large size will lead to a more I/O-intensive test. No matter which size is chosen, the bottom of the screen will show the required data and temporary tablespace sizes. It is important to note that no matter which size is chosen for a baseline test, the same size should be used for the post-change benchmark run so that the test size does not skew the results.

After pressing the Next button, the database administrator can define the degree of parallelism used to create indexes and compute statistics when setting up the schema.

At this point, the database administrator can press the Finish button to start the wizard. A progress screen will be displayed, similar to the following.

After a short period of time, the wizard will display the screen below that signifies the end of the schema creation.

At this point, the Swingbench tool is ready for its first run. To start the utility, run the swingbench executable from the bin directory. The top left section of the Swingbench screen contains the connection details. The Order Entry schema username and password is entered. In the Connection String field, enter ?//scan-vip//service_name?. The SCAN VIP is only available in Oracle 11gR2 and higher. With the SCAN VIP, Swingbench will be able to connect to all instances running the named service. If the database administrator is testing an earlier Oracle version, then the Swingbench cluster coordinator may need to be used, a feature that will be discussed later in this chapter. An alternative to the SCAN VIP and the cluster coordinator is to use the Oracle OCI driver and an alias in the tnsnames.ora configuration file that defines the connection to an Oracle RAC database. 

Below the connection details is a section to automate AWR snapshots. It is always a good idea to collect snapshots at the start and end. With AWR snapshots, the database administrator can take a deep dive in to the benchmark performance. Even though the screen says for ?10g/11g only?, the snapshots work in Oracle 12c as well. The database administrator will need to enter a user with the dba role in this section.

Continuing down the left side of the screen is the section on defining the load parameters. Here is where the database administrator denotes the number of users in the test and the benchmark runtime.

The example above will simulate 40 user with a runtime of 20 minutes. The test is now set up and ready to run. To start the test, press the green play button near the top left of the screen.

When the benchmark test is running, the Overview Chart provides nice information on the overall progress. This chart includes the number of transactions per minute and response time.

Above the Overview Chart is a pull down menu to select the Chart Type. The database administrator can choose the Transactions Per Minute (TPM) chart, a sample of which can be seen below.

Another option is a chart that shows the Data Manipulation Language Operations Per Minute. This chart type is shown in the screen shot below.

At the completion of the load test, the Output tab contains an XML document with the test results.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<Results xmlns="http://www.dominicgiles.com/swingbench">

    <Overview>

        <BenchmarkName>"Order Entry (PLSQL) V2"</BenchmarkName>

        <Comment>""</Comment>

        <TimeOfRun>Sep 28, 2015 11:37:40 PM</TimeOfRun>

        <TotalRunTime>0:20:46</TotalRunTime>

        <TotalLogonTime>0:00:02</TotalLogonTime>

        <TotalCompletedTransactions>3244</TotalCompletedTransactions>

        <TotalFailedTransactions>0</TotalFailedTransactions>

        <AverageTransactionsPerSecond>2.6</AverageTransactionsPerSecond>

        <MaximumTransactionRate>181</MaximumTransactionRate>

    </Overview>

The XML file concludes with wait events and database statistics for the load test. This information can be useful when comparing a post-change benchmark to the baseline results. The database administrator can get an idea of exactly where the change affected the outcome.

At this point, the Order Entry benchmark test from Swingbench is complete. The reader should now have a good idea on how to use Swingbench to perform simple benchmark testing for their system. Just remember that for Oracle RAC databases, use the SCAN VIP when connecting to the service so that the load is spread among all instances that support the service.

Also see Swingbench Calling Circle Tips