ORACLE APPS : Database JDBC session pool setup

While monitoring the database connections, I observe that inactive JDBC Connections keeps on growing and are not getting dropped. You could use the SQL below to monitor these connections

REM SQL to check number of JDBC connections

set echo on
set feedback on
set pagesize 132
set linesize 80
col username format a10
col how_many format 9999
col machine format a25

select s.machine, s.username, s.module, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module from V$SESSION) s,
v$process p
where s.paddr = p.addr and s.program = 'JDBC Thin Client'
group by s.machine,s.username, s.module;

REM end of SQL

NOTE - it is quite normal for JDBC connections to show as INACTIVE status. This in itself does not mean there is a problem .


In brief, to tune the JDBC Connection Pool to minimise database connections (possibly at the expense of performance) you could modify these parameters as suggested below. Then run AutoConfig to regenerate the configuration files and re-check the DBC file to confirm your changes have been made. You then need to restart iAS for these parameters to take effect.

FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_JDBC_BUFFER_DECAY_INTERVAL=120
FND_JDBC_BUFFER_DECAY_SIZE=100



Ref erance : Oracle metalink :
Note 278868.1 "Diagnosing and tuning AOL/J JDBC Pool in Oracle e-Business Suite 11i" will soon be available to provide documentation on how these parameters interact, however until this is published you can refer to Note 264599.1 "How to interpret parameters for Applications Database Connection Pooling" for an understanding of the JDBC Connection Pool parameters

KB : ORACLE APP/How_to_tune_JDBC_connection_pool.txt