I am seeing there are many connection in 'INACTIVE' state and are present for more than 12-15 hours. Most of the sessions (around 90%) have executed the below query to JBP_INSTANCE_PER_USER table before moving to inactive state
Checked from last query executed from v$session table and prev_sql_id
[org.hibernate.SQL] select persistent0_.PK as PK6_0_, persistent0_.INSTANCE_PK as INSTANCE2_6_0_, persistent0_.SER_STATE as SER3_6_0_, persistent0_.USER_ID as USER4_6_0_, persistent0_.PORTLET_REF as PORTLET5_6_0_ from JBP_INSTANCE_PER_USER persistent0_ where persistent0_.INSTANCE_PK=? and persistent0_.USER_ID=?
The open connection count is reaching to DB threshold value and the inactive sessions are not getting utilized for serving the new request and DB is creating the new connection. Also I have found the PortalDS connection is running out of connection while checking from JMX console. The in-use connection is touching the maximum connection count and available connection count to zero.
Application server: Jboss Portal Application 2.7.2
I also did the code review for connection leak, but seems the above query is getting executed by Jboss internally while user logs-in to application portal.
Can anyone please help me out, if encountered the similar problem in Jboss portal application server. Let me know if need more information for issue.