Oracle JDBC connection cache, connection kept open for long time and eventually fails to close it

Question

In our application, we are facing an issue where in for certain hibernate queries, the queries are taking longer (sometimes not completing) than usual and when profiled using a profiler, we are observing that the connection objects related to these queries are open but not closed. Because of this behavior, eventually the application runs out of connections and goes into high CPU and heap utilization.

    java.lang.Thread.State: TIMED_WAITING
at java.lang.Object.wait(Native Method)
- waiting on <3a685292> (a oracle.jdbc.pool.OracleImplicitConnectionCache)
at oracle.jdbc.pool.OracleImplicitConnectionCache.processConnectionWaitTimeout(OracleImplicitConnectionCache.java:2955)
at oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:374)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:374)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:178)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:156)
at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.getTargetConnection(LazyConnectionDataSourceProxy.java:403)
at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:376)
at com.sun.proxy.$Proxy75.prepareStatement(Unknown Source)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1854)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1831)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
at org.hibernate.loader.Loader.doQuery(Loader.java:899)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:311)
at org.hibernate.loader.Loader.loadEntity(Loader.java:2111)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:82)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:72)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3917)
at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:460)
at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:429)
at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:206)
at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:262)
at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:150)
at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1091)
at org.hibernate.internal.SessionImpl.access$2000(SessionImpl.java:174)
at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2473)
at org.hibernate.internal.SessionImpl.get(SessionImpl.java:991)
at org.hibernate.event.internal.DefaultMergeEventListener.entityIsDetached(DefaultMergeEventListener.java:271)
at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:151)
at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:76)
at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:913)
at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:897)
at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:901)

In such a scenario could you please suggest what kind of timeout property is preferable for the connection cache. As per JDBC documentation, we came across the following properties, please help:

InactivityTimeout
TimeToLiveTimeout
AbandonedConnectionTimeout
Ref: http://docs.oracle.com/cd/B14117_01/java.101/b10979/conncache.htm#CDEBCBJC

Answer 1

Please use Oracle Universal Connection Pool for Java (UCP) a replacement for the Implicit Connection Cache (ICC) which has been de-supported in Oracle Database 12c. The documentation is found on OTN.