How To Prevent Inactive JDBC Connections In Oracle Applications

Applies to:
=======
Oracle Application Object Library - Version: 11.5.9 to 11.5.10.2
This problem can occur on any platform.

Symptoms
=======
Many Inactive JDBC connections causing performance issues in the database and in framework pages

Cause
=====
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 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
======
Following proactive checks can be done to prevent high number of inactive JDBC connections :

1. Check the for the JDK version being used it the instance. From JDK 1.4.2 onwards as a thumb rule it is suggested to use 1 JVM per CPU for 100 active connected users to OACoreGroup.

Use the script to determine "active users" for OACoreGroup :

REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
select 'Number of user sessions : '
count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != 'Y'
and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < limit_connects;
REM
REM END OF SQL
REM


Note 362851.1 : Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12

2. For application version 11.5.10 onwards ensure ATG_PF.H is applied onto the instance. Also ensure that you are using the latest version of the JDBC driver. You may run the following sql to get the current JDBC driver version in the system :

select bug_number, decode(bug_number,
'3043762','JDBC drivers 8.1.7.3',
'2969248','JDBC drivers 9.2.0.2',
'3080729','JDBC drivers 9.2.0.4 (OCT-2003)',
'3423613','JDBC drivers 9.2.0.4 (MAR-2004)',
'3585217','JDBC drivers 9.2.0.4 (MAY-2004)',
'3882116','JDBC drivers 9.2.0.5 (OCT-2004)',
'3966003','JDBC drivers 9.2.0.5 (OCT-2004)',
'3981178','JDBC drivers 9.2.0.5 (NOV-2004)',
'4090504','JDBC drivers 9.2.0.5 (JAN-2005)',
'4201222','JDBC drivers 9.2.0.6 (MAY-2005)') Patch_description
from ad_bugs
where bug_number in
('3043762',
'2969248',
'3080729',
'3423613',
'3585217',
'3882116',
'3966003',
'3981178',
'4090504',
'4201222'
) order by 2;

3. Ensure that you have all the database initialization parameters set and all the recommended database performance patches applied on the instance as per the following notes :


Note 216205.1 : Database Initialization Parameters for Oracle Applications 11i
Note 396009.1 : Database Initialization Parameters for Oracle Applications Release 12
Note 244040.1 : Oracle E-Business Suite Recommended Performance Patches


4. Implement a strategy to minimize the JDBC connections

If JDBC connections are being retained in the pool then this will ensure connections are dropped as soon as the application has finished with it.

Apart from ensuring minimum DB connections it will also help to identify if there is a JDBC connection leak

a) De-tune JDBC connection pool

Do through Autoconfig (or manually update DBC file) on all Middle Tier servers

FND_JDBC_BUFFER_DECAY_INTERVAL=120
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_MAX_JDBC_CONNECTIONS=256
FND_JDBC_USABLE_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

Note : "FND_JDBC_USABLE_CHECK=true" is preferred for RAC, as discussed in note
Note 278868.1 : AOL/J JDBC Connection Pool White Paper
Note 294652.1 : E-Business Suite 11i on RAC : Configuring Database Load balancing & Failover


5. Ensure that the current TCP settings are set as recommended by Oracles tcpset.sh script and also from OS vendor web site

Parameter Recommended value

tcp_ip_abort_interval 60,000
tcp_keepalive_interval 900,000
tcp_rexmit_interval_initial 1500

6. Check the jserv.properties "security" settings

Ensure that the current setting does not allow more connections to the JVM than JDBC connections, which is not best practice.

Changing security.backlog, in particular, can lead to user connections hanging.
Maintain default settings
security.maxConnections=256
# security.backlog=5

7. Check ApJServRetryAttempts parameter in Jserv.conf

This setting will delay any recovery of a dead JVM by mod_oprocmgr. If a JVM is not responding in 45 minutes (default setting) then tuning should be implemented to resolve this, rather than allowing 45 minutes of no response.

Maintain default settings

ApJServRetryAttempts 3

8. Disable JVM Distributed Caching

If some JVMs out of many are not servicing requests and generating "java.lang.NoClassDefFoundError" errors. Disabling Distributed JVM caching would eliminate this cause of the problem.

Disabling Distributed JVM Cache is achieved by changing "LONG_RUNNING_JVM=" from "true" to "false" in the jserv.properties. This is controlled by AutoConfig parameter "s_long_running_jvm"

9. Prevent wastage of database connections on the system by setting the profile option

'FND: Application Module Pool Minimum Available Size' value to 0 (which is the default).


After doing the above monitor the progress in terms of connection utilization. Also keep collecting the following information from system periodically:

column module heading "Module Name" format a48;
column machine heading "Machine Name" format a15;
column process heading "Process ID" format a10;
column inst_id heading "Instance ID" format 99;
prompt
prompt Connection Usage Per Module and process
select to_char(sysdate, 'dd-mon-yyyy hh24:mi') Time from dual
/
prompt ~~~~
select count(*), machine, process, module from v$session
where program like 'JDBC%' group by machine, process, module order by 1 asc
/

10. The following sqls can be useful

+ To find total number of open database connections for a given JVM PID

SELECT s.process, Count(*) all_count FROM v$session s WHERE s.process IN () GROUP BY s.process

+ To find number of database connections per JVM that were inactive for longer then 30 minutes

SELECT s.process, Count(*) olderConnection_count FROM v$session s WHERE s.process IN ()
and s.last_call_et>=(30*60) and s.status='INACTIVE' GROUP BY s.process

+ To find the modules responsible to JDBC connections for a process id

SELECT Count(*), process,machine, program, MODULE FROM v$session s
WHERE s.process IN ('&id')GROUP BY process,machine, program, MODULE ORDER BY process,machine, program, MODULE;


11. For processes that show the highest counts in query result above, Log in to the JVM machine and run

"kill -3 jvmid".

where jvmid is the process seen in query output above.

The output goes to OACoreGroup.*.stdout file. Post those files to Oracle Support for reveiw.

12. If there seems to be a lot of blocked sessions in the database. Check 2 or 3 locked/blocked sessions to ascertain the user, sql and row locked.

You can use utllockt.sql ( Note 166534.1 : Resolving locking issues using utllockt.sql script ) as the starting point. Post the output from utllockt.sql in addition to the SQL / row lock information for 2 or 3 sessions to Oracle Support.

References
========
Note 164317.1 - Upgrading JDBC drivers with Oracle Applications 11i
Note 166534.1 - Resolving locking issues using utllockt.sql script
Note 216205.1 - Database Initialization Parameters for Oracle Applications Release 11i
Note 244040.1 - Oracle E-Business Suite Recommended Performance Patches
Note 278868.1 - AOL/J JDBC Connection Pool White Paper
Note 294652.1 - E-Business Suite 11i on RAC : Configuring Database Load balancing & Failover


HAPPY LEARNING!