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 :
That the Oracle JDBC Driver(ojdbc7.jar) caches PreparedStatements. Is there a way to customize the number of PreparedStatements that it can cache.
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
Any other (Oracle) JDBC performance optimization tip would be appreciated.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With