Caused by: org.jboss.util.NestedSQLException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ))
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:107)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:110)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:76)
... 98 more
Caused by: javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] )
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:246)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:534)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:395)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:297)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:447)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:874)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:103)
... 100 more
【问题现象】JBOSS 崩溃
【问题原因】
Caused by: javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] )
通过异常从网上查了一下意思是：表示这个库连接数一直吃紧，或者连接超时，
查询条件拼接错误导致 SQL 一直查询数据库，
最终因积累的数据库处理太多，都在等待数据连接最终报错
整理了以下网上排查相关问题用到的SQL
--正在执行的SQL
SELECT A.USERNAME, A.SID, B.SQL_TEXT, B.SQL_FULLTEXT
FROM V$SESSION A, V$SQLAREA B
WHERE A.SQL_ADDRESS = B.ADDRESS;
SELECT * FROM CUSTOMEREN;
--最近执行的 SQL
SELECT B.SQL_TEXT, B.FIRST_LOAD_TIME, B.SQL_FULLTEXT
FROM V$SQLAREA B
WHERE B.FIRST_LOAD_TIME BETWEEN '2016-05-25/09:24:47' AND
'2016-05-25/19:24:47'
ORDER BY B.FIRST_LOAD_TIME DESC;
--查找前十条性能差的SQL.
SELECT *
FROM (SELECT PARSING_USER_ID,
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM V$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;
--获取单次执行时间最长的10个SQL
SELECT SQL_ID, SQL_TEXT, ROUND(EXEC_TIME / 1000000, 0) EXEC_TIME
FROM (SELECT SQL_ID,
SQL_TEXT,
EXEC_TIME,
RANK() OVER(ORDER BY EXEC_TIME DESC) EXEC_RANK
FROM (SELECT SQL_ID,
SQL_TEXT,
CPU_TIME,
ELAPSED_TIME,
EXECUTIONS,
ROUND(ELAPSED_TIME / EXECUTIONS, 0) EXEC_TIME
FROM V$SQL
WHERE EXECUTIONS > 1))
WHERE EXEC_RANK <= 10;
--获取执行次数最多的10个SQL
SELECT SQL_TEXT, EXECUTIONS
FROM (SELECT SQL_TEXT,
EXECUTIONS,
RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK
FROM V$SQL)
WHERE EXEC_RANK <= 10;