Are you using – or planning to use – Swingbench?
Swingbench currently provides four different benchmarks, the most well-known being Order Entry, a TPC-C like benchmark based on the Oracle-shipped oe schema.
In its default configuration, Order Entry’s SELECT-INSERT-DELETE ratio is 50% – 30% – 20% (as documented in http://dominicgiles.com/swingbench/swingbench22.pdf).
Order Entry is highly customizable, you can modify the composing transactions’ weights and activate up to three more datawarehouse-like transactions that are not “on” by default. Still, depending on what you need to test, you might find that even by carefully choosing your transactions you cannot generate the kind of activity required.
In that case, it’s possible to write your own transactions.
Adapting the build is easy and well documented in the FAQs. What I’d like to do here is give a very basic code example, stripped down to the requirements of integration into the Swingbench framework and performing a basic JDBC action.
I’ll break the code up in pieces first; a complete listing will be found at the end of the post.
A basic Swingbench transaction
Swingbench transactions are written in Java. There are two types of transactions, found in two different subdirectories – those which simply use Java to call, and retrieve the results from, stored procedures and those which use JDBC to perform the distinct steps of parsing, executing and fetching.
Here I assume we need more control over single parts of this process (which is why we’re writing our own code in the first place, instead of varying, e.g., the size of the data), so we’re going to write a JDBC-based transaction.
First, the package declaration (surprisingly called mytransactions here) and the imports. Importing OracleConnection is only needed if you’d like to instrument the code for tracing. (Aside: using EndToEndMetrics is deprecated as of 12c, but to use DMS metrics – the recommended alternative – a special jar file, ojdbc_dms.jar, is required).
package com.dom.benchmarking.swingbench.mytransactions; // the Swingbench framework import com.dom.benchmarking.swingbench.kernel.DatabaseTransaction; import com.dom.benchmarking.swingbench.event.JdbcTaskEvent; import com.dom.benchmarking.swingbench.kernel.SwingBenchException; import com.dom.benchmarking.swingbench.kernel.SwingBenchTask; // java classes needed by either Swingbench or our own code import java.util.Map; import java.util.Random; import java.util.logging.Logger; // JDBC import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle JDBC (optional, needed by setEndToEndMetrics) import oracle.jdbc.OracleConnection;
Next, the class declaration and some instance variables
// Extend DatabaseTransaction to use the Swingbench framework
public class FetchLotsOfData extends DatabaseTransaction {
// add our own logging output to Swingbench's debug log
private static final Logger logger = Logger.getLogger(FetchLotsOfData.class.getName());
// Just one query here, I can put it outside of any methods
private String query = "select * from mining_info where customer_id between ? and ?";
private Random random = new Random();
All classes you write have to extend DatabaseTransaction to use the Swingbench framework. The JDBC transactions that come with Swingbench all extend OrderEntryProcess, a common superclass that provides much shared functionality. So if your test is going to run against the Swingbench-provided tables, and possibly using similar logic, check if you can make use of the functionality provided there. (In that case, in might make sense to put your class in the same package, instead of creating a separate one, or else you might have to change some instance variables to protected in OrderEntryProcess.java).
Here we’re not need to use any common helper methods and so, it is sufficient to extend DatabaseTransaction.
Now, every transaction has to implement three methods, init, execute, and close. Of these, you might just need execute, which is where you place the queries you want to be benchmarked.
The init method can be used for setup activities, e.g., getting data from the database (as in BrowseProducts.java) or reading initialisation files (as in NewCustomerProcess.java). The close method is not used in any of the provided transactions.
So here is our execute method, with comments. The JDBC action itself is kept deliberately simple here, as we’re focusing on the framework integration.
public void execute(Map params) throws SwingBenchException {
// get a connection; how Swingbench does this, is transparent for us
Connection conn = (Connection)params.get(SwingBenchTask.JDBC_CONNECTION);
// initialise the counters/metrics
initJdbcTask();
// setting the module so we can trace this on the server (optional)
String metrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "FetchLotsOfData";
// start measuring
long executeStart = System.nanoTime();
PreparedStatement stmt = null;
ResultSet rset = null;
try {
((OracleConnection) conn).setEndToEndMetrics(metrics, (short) 0);
logger.fine("Preparing query: " + query);
stmt = conn.prepareStatement(query);
int low = random.nextInt(900);
int high = low + 100;
stmt.setInt(1, low);
stmt.setInt(2, high);
rset = stmt.executeQuery();
String s;
while (rset.next ()) {
s = "Customer " + rset.getInt(1) + "(run " + rset.getInt(2) + "): " + "";
// do something with s if you want
}
rset.close();
// tell Swingbench we performed one select
addSelectStatements(1);
logger.fine("Result set closed.");
stmt.close();
logger.fine("Statement closed.");
// tell Swingbench our execution time, and that execution was successful
processTransactionEvent(new JdbcTaskEvent(this, getId(), (System.nanoTime() - executeStart), true, getInfoArray()));
} catch (SQLException se) {
se.printStackTrace();
// tell Swingbench our execution time, and that we failed
processTransactionEvent(new JdbcTaskEvent(this, getId(), (System.nanoTime() - executeStart), false, getInfoArray()));
// re-throw the exception, Swingbench will display it - and the nested SQLException - on standard out
throw new SwingBenchException(se.getMessage());
} finally {
// these two convenience methods are provided by Swingbench, they just catch the exceptions possibly thrown by closing
// the statement / result set
hardClose(rset);
hardClose(stmt);
}
}
What’s important here is the exception handling. JDBC methods throw SQLException; this has to be handled while still keeping track of the stats.
In case the transaction was successful, we just call processTransactionEvent and proceed with the benchmark; otherwise we catch the exception, do the bookkeeping for a failed transaction, and rethrow the exception for Swingbench to handle as it sees fit (it will be displayed on the command line).
In both cases, we make sure the resources (ResultSet and Statement) are closed.
So that’s it – for you to implement more complex transactions :-). Here again is the complete class:
package com.dom.benchmarking.swingbench.mytransactions;
// the Swingbench framework
import com.dom.benchmarking.swingbench.kernel.DatabaseTransaction;
import com.dom.benchmarking.swingbench.event.JdbcTaskEvent;
import com.dom.benchmarking.swingbench.kernel.SwingBenchException;
import com.dom.benchmarking.swingbench.kernel.SwingBenchTask;
// java classes needed by either Swingbench or our own code
import java.util.Map;
import java.util.Random;
import java.util.logging.Logger;
// JDBC
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// Oracle JDBC (optional, needed by setEndToEndMetrics)
import oracle.jdbc.OracleConnection;
// Extend DatabaseTransaction to use the Swingbench framework
public class FetchLotsOfData extends DatabaseTransaction {
// add our own logging output to Swingbench's debug log
private static final Logger logger = Logger.getLogger(FetchLotsOfData.class.getName());
// Just one query here, I can put it outside of any methods
private String query = "select * from mining_info where customer_id between ? and ?";
private Random random = new Random();
public void execute(Map params) throws SwingBenchException {
// get a connection; how Swingbench does this, is transparent for us
Connection conn = (Connection)params.get(SwingBenchTask.JDBC_CONNECTION);
// initialise the counters/metrics
initJdbcTask();
// setting the module so we can trace this on the server (optional)
String metrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "FetchLotsOfData";
// start measuring
long executeStart = System.nanoTime();
PreparedStatement stmt = null;
ResultSet rset = null;
try {
((OracleConnection) conn).setEndToEndMetrics(metrics, (short) 0);
logger.fine("Preparing query: " + query);
stmt = conn.prepareStatement(query);
int low = random.nextInt(900);
int high = low + 100;
stmt.setInt(1, low);
stmt.setInt(2, high);
rset = stmt.executeQuery();
String s;
while (rset.next ()) {
s = "Customer " + rset.getInt(1) + "(run " + rset.getInt(2) + "): " + "";
// do something with s if you want
}
rset.close();
// tell Swingbench we performed one select
addSelectStatements(1);
logger.fine("Result set closed.");
stmt.close();
logger.fine("Statement closed.");
// tell Swingbench our execution time, and that execution was successful
processTransactionEvent(new JdbcTaskEvent(this, getId(), (System.nanoTime() - executeStart), true, getInfoArray()));
} catch (SQLException se) {
se.printStackTrace();
// tell Swingbench our execution time, and that we failed
processTransactionEvent(new JdbcTaskEvent(this, getId(), (System.nanoTime() - executeStart), false, getInfoArray()));
// re-throw the exception, Swingbench will display it - and the nested SQLException - on standard out
throw new SwingBenchException(se.getMessage());
} finally {
// these two convenience methods are provided by Swingbench, they just catch the exceptions possibly thrown by closing
// the statement / result set
hardClose(rset);
hardClose(stmt);
}
}
recurrent null 