implicitCachingEnabled, defaultRowPrefetch

[Amir]

Hi,

I trying to use Hikari in on of our clojure based project. everything works fine but when i get Data source properties as follows

(.getDataSourceProperties (:datasource (db-connection)))

I am getting the following result.

{"maxStatements" 1000, "user" "xxxxxxx", "password" "xxxxxxxxxx", "dataSourceClassName" "oracle.jdbc.pool.OracleDataSource", "implicitCachingEnabled" "true", "defaultRowPrefetch" 5000, "oracle.jdbc.defaultLobPrefetchSize" 10000, "defaultBatchValue" 1000}


here implicitCachingEnabled is set to true but when I unwrap the source and check the status in Oracle connection it is false

(.getExplicitCachingEnabled (.unwrap (clojure.java.jdbc/get-connection (db-connection)) ;; returns false

can any one confirm this behaviour of I am doing something wrong. I am setting the properties in config as follows

                      (.addDataSourceProperty "oracle.jdbc.defaultLobPrefetchSize" (global-property (str prefix "defaultLobPrefetchSize") 10000))
                      (.addDataSourceProperty "defaultRowPrefetch"                 (global-property (str prefix "defaultRowPrefetch")      2000))
                      (.addDataSourceProperty "defaultBatchValue"                  (global-property (str prefix "defaultBatchValue")       1000))
                      (.addDataSourceProperty "implicitCachingEnabled"             (global-property (str prefix "implicitCachingEnabled") "true")
                      (.addDataSourceProperty "maxStatements"                      (global-property (str prefix "maxStatements") 1000))
                      (.addDataSourceProperty "dataSourceClassName"                (global-property (str prefix "dataSourceClassName") "oracle.jdbc.pool.OracleDataSource"))                     

thanks,

Amir

[Brett Wooldridge]

This is probably a question better to ask on the Closure-hikaricp github issue tracker.

[Amir]

I am not using Clojure-hikaricp, i am using Hikari directly.

[Amir]

As you can see the

getDataSourceProperties returns correct settings but when i check oracle connection for implicitCachingEnabled. The result is false. That probably means the settings are not being transfered from Datasource to connection.

On Friday, 6 February 2015 11:36:39 UTC+1, Brett Wooldridge wrote:

[Amir]

just did a quick java version, same result getting false for implicitCachingEnabled

package wm.tests;

import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleConnection;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/**
 * Hello world!
 *
 */
public class App
{
    private HikariDataSource ds;
   
    App(){
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:oracle:thin:@xxxxxxxxxx);
        config.setUsername("xxxxxxxx");
        config.setPassword("xxxxxxxxxxx
        config.addDataSourceProperty("dataSourceClassName", "oracle.jdbc.pool.OracleDataSource");
        config.addDataSourceProperty("implicitCachingEnabled", "true");
        config.addDataSourceProperty("maxStatements", "1000");
        ds = new HikariDataSource(config);
    }
   
    Connection getConnection() throws SQLException{
        return ds.getConnection();
    }
   
    public static void main( String[] args )
    {
        App app = new App();
       
        try {
            oracle.jdbc.driver.OracleConnection con = app.getConnection().unwrap(OracleConnection.class);
            System.out.println( con.getImplicitCachingEnabled());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

[Brett Wooldridge]

Just googling around, this seems like a common Oracle issue, for example:

http://stackoverflow.com/questions/2267654/how-to-enable-implicitcachingenabled-statement-caching-from-tomcat-for-oracle

For a proposed solutuon, see the last answer in the thread here:

https://community.oracle.com/thread/2434852

UPDATE: I will also add that, while we love HikariCP, for many Oracle users the connection pool built into the Oracle driver is actually quite sophisticated.  It supports clustering and failover in ways not possible from an external pool.  Feel free to use HikariCP, but if you find yourself needing some of those features, don't hesitate to switch over to the Oracle pool.

[Amir]

Hey Brett,

thanks for the link. the following worked for me.

-------------------------

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

Best regards and thanks for help again. It solved my issue.

Amir