Oracle連接配接數滿了

項目逐漸做大，分布式微服務越部署越多，會遇到 資料庫連接配接數不夠的情況。

image.png

采用直連的方式報錯如下：

00:00:22.670 |Weekday_Thread-220523178|ERROR| c.e.m.b.t.WeekdayManageThread - 線程中斷運作：Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.S
QLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot 
get a connection, pool error Timeout waiting for idle object
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:238) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:263) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at com.sun.proxy.$Proxy2417.doWeekdayControl(Unknown Source) ~[na:na]
    at com.xxx.matrix.base.thread.xxxx.run(xxxx.java:43) ~[its.xxx.xxxxx-1.70.jar:1.70]
    at java.lang.Thread.run(Thread.java:784) [na:1.6.0]
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
    at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:104) ~[commons-dbcp-1.2.2.jar:1.2.2]
    at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880) ~[commons-dbcp-1.2.2.jar:1.2.2]
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:200) ~[spring-2.5.6.jar:2.5.6.SEC03]
    ... 10 common frames omitted
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
    at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1174) ~[commons-pool-1.6.jar:1.6]
    at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96) ~[commons-dbcp-1.2.2.jar:1.2.2]
    ... 12 common frames omitted

           

使用websphereb（was）報錯如下：

10:39:51.130 |Weekday_Thread878128082|ERROR| c.e.m.b.t.xx - 線程中斷運作：Could not open JDBC Connection for transaction; nested exception is com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:238) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:263) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-2.5.6.jar:2.5.6.SEC03]
    at com.sun.proxy.$Proxy109.doWeekdayControl(Unknown Source) ~[na:na]
    at com.xxx.matrix.base.thread.xxx.run(WeekdayManageThread.java:43) ~[its.xxx.xxx.xx-1.70.jar:1.70]
    at java.lang.Thread.run(Thread.java:784) [na:1.6.0]
Caused by: com.ibm.websphere.ce.cm.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
    at com.ibm.ws.rsadapter.AdapterUtil.toSQLException(AdapterUtil.java:1680) ~[com.ibm.ws.runtime.jar:na]
    at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:661) ~[com.ibm.ws.runtime.jar:na]
    at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:611) ~[com.ibm.ws.runtime.jar:na]
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:200) ~[spring-2.5.6.jar:2.5.6.SEC03]
    ... 10 common frames omitted
Caused by: com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException: CWTE_NORMAL_J2CA1009
    at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1782) ~[com.ibm.ws.runtime.jar:na]
    at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3871) ~[com.ibm.ws.runtime.jar:na]
    at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3091) ~[com.ibm.ws.runtime.jar:na]
    at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:1548) ~[com.ibm.ws.runtime.jar:na]
    at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:1031) ~[com.ibm.ws.runtime.jar:na]
    at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:644) ~[com.ibm.ws.runtime.jar:na]
    ... 12 common frames omitted
           

通常遇到這樣的問題，排查方向：

• 1、檢查應用的連接配接數，是不是配置的小了。
• 2、如果使用資料源，檢查資料源的連接配接數是不是配置小了。
• 3、檢查資料庫的最大連接配接數和目前連接配接數，是否是公用的資料庫，被别人占用了
• 4、如果上面均正常，那麼查詢時間最長的sql，是否是有鎖死等待的sql，一直在建立，但是一直鎖等待。

---- 目前連接配接數
select count(*) from v$process ;
--- 最大連接配接數
select value from v$parameter where name ='processes';  
--- 目前所有使用者，按照連接配接數排序
 select username,count(username) as con from v$session where username is not null group by username  order by con desc;
 
---- 按執行時間排序
 SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;