Thursday, October 30, 2008

Understanding JDBC Connections From the eBusiness Middle Tier



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.

Brief overview

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:

The "s_dbc_file_name" variable in the CONTEXT.xml file provides the dbc filename and is located in the $FND_TOP/admin/<>_ directory.

JDBC connection pool parameters and their use are covered in the following documents:

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.
  1. Select the "JServ Usage" under the Monitoring section in OAM
  2. 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.
  3. If there are any non zero values for "Potentially Leaked" then click on this number to drill down into the details
  4. Select "Yes" for the "Filter by Potentially Leaked" option and click "Go" button
  5. 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
REM START OF SQL
REM
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,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id
/
REM
REM END OF SQL
REM

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:

  1. Core Java Code
  2. AOL/J JDBC Code
  3. OA Framework
  4. Functional Code
  5. 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:

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:-

The latest JDBC patch can be found in:

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 (9.2.0.8) 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 :-

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.

Functional Code

Issues at this code level would tend to be a specific bug where a connection is not released.

External influences

Firewall timeouts are known to potentially cause an issue for JDBC connections. For details, see:

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:

FND_JDBC_BUFFER_DECAY_INTERVAL=180
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_JDBC_BUFFER_DECAY_SIZE=50

Conclusion

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.

References

Related

HAPPY LEARNING!
Posted by at

No comments:

Post a Comment

Thanks for you valuable comments !

Subscribe to: Post Comments (Atom)