How To Prevent Inactive JDBC Connections In Oracle Applications 11i and R12 (Doc ID 427759.1)

Last updated on JULY 20, 2023

Applies to:

Goal

Many Inactive JDBC connections causing performance issues in the database and in Framework pages. There are many reasons for the inactive JDBC sessions to happen. Following are the brief details how the JDBC connections are established and are maintained in pool.

In E-Business suite environment, the JDBC connections are established with the server when there is a database connection request comes from the client. In Oracle applications we use the JDBC thin driver out of various database connection drivers. The dbc file present under $FND_TOP/secure directory (In R12,location is defined by Environment variable $FND_SECURE) contains various parameters which are responsible for the connection to the database upon receiving a request from Apache Jserv.The following are the important parameters in the dbc file :

FND_MAX_JDBC_CONNECTIONS=100
FND_JDBC_BUFFER_MIN=5
FND_JDBC_BUFFER_MAX=5
FND_JDBC_BUFFER_DECAY_INTERVAL=60
FND_JDBC_BUFFER_DECAY_SIZE=1
FND_JDBC_USABLE_CHECK=true
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_PLSQL_RESET=false

The AOLJ Database connection pool is intended to have a farm of open JDBC connections to the database which can be borrowed by the java code running in the OACoreGroup for a short time. Performance wise this is more efficient since it saves opening and closing of a JDBC connection each time. This however means that a connection can be idle for quite a long time when there is little activity in the system.

Note that each JVM has it's own connection pool. So, if there are 2 JVMs running for OACore, then there are also 2 connection pools. This is important since it also means that the max number of JDBC connections in this case is 2 x FND_MAX_JDBC_CONNECTIONS. Specially in large environments with multiple MT servers and multiple JVM's the total number of connection could become too large. Unfortunately there is no mechanism implemented in the connection pool which performs some kind of 'heartbeat' (like we have in Forms) for idle connections.

Also 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 some code borrows it and then finds out that the connection has been dropped. We can drop all the INACTIVE connections at once. Later, when high number of new requests for JDBC connections are received then a lot of new connections have to be created which does not benefit system performance. The JDBC connection pool, neither knows nor cares whether a given user is still logged in. It only cares how many different user sessions need access to the database right now.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.