CODEWITHSUNDEEP
oracle-databasejdbcconnection-poolinghikaricpucp
tomas
tomas

Reputation: 739

oracle.net.keepAlive vs connection pooling (Hikari CP/UCP pool idle connection timeout)

Using connection pools such as Hikari or Oracle UCP, you can set pool size (init size, idle size, max size) and timeouts (idle/inactive connection timeout).

In hikari, you can configure keepaliveTime (combined with long idle tiemout / max life time) to prevent firewall killing idle TCP connections (SQLRecoverableException: Closed Connection)...

But in Oracle UCP, I did not find option to enable connection "keepalive". So there might be option to set it on jdbc driver level - oracle.net.keepAlive; oracle.net.TCP_KEEPIDLE; oracle.net.TCP_KEEPINTERVAL; oracle.net.TCP_KEEPCOUNT;

Question: If I enable keepalive on JDBC driver, what happens to pool's idleTimeout when pool size is bigger than minimumIdle?

Would the pool drop such connection as "idle" or would it consider "alive" because of keepalive packets and keep it opened?

Thanks

Upvotes: 0

Views: 997

Answers (4)

tomas
tomas

Reputation: 739

Based on answers, I have decided not to interfere with ojdbc settings and set ucp to validateConnectionOnBorrow=true, secondsToTrustIdleConnection=1800 to achieve detection of broken connections (because of missing keepalive option in ucp)

Upvotes: 0

Pablo Silberkasten
Pablo Silberkasten

Reputation: 46

The correct prefix for UCP properties in Spring Boot is spring.datasource.oracleucp (not just ucp as a previous answer)

Upvotes: 1

Saurabh Verma
Saurabh Verma

Reputation: 300

You can pass the JDBC driver properties to UCP using setConnectionProperties() API like below.

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
Properties props = new Properties();
props.setProperty("oracle.net.keepAlive", "true");
pds.setConnectionProperties(props);

The driver keepAlive settings are transparent to UCP pool, it will close an idle connection if it is inactive from x seconds where x is configured inactive connection timeout on ucp pool.

Upvotes: 0

frostedPenguin
frostedPenguin

Reputation: 1

It seems oracleUCP gives an option for you to set keep alive time.

spring.datasource.ucp.time-to-live-connection-timeout

And in general, I don't think it is a good idea to mess with connection settings on the driver level because it could create conflicts!

reference: https://docs.oracle.com/en/database/oracle/oracle-database/21/jjuar/oracle/ucp/jdbc/UCPDataSource.html#setTimeToLiveConnectionTimeout_int_

Upvotes: 0

Related Questions