Tune the App, Not the SQL – DBA Sherlock’s Adventures in Hibernate/jOOQ Land

Last weekend at Trivadis Tech Event, in addition to my session on Oracle 12c Client High Availability for Java (Application Continuity, Transaction Guard … choose your degrees of freedom”), I gave a quite different talk called Tune the App, Not the SQL – DBA Sherlock’s Adventures in Hibernate/jOOQ Land”.

In a nutshell, what is it all about? Well, if you’re a DBA, you might sometimes (or often, or seldom – depends on your job ;-)) do what we call “SQL Tuning”. It might look like this: You get a call that the application “is slow”. Fortunately, you’re able to narrow that down quickly, and it’s just “the click on that button” that is slow. Fortunately, too, the application is well instrumented, and getting traces for specific tasks is not a problem. So you get a SQL trace file, run tkprof on it, and obtain the problematic SQLs in order of elapsed time. Then you analyse the “Top SQL”. You may find it should be rewritten, or that it would benefit from an index. Perhaps adding additional data structures might help, such as creating a materialized view. Perhaps you’re unlucky and just an ugly hint will fix it.

So you tell the developers “please rewrite that, like this”, or you create that index. And after, it’s waiting and hoping that performance will improve – noticeably, that is. Because tuning that single statement might – if you’re unlucky – not make that much of a difference.

There is, however, another approach to tuning application performance (always talking about the DB related part of it here, of course), and that has to do with the application logic and not single statements. There is an excellent demonstration of this in Stéphane Faroults very recommendable book “Refactoring SQL applications”, in fact, this is right at the beginning of the book and something that immediately “draws you in” (it was like that for me :-)).

Application logic affects what, when, and how much data is fetched. Of course, there are many aspects to this – for example, it just may not be possible to replace two DB calls by one simply because another service has to be queried in between. Also, there will have to be a tradeoff between performance and readability/maintainability of the code. But often there will be a choice. And you will see in the presentation it is not always that combining two queries into one results in better performance.

In fact, it all depends. So the first conclusion is the ubiquitous “don’t just assume, but test”.

There is another aspect to this, though. While Stéphane Faroult, in his test case, uses plain JDBC, I am using – and comparing, in a way – two commonly used frameworks: Hibernate and jOOQ. (For an “interdisciplinary introduction” to Hibernate, see my talk from previous Tech Event, Object Relational Mapping Tools – let’s talk to each other!. Quite a contrast, jOOQ is a lightweight, elegant wrapper framework providing type safety and near-100% control over the generated SQL.)

Now while for a DBA looking at a raw trace file, it will always be a challenge to “reverse engineer” application logic from the SQL sent to the database (even if plain JDBC or jOOQ are being used), the difficulties rise to a new dimension with Hibernate :-). In the talk, I am showing a DBA – who doesn’t need to be convinced any more about the importance of application logic – trying to make sense of the flow of statements in a trace file: the only information he’s got. And it turns out to be very difficult …

But as he’s not just “some DBA”, but Sherlock, he of course has his informants and doesn’t remain stuck with his DB-only view of the world – which brings me to one of my ceterum censeo’s, which is “DBAs and developers, let’s talk to each other” :-).

The slides are here.

Advertisements

Oracle 12c Transaction Guard, Application Continuity … choose your degrees of freedom!

Last Saturday at Trivadis Tech Event, I presented on Application Continuity, Transaction Guard … choose your degrees of freedom”.
What a strange title, you will be asking! What do Application Continuity (AC) and Transaction Guard (TG) have to do with freedom 😉
And why talk about Transaction Guard, isn’t that just a technology used by AC? Surely it’s not something you would care to implement – if the language you’re working with is Java?

Well, there are – in addition to the extensive, but not really easy to digest Oracle documentation – some very helpful resources (blog articles, presentations) on the net, but these mostly focus on infrastructural aspects: How does AC work with RAC, with Data Guard, with RAC One Node? What does the DBA have to do to enable AC (or TG)?

The demos are mostly designed to demonstrate that AC “just works”, in different environments. As the client code is not in focus, often the easiest way of implementing AC on the client is chosen: using Oracle Universal Connection Pool (UCP). (I’ve encountered one notable exception, which is Laurent Leturgez’ very interesting post on AC resource usage.)

However, in real life, much will depend on the developer teams: Are they comfortable with making the necessary modifications? Do they trust the technology? What if they, for whatever reasons, use their own connection pool, and so can’t use UCP?

In this presentation, the focus is on the developers’ part. How the code looks / might look, and what pitfalls there are – what errors you might see if you don’t do it right, and what they mean. This is for both AC and TG.

Let’s assume, however, that you’re VERY impatient and just want to know what the “main thing” is here 😉 … I’d say it’s about TG.

As of today, I’m not aware of any Java code on the web implementing TG that is NOT from Oracle documentation / whitepapers. Of course, as the topic is not easy and probably a bit “scary”, we are thankful for the example code Oracle provides. In Transaction Guard with Oracle Database 12c Oracle provide the following code example, which shows how it works:


Connection jdbcConnection = getConnection();
boolean isJobDone = false;
while(!isJobDone) {
    try {
        updateEmployeeSalaries(jdbcConnection);
        isJobDone = true;
    } catch (SQLRecoverableException recoverableException) {
    try {
        jdbcConnection.close();
    } catch (Exception ex) {}
    }
    Connection newJDBCConnection = getConnection();
    LogicalTransactionId ltxid = ((OracleConnection)jdbcConnection).getLogicalTransactionId();
    isJobDone = getTransactionOutcome(newJDBCConnection, ltxid);
    jdbcConnection = newJDBCConnection;
  }
}

