Oracle JDBC连接缓存，连接保持打开很长时间，最终关闭失败

Question

在我们的应用程序中，我们面临着一个问题，在某些hibernate查询中，查询比平时花费更长的时间(有时不完成)，当使用分析器进行分析时，我们观察到与这些查询相关的连接对象是打开的，而不是关闭的。由于这种行为，应用程序最终会耗尽连接，并进入高CPU和堆利用率。

    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)

在这种情况下，你能建议哪种超时属性更适合连接缓存吗?根据JDBC文档，我们遇到了以下属性，请帮助:

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

Answer 1

请使用Oracle通用连接池for Java (UCP)代替隐式连接缓存(ICC)，这在Oracle数据库12c中已不支持。该文档可在OTN上找到。