DB Sesions from JVM are very very high

For past few months, there was an issue of too much swapping on a shared box in our environment. After the high SGAs were size down, the problem was still at large. One of the experts monitored and saw that JVM sessions were very high on OS level. He inquired about any known issues after RUP6. Metalink Note 459353.1 which describes post ATG RUP 5/6 issues, shows a bug 6841295 which has a similar behavior. It says:

After RUP6 Patch 5903765 is applied there are a large number of idle JDBC connections owned by the APPLSYSPUB user. These connections build up and cause performance issues.

AppsLocalLogin.jsp is leaking connections when the initial login is not successful.

Note: Although the title of the bug and patch suggests that this issue appears after RUP6 + JDK 1.6 the problem will manifest after RUP6 and does not require JDK 1.6 to be triggered

There are lots of idle database connections coming from the APPLSYSPUB user.

Use this script to identify if you are experiencing this problem:
select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%';


These connection leaks are NOT reported in ""AOL/J Database connection pool status" page.

Last SQL executed shows :

BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5,:6,:7,:8); END;
or
BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;

The fix can be obtained via Patch 6841295 "DB SESSIONS FROM JVMS ARE VERY VERY HIGH AFTER ATG RUP6 + JDK 1.6 UPGRADE"

This patch is password protected - please contact Oracle Support

We executed the SQL:

SQL> select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%'; 2 3 4 5

SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
6712

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472085

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
363139


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472085

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140226

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472095


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
471800

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140221

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140219


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1133010

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472089

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472094


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
1140216

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472090

2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472090


SQL_HASH_VALUE SQL_TEXT
-------------- ----------------------------------------------------------------
LAST_CALL_ET
------------
2656254451 BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;
472082


16 rows selected.

That matches roughly with:

$ top -b 5000 -s size | grep applmgr
25809 applmgr 73 59 0 1140M 356M sleep 39:01 0.00% java
15528 applmgr 65 59 0 361M 187M sleep 28:49 0.00% java
15548 applmgr 62 59 0 1051M 275M sleep 22:02 0.00% java
15770 applmgr 62 59 0 1051M 269M sleep 21:59 0.00% java
15538 applmgr 62 59 0 1051M 283M sleep 21:23 0.00% java
19372 applmgr 62 59 0 1136M 345M sleep 12:28 0.00% java
19435 applmgr 62 59 0 1140M 338M sleep 11:53 0.00% java
19409 applmgr 62 59 0 1112M 306M sleep 9:39 0.00% java
19395 applmgr 62 59 0 1111M 315M sleep 9:31 0.00% java
19399 applmgr 62 59 0 1111M 313M sleep 9:29 0.00% java
19414 applmgr 62 59 0 1111M 304M sleep 9:11 0.00% java
19421 applmgr 60 59 0 1144M 343M sleep 8:59 0.00% java
19452 applmgr 61 59 0 1114M 307M sleep 8:55 0.00% java
19384 applmgr 59 59 0 1114M 307M sleep 8:40 0.00% java

Strangely this instance was not on RUP6 but on RUP4. Maybe this issue occurs on RUP4 too. Since this patch is password protected, I have logged an SR with Oracle to get the password. We'll be applying this in one of the affected instances and check if it solves the issue. Will update this post once it is done.