CODEWITHSUNDEEP
oracle-databasejdbcoracle11gdriver
cristi
cristi

Reputation: 171

using Oracle JDBC driver implicit caching feature

I am pretty sure that somebody else already asked this question, but I still couldn't find a satisfactory answer to it. So, here is my scenario: I want to use the Oracle's JDBC driver implicit statement caching (documented here: http://docs.oracle.com/cd/B28359_01/java.111/b31224/stmtcach.htm#i1072607)

I need to use the connections from a 3rd party JDBC pool provider (to be more specific, Tomcat JDBC) and I have no choice there.

The problem is that the way to enable the implicit caching is a two-step process (accordingly to the documentation):

1.

Call setImplicitCachingEnabled(true) on the connection or Call OracleDataSource.getConnection with the ImplicitCachingEnabled property set to true. You set ImplicitCachingEnabled by calling OracleDataSource.setImplicitCachingEnabled(true)

2.

In addition to calling one of these methods, you also need to call OracleConnection.setStatementCacheSize on the physical connection. The argument you supply is the maximum number of statements in the cache. An argument of 0 specifies no caching.

I can live with 1 (somehow I can configure my pool to use the OracleDataSource as a primary connection factory and on that I can set the OracleDataSource.setImplicitCachingEnabled(true)). But at the second step, I already need the connection to be present in order to call the setStatementCacheSize.

My question is if there is any possibility to specify at the data source level a default value for the statementCacheSize so that I can get from the OracleDataSource connections that are already enabled for implicit caching.

PS: some related questions I found here: Oracle jdbc driver: implicit statement cache or setPoolable(true)?

Update (possible solution):

Eventually I did this:

  1. Created a native connection pool using oracle.jdbc.pool.OracleDataSource.
  2. Created a tomcat JDBC connection pool using org.apache.tomcat.jdbc.pool.DataSource that uses the native one (see the property dataSource).
  3. Enabled via AOP a poincut so that after the execution of 'execution(public java.sql.Connection oracle.jdbc.pool.OracleDataSource.getConnection())' I pickup the object and perform the setting I wanted.

The solution works great; I am just unhappy that I had to write some boilerplate to do it (I was expecting a straight-forward property).

Upvotes: 5

Views: 12448

Answers (2)

Pino
Pino

Reputation: 9303

The white paper Oracle JDBC Memory Management says that

The 11.2 drivers also add a new property to enable the Implicit Statement Cache.

oracle.jdbc.implicitStatementCacheSize

The value of the property is an integer string, e.g. “100”. It is the initial size of the statement cache. Setting the property to a positive value enables the Implicit Statement Cache. The default is “0”. The property can be set as a System property via -D or as a connection property via getConnection.

Upvotes: 6

Mustafa Ulu
Mustafa Ulu

Reputation: 185

You can only change statement cache size through OracleConnection.setStatementCacheSize method.

Instead of modifying your application to call OracleConnection.setStatementCacheSize on every connection, you can create a JDBC interceptor.

@Override
public void reset(ConnectionPool pool, PooledConnection connection) {

    if (connection == null) {
        return;
    }

    Connection original = connection.getConnection();

    if (!(original instanceof OracleConnection)) {
        return;
    }

    try {
        if (!((OracleConnection) original).getImplicitCachingEnabled() && implicitCachingEnabled) {

            ((OracleConnection) original).setImplicitCachingEnabled(implicitCachingEnabled);

            log.info("Activated statement cache");

            ((OracleConnection) original).setStatementCacheSize(statementCacheSize);

            log.info("Statement cache size set to " + statementCacheSize);
        }
    } catch (SQLException e) {
        log.error(e.getMessage(), e);
    }
}

Upvotes: 1

Related Questions