Less Soft Parsing in Java

Avoiding hard parsing when using SQL in Java is pretty simple - just use prepared statements, and bind the variable parts to the query, eg:

import java.sql.*;
import oracle.jdbc.*;

public class TestDBOracle {

    public static void main(String[] args)
    throws ClassNotFoundException, SQLException
    {
        DriverManager.registerDriver
           (new oracle.jdbc.driver.OracleDriver());

        String url =     "jdbc:oracle:thin:@//localhost:1521/local11gr2.world";

        Connection conn =
            DriverManager.getConnection(url,"sodonnel","sodonnel");
        conn.setAutoCommit(false);

        // Prepare the statement
        PreparedStatement stmt = 
            conn.prepareStatement("select * from dual where 1 = ?");

        // Bind any variables
        stmt.setInt(1, 1);

        // Execute the statement
        ResultSet rset =
          stmt.executeQuery();

        // Print the results    
        while (rset.next()) {
            System.out.println (rset.getString(1));
        }
        // Close the statement to close the cursor on the database
        stmt.close();
    }
}

The example above isn't particularly useful code. Real world code often calls the same query in a loop, or in the case of a web application, the same query can be run for each request with database connections persisted across requests.

If there real world application are coded in a similar way to above, the select statement is opened (aka soft parsed in the database), executed and then closed over and over again. While soft parsing is not too bad, it would be better if it could be totally eliminated.

One strategy is to add some logic in the application to store the Prepared Statement object and retrieve it when the same piece of SQL needs to be executed. However, the Oracle JDBC drivers provide two better ways of achieving the same thing.

Implicit Cursor Caching

If implicit cursor caching is enabled on the Oracle connection, each time a new statement is prepared, it is retrieved from the cursor cache inside the Java connection object. When the statement is closed, it is not actually closed on the database, it is simply stored inside the connection object in case it is needed again.

Using this feature is almost completely transparent to the application. By setting two parameters on the connection object, implicit cursor caching will be enabled for all statements created from the connection:

(OracleConnection)conn.set_statement_cache_size(100);
(OracleConnection)conn.set_implicit_caching_enabled(true);  

Depending on the size of the application, the size of the cache can be moved up or down.

Note that the Connection object (which is really an interface) needs to be cast to an OracleConnection to get access to the Oracle Connection specific methods.

Explicit Cursor Caching

Explicit cursor caching also stores opened cursors inside the connection object, but only if you explicitly tell it to, and you only get the statement back if you explicitly ask for it. Therefore, enabling this feature is not transparent to the application.

First, explicit caching needs to be enabled for the connection:

(OracleConnection)conn.set_statement_cache_size(100);
(OracleConnection)conn.set_explicit_caching_enabled(true);

Then a slightly different approach needs to be used when preparing calls or statements. The getCallWithKey and getStatementWithKey methods allow a statement to be retrieved from the cache, while the closeWithKey method allows a statement to be stored in the cache for later retrieval. The code below demonstrates using explicit cursor caching.

Again note the cast calls when using the Oracle specific methods on the connection and statement objects.

import oracle.jdbc.*;
import java.sql.*;

public class TestDBOracle {

    public static void main(String[] args)
    throws ClassNotFoundException, SQLException
    {
       DriverManager.registerDriver
           (new oracle.jdbc.OracleDriver());

        String url =     "jdbc:oracle:thin:@//localhost:1521/local11gr2.world";

        Connection conn =
        DriverManager.getConnection(url,"sodonnel","sodonnel");

        conn.setAutoCommit(false);
    ((OracleConnection)conn).setStatementCacheSize(100);
    ((OracleConnection)conn).setExplicitCachingEnabled(true);   

        PreparedStatement stmt = null;

        stmt =     ((OracleConnection)conn).getStatementWithKey("select1");
        if (stmt == null) {
        System.out.println ("The query is not cached");
        stmt = ((OracleConnection)conn).prepareStatement("select * from dual where 1 = ?");
    }
        stmt.setInt(1, 1);
        ResultSet rset = stmt.executeQuery();
    ((OraclePreparedStatement)stmt).closeWithKey("select1");
        //
        // Run the same query again to prove it comes from the cache    
        //
        stmt =     ((OracleConnection)conn).getStatementWithKey("select1");
        if (stmt == null) {
        System.out.println ("The query is not cached");
        stmt = ((OracleConnection)conn).prepareStatement("select * from dual where 1 = ?");
    } else {
            System.out.println ("The query came from the cache");
        }
        stmt.setInt(1, 1);
        rset = stmt.executeQuery();

        while (rset.next()) {
            System.out.println (rset.getString(1));
        }
    ((OraclePreparedStatement)stmt).closeWithKey("select1");
    }
}