How To Prevent Inactive JDBC Connections In Oracle Applications 11i and R12
Last updated on JANUARY 17, 2018
Applies to:Oracle E-Business Suite Performance - Version 220.127.116.11 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle E-Business Suite Technology Stack - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
Document is not applicable to 12.2.x environment with FMW WebLogic Server Techstack.
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 :
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms