This discussion is archived
4 Replies Latest reply: Sep 7, 2012 4:02 PM by dsurber RSS

using Oracle JDBC driver implicit caching feature

958564 Newbie
Currently Being Moderated
Hello Everybody,

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|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.

Any info greatly appreciated!
Cristi
  • 1. Re: using Oracle JDBC driver implicit caching feature
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    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.
    >
    I have not used this so can not vouch for it but I did find a reference that appears to have what you are looking for
    http://books.google.com/books?id=9MsOpXrElhUC&pg=PA513&lpg=PA513&dq=oracle+java+jdbc+OracleDataSource+setStatementCacheSize&source=bl&ots=mCwoPNxJQg&sig=7-F03ttkKo0dn6OaEnhhm-27v28&hl=en#v=onepage&q=oracle%20java%20jdbc%20OracleDataSource%20setStatementCacheSize&f=false

    I found this using a Google search for 'oracle java jdbc OracleDataSource setStatementCacheSize' and the link above will then show embedded markers in the right margin that you can click on to get to the correct page (page 513 in Chapter 13 Statement Caching)
    >
    To enable implicit statement caching on the OracleDataSource object, you need to perform the following steps:

    1. Invoke OracleDataSource.getConnection() with the ImplicitCachingEnabled property set to true, or set ImplicitCachingEnabled on the OracleDataSource by calling OracleDataSource.setImplicitCachingEanble(true) as follows (assume ods is an initialized variable of type OracleDataSource):

    ods.setImplicitCachingEnable( true );

    2. Set the cache size by invoking OracleDataSource.setMaxStatements( int maxNumberOfStatements) as follows (assume ods is an initialized variable of type OracleDataSource):

    ods.setMaxStatements( 10 );
    >
    Hope that is what you are looking for.

    If not this forum thread has some additional options that may provide some guidance. You will need to read it all to see which of these techniques you have tried and which you haven't.
    Implicit connection cache -- not working?
  • 2. Re: using Oracle JDBC driver implicit caching feature
    958564 Newbie
    Currently Being Moderated
    Thanks a lot for your answer. I took some time and played with those variables and I still couldn't get it running. I will try to go through some official Oracle support channel and see if I can find something there. In the meantime, I have an idea on how I can actually come with a workaround (to be more precise, I can build a decorator around the data source and call OracleConnection.setStatementCacheSize on the connection that is freshly created).
    Anyway, if I find the answer I will post it here also...
  • 3. Re: using Oracle JDBC driver implicit caching feature
    rp0428 Guru
    Currently Being Moderated
    >
    I took some time and played with those variables and I still couldn't get it running
    >
    Did you see this part?
    >
    set ImplicitCachingEnabled on the OracleDataSource by calling OracleDataSource.setImplicitCachingEanble(true) as follows (assume ods is an initialized variable of type OracleDataSource):
    >
    You have to cast to OracleDataSource. Did you do that? It won't work if you just use DataSource.
  • 4. Re: using Oracle JDBC driver implicit caching feature
    dsurber Explorer
    Currently Being Moderated
    As of some fairly recent release setting the system property oracle.jdbc.implicitStatementCacheSize will enable the implicit statement cache for all connections. You can do this with the -D option to java. Look in the JavaDoc for oracle.jdbc.OracleConnection. It describes all the connection properties, including this one. You can also set it in the DataSource connectionProperties and that will enable it for all connections created by that DataSource. All you have to do is set the size to a value greater than zero. That both sets the size and enables the cache.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points