In this article I will describe the basics of configuring and monitoring JDBC connections between the eBusiness Web Tier and the Database, then cover some common issues to help identify root causes for both Release 11i and Release 12 of eBusiness Suite.
In general, whenever a functional page requires data from the database, it makes a call to a lower code level (the Java layer in the Application Object Library, also known as AOL/J) which handles the database connectivity. The AOL/J code provides a JDBC connections to the database through the Database Connection Pool.
You should note that the Java Connection Pool mechanism for eBiz is completely provided by eBiz code and does not use the mechanisms provided to pool connections through Java language directly, nor through the Application Server configuration.
Configuring JDBC connection pooling
The JDBC connection pool is created using the settings in the DBC file. The name, location and contents of this DBC file is controlled through AutoConfig. To modify any of the JDBC connection pool parameters, you should therefore use the techniques described in the following document to ensure changes are maintained in a supportable and consistent way:
- Using AutoConfig to Manage System Configurations with Oracle Applications 11i (Metalink Note 165195.1)
The "s_dbc_file_name" variable in the CONTEXT.xml file provides the dbc filename and is located in the $FND_TOP/admin/<>_
JDBC connection pool parameters and their use are covered in the following documents:
- Diagnosing and tuning AOL/J JDBC Pool in Oracle e-Business Suite 11i (Metalink Note 278868.1)
- The "Application Module and Connection Pooling" chapter in:
- Release 11i : Oracle Application Framework Developer's Guide (Metalink Note 269138.1)
- Release 12 : Oracle Application Framework Developer's Guide (Metalink Note 394780.1)
When considering any changes, you should also take into account that every JVM has its own JDBC connection pool. For example, if you have one Web Node with three OACoreGroup JVMs plus one XmlSvcsGrp JVM configured, then you will have a total of four JDBC connection pools with connections to your eBiz database
Monitoring the JDBC connection pool
It is always a good idea to understand how your environment looks when things are going well, to give you a baseline to compare against if you need to investigate any issues.
You will most certainly need to review the JDBC connection data if you are experiencing issues.
Monitoring JDBC Connections through Oracle Applications Manager (OAM)Login to OAM directly or via the "System Administration" responsibility.
- Select the "JServ Usage" under the Monitoring section in OAM
- Click the "Expand all" link to list the Servers and all the JServ processes for OACoregroup. This shows memory usage, connections (including "Potentially Leaked") and Application Module information. You can click the "Add to support cart" to capture this page if Oracle Support are assisting your investigation.
- If there are any non zero values for "Potentially Leaked" then click on this number to drill down into the details
- Select "Yes" for the "Filter by Potentially Leaked" option and click "Go" button
- Click "Show all details" to display the Java Thread dump for all the potentially leaks Java connections
The "old" way of gathering this data was to use the URL http://host.domain:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp but this will only give data for the one JVM you happen to connect to, so may not be so useful in multi-JVM environments.
Run SQL scripts to monitor database connections
Using SQL scripts will not give so much information as OAM, but can be used to provide useful summary information on a periodic basis. For example you may wish to include the information from the SQL below as part of your baseline data:
REM START OF SQL
REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
REM END OF SQL
NOTE - when looking at V$SESSION, the STATUS of JDBC connections tend to show as INACTIVE, this is normal and does not indicate a specific problem
Where Could It Go Wrong?
Issues with the JDBC connection pool tend to be of a nature whereby the number of database connections increase over time and do not seem to be released again. If this continues unchecked, then you may find the database will run out of sessions/processes and/or the Middle Tier JVM will run out of connections or memory.
A "quick fix" would normally be to restart Apache, but the underlying cause would need to be investigated further.
Issues can potentially occur at five different levels:
- Core Java Code
- AOL/J JDBC Code
- OA Framework
- Functional Code
- External Influences
I'll discuss each of these areas next.
Core Java code
Although eBiz does not use native Java connection pooling methods, we do rely on the underlying Java APIs generally. Any issues at this level will generally require the latest Java version to be installed
If you need to upgrade your Java version, see:
- Overview of Using Java with Oracle E-Business Suite Release 11i (Metalink Note 300482.1) if you need to upgrade your Java version
AOL/J JDBC code
As this is the code that handles JDBC connection, it is often the first area to be blamed, but the JDBC connection pool can only drop database connections where the calling application has released the JDBC connection it holds in the pool, so it often turns out to be an issue higher up the code stack.
Number of JDBC connections increase after ATG Rup 5 because jdbc parameters are lower case (Metalink Note 459072.1) describes a known issue with Apps 11i.
It is prudent to be on the latest JDBC driver patch, but should have at least applied one of the following patches:-
- Patch 4282857: REHOSTING ORACLE JDBC THIN DRIVERS V220.127.116.11+ FOR USE WITH APPS 11I
- Patch 4201222: REHOSTING ORACLE JDBC THIN DRIVERS V18.104.22.168 FOR USE WITH APPS 11I
The latest JDBC patch can be found in:
- Upgrading Oracle JDBC Drivers with Oracle E-Business Suite 11i (Metalink Note 164317.1)
You should note that the JDBC driver version has no relation to the Database version, as it is installed on the eBiz Middle Tier. For example, the latest version of JDBC drivers provided by patch 4899697 (22.214.171.124) is the same patch for all RDBMS versions.
OA Framework (OAF)
OA Framework calls AOL/J when it needs a database connection, and it is up to OAF to release any such connection when it has finished with it. There is an added complexity, in that OAF also has its own pooling mechanism for the OAF Pages, which is the "Application Module pool" (AM pool). This means that although a user may have finished with a page, the page and its associated database connection are retained for a period of time.
The AM pool is controlled by profile options, which are described in :-
- Oracle Application Framework Profile Options Release 11i (Metalink Note 275876.1)
- Oracle Application Framework Profile Options Release 12 (Metalink Note 395445.1)
Issues at this code level would tend to be either:
1. Issue with AM Pooling
You can test the effect of disabling AM pooling by setting the profile option "FND: Application Module Pool Enabled" to "No". Use this technique with caution if considering this for a production environment.
2. Specific bug where database connection is not released.
This would generally require patching.
Issues at this code level would tend to be a specific bug where a connection is not released.
Firewall timeouts are known to potentially cause an issue for JDBC connections. For details, see:
- Firewall BLACKOUT and JDBC connections with Oracle Applications 11i(Metalink Note 276557.1)
Although this note is for Apps 11i, the technical issue can also apply to Release 12 as well
Configuring eBiz to minimize JDBC connections
If investigating issues with JDBC connections increasing, it may be useful to minimise the database connections as much as possible by de-tuning the JDBC pool. This may reduce end user performance, so should be used with caution if considering this for a production environment.
To do so, you need to do both these steps:
1. Disable Application Module (AM) Pooling
This is necessary as the AM objects hold a JDBC connection whilst they are alive, even if they are not currently used.
Set the profile option "FND: Application Module Pool Enabled" (AMPOOL_ENABLED) at SITE level to a value of "No"
2. Set the JDBC connection pool parameters to release connections:
Identifying issues with JDBC connections can sometimes be a frustrating process, as the investigations may need to consider multiple failure points and complex architectures. I hope this article has given you a better understanding of JDBC Pooling and where to start looking for issues.
Happy Learning !