Basically we have a loop around our transaction. Normally that loop is left immediately. But in case we receive a recoverable exception, we get a new connection, obtain the Logical Transaction ID from the dead connection, and ask the database server for the transaction outcome for that LTXID. If the commit went through successfully, we’re done, otherwise we resubmit our transaction.

Now while this demonstrates how to do it, we do not want to clutter our code like this everywhere, do we? And fortunately, with Java 8, we don’t have to!

In Java 8, we have Functional Interfaces. Formally, a functional interface is an interface with exactly one explicitly declared abstract method. This abstract method can be implemented directly inline, using a lambda expression. That is, the lambda expression IS an implementation of the functional interface.

This allows us to separate transaction handling from the business methods, and get rid of the above loops. How?

On the one hand, this is how one business method could look:


private void updateSalaries(Connection conn) throws SQLException {
    String query = "select empno, sal from tg.emp";
    PreparedStatement stmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
       int oldsal = rs.getInt("sal");
       int newsal = calculateNewValue(oldsal);
       rs.updateInt("sal", newsal);
       rs.updateRow();
    }
    rs.close();
    stmt.close();
}

On the other, here we have a functional interface:

@FunctionalInterface
public interface Transaction {
    public void execute(Connection connection) throws SQLException;
}

One implementation of a transaction can be a lambda expression that wraps the updateSalaries business method, like this: (conn -> updateSalaries(conn)):

TGTransactionProcessor tp = new TGTransactionProcessor(url, appUser, appPasswd);
    if (tp.process(conn -> updateSalaries(conn))) {
        logger.fine("Salaries updated.");
    }


public boolean process(Transaction transaction) throws SQLException {

    boolean done = false;
    int tries = 0;
    Connection conn = getConnection();

    while (!done && tries <= MAXRETRIES) {

        try {

            transaction.execute(conn);
            conn.commit();
            conn.close();
            done = true;

        } catch (SQLRecoverableException e) {

            try {
                conn.close();
            } catch (Exception ex) {
           }
            LogicalTransactionId ltxid = ((OracleConnection) conn).getLogicalTransactionId();
            Connection newconn = getConnection();
            setModule(newconn, moduleName);
            done = isLTxIdCommitted(ltxid, newconn);
            if (done) {
                logger.info("Failed transaction had already been committed.");
           } else {
                logger.info("Replay of transaction neccessary.");
               tries++;
               conn = newconn;
           }

        }
    }
    return true;
}

So with Java 8 Functional Interfaces, we have an elegant way to separate business logic and transaction handling in general, and implement TG in Java, specifically.
So that’s the end of the “highlights of” section, for more information just have a look at the slides :-).

Raising the Fetch Size, Good or Bad? Memory Management in Oracle JDBC 12c

Yesterday at DOAG I presented on “Raising the Fetch Size, Good or Bad? Memory Management in Oracle JDBC 12c”.
Again I’m too lazy to write a separate post (okay, let’s phrase this positively: I don’t like to be redundant ;-)) so I’ll just say what’s not in the slides …

Firstly, it all started with a story, a story about an Oracle DBA for whom, like for most any Oracle DBA I guess, fetch size was a well-known tuning knob. Most any Oracle DBA, I think, will have read some blog post on this topic, or seen a demo showing that as we raise the fetch size, throughput goes up. In fact, sometimes you can see it going up very much!

And so in one company, there was a DBA who decided to do systematic experiments, in order to find out the optimal fetch size for the in-house application. As it turned out, the optimal value for that application lay around 80. So the DBA told the developers, it was integrated into the code, and released to pre-prod. All was fine or at least nobody said the opposite.

Then inevitably, there came the day of the production release, and all wasn’t fine any more … The application server kept crashing with out of memory errors, and they had to do an emergence release, and no-one never ever wanted to talk about this any more.
What had happened? How could a value of 80 (we’re not speaking 10000 here) have such a catastrophic effect?

So that’s for the teaser… now there’s an Oracle Whitepaper where they describe how memory management in the JDBC driver was fundamentally changed between 11g and 12c. Will it be enough to read this paper, for people who have experienced those OOM errors, to be willing to try it again?
My idea was to perform an experiment and see for myself. And that’s what the talk was about, – how I set up the experiment and what was the outcome ;-). Here are the slides.

Object Relational Mapping Tools – let’s talk to each other

Following up with the follow up of Trivadis Tech Event :-).
In addition to the five minute talk about Linux Tracing for Oracle, I presented on a quite different subject: “Object Relational Mapping Tools – let’s talk to each other”.
My original intention was to write 2 – 3 successive posts on the subject, but as always, time’s scarce and so I’ve decided to just upload the presentation, see here.

But allow me some words on the background. A database admin (though spare time coder, too ;-)), presenting on O/R Mappers? How could that be? At least then, these performance killers should really get pulled to pieces, right?
Well, no. They aren’t. Instead, my intentions have been the following:

  • Explain to a DBA audience why developers might want to use them.
  • Explain – to DBAs and developers both – what are probable difficulties, on a conceptual level (the infamous “Object-Relational Impedance Mismatch”)
  • Explain – again on a conceptual level, but with concrete examples, and to both audiences, too – what to watch out for when fetching data (i.e., doing SELECTs as opposed to data manipulation).

“What to watch out for” – this has actually been the talk’s main purpose. (I plan to follow up with load tests / “real empirical stuff” at a later time).
And how about the “let’s talk to each other”? You may have guessed already …

Write your own Swingbench transaction

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);
      
    }
  }