Oracle JDBC Optimization : Enable PreparedStatement caching in a Spring boot Application

Question

I have a Spring Boot REST application that's connected to an Oracle database. We're using JDBC using JdbcTemplate. The Oracle database properties are obtained through these 3 application.properties settings:

spring.datasource.url
spring.datasource.username
spring.datasource.password

This application is using the HikariCP. From the HikariCP website, I came to know that this pool doesn't cache PreparedStatements because the JDBC driver is best setup to do that.

Now, where and what would I specify to ensure these :

1. That the Oracle JDBC Driver(ojdbc7.jar) caches PreparedStatements. Is there a way to customize the number of PreparedStatements that it can cache.

2. From https://howtodoinjava.com/java/jdbc/best-practices-to-improve-jdbc-performance/, we see that

   Ensure that your database is set to the maximum packet size and that the driver matches that packet size. For fetching larger result sets, this reduces the number of total packets sent/received between the driver and server.

In pursuance of the above, what are the steps required to

1. find the Oracle DB Server packet size
2. find if the Oracle DB Server is set to the maximum packet size
3. find set the Oracle JDBC driver's(ojdbc8.jar) packet size.

Any other (Oracle) JDBC performance optimization tip would be appreciated.

Answer 1

Hi the function Enable Prepared statement caching has nothing to do neither with Spring, neither with REST. This function is a question of negotiation only between your datasource, your JDBC driver and your database. In order to find out how to set it up read the relevant documentation about your driver , datasource and database.

When it comes to Hikari, the coirrect way to do this is(notice datasource2, rename to datasource to enable autoconfiguration):

spring:
  datasource2:
      dataSourceClassName: com.zaxxer.hikari.HikariDatasource
       .....
       ......
      configuration:
            maximumPoolSize: 25  
            data-source-properties:
               ImplicitCachingEnabled: true
               MaxStatements: 100

The properties inside your configuration will be passed straight to the underlying driver.

@Bean
@ConfigurationProperties("spring.datasource2")
public DataSourceProperties dataSourceProperties2() {
    return new DataSourceProperties();
}

@Bean()
@ConfigurationProperties("spring.datasource2.configuration")
public DataSource hikariDatasource() {


    return dataSourceProperties2().initializeDataSourceBuilder().build();

}

This example uses manual initialization of the underlying datasource.

Answer 2

• Enable Statement caching through

oracleDataSource.setImplicitCachingEnabled(true)

• Choose the right cache size to best utilize the memory

connection.setStatementCacheSize(10) Try to be closer to the number of most used statements Default statement cache size is 10

• Fallback if you cannot change the application to use statement caching

session_cached_cursors = 50 Connection.setStatementCacheSize(10)

Answer 3

Start by checking the documentation to ensure that your ojdbc8.jar matches the database server version. There are different versions of ojdbc8.jar for 11g, 11gR2, 12c.

As per this answer, you need oracle.jdbc.implicitStatementCacheSize property to be set in the JDBC driver. This article mentions few more JDBC driver properties e.g. oracle.jdbc.freeMemoryOnEnterImplicitCache or oracle.jdbc.maxCachedBufferSize. You need to check docs for your driver version to confirm that these properties are available.

This can be passed using Spring Boot HikariCP spring.datasource.hikari.data-source-properties option. Double check docs for your Spring Boot version, this property was renamed at least once:

application.yaml

spring:
  datasource:
    hikari:
      data-source-properties:
        oracle.jdbc.implicitStatementCacheSize: 100  

application.properties

spring.datasource.hikari.data-source-properties.oracle.jdbc.implicitStatementCacheSize: 100 

You might also be interested in statement fetch size but this optimization is usually applied to each statement separately.