Understanding Inactive Sessions From JDBC Connection Pool in Oracle Weblogic Server

JDBC connections are established with the database server when there is an application connection request coming from the client. The JDBC connection pool is intended to have a farm of open JDBC connections to the database which can be borrowed by the application. Performance-wise, this is more efficient, since it saves opening and closing a JDBC connection each time. However, this means that a connection can be idle for quite a long time when there is little activity in the system. Most connections will spend most of their time either sitting in the connection pool waiting for a Java thread to open them, or waiting on the Java thread to do something with the data, or waiting on the network to transfer data between the machines. That means that there will be a reasonable number of connections to the database where the STATUS in V$SESSION is "INACTIVE" at any given point in time. That is perfectly normal.

However, it is important to verify that there is no steady increase in the number of open connections, pointing to a leak of connections that prevent these from being reused and cause the maximum number of connections to be reached. To avoid this:

• Connections used from the JDBC pool need to be closed after usage by the application code. If close() is not called, connections are not freed and not available for reuse. Please refer to: How To Ensure No ResultSet/Connection/Statement Leaks Exist in Your JDBC Code
• Connection pools should be configured with timeout properties. Depending on the connection pool, there are particular properties for this. For example, for Oracle Universal Connection Pool (UCP), you can refer to: Controlling Stale Connections
• There is no mechanism in the Connection pool to determine whether the JDBC connection to the database has been dropped. So the JDBC connection in the pool still seems to be valid until the application borrows it, and then finds out that the connection is invalid as it has been dropped.
• For this, the connection pool has an option to validate the connection before passing it to the application. On this, for example for UCP, please refer to: Validating connections

Also, there is no mechanism implemented in the connection pool which performs some kind of 'heartbeat' for idle or dropped connections. Dead Connection Detection (DCD) in the database can help with the cleanup of inactive sessions in the database. Besides setting ENABLE=BROKEN parameter in the DESCRIPTION clause of the connection descriptor:

jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=myhost))(CONNECT_DATA=(SID=orcl)))

SQLNET.EXPIRE_TIME would need to be set in the database SQLNET.ORA file. This will enable Dead Connection Detection. Information on this setting can be found in : Dead Connection Detection (DCD) Explained

SQLNET.EXPIRE_TIME is a keepalive mechanism. From a JDBC perspective, for example to prevent firewall timeouts from severing connections, the JDBC connection string should include the ENABLE=BROKEN clause and at the database side, SQLNET.EXPIRE_TIME should be added.
The (ENABLE=BROKEN) clause will work in conjunction with the server-side SQLNET.EXPIRE_TIME=1 , by enabling the JDBC driver to respond to the client probes sent by the server-side when the SQLNET.EXPIRE_TIME is reached. This will provide regular traffic on the sockets and prevent them from timing out. See JDBC documentation at : Oracle® Database JDBC Developer's Guide 11g Release 2 (11.2) Part Number E16548-03 Frequent Abrupt Disconnection from Server