Optimizing Oracle ADF Application Pool

If you was reading my previous post - Monitoring Data Source Connection Leaks, you got clear idea how you can monitor data source connection pool usage by Oracle ADF application. Basically speaking, Oracle ADF application works with two pools - Application and Database. First pool allows to optimize application work on middle tier, while second pool cares about database connections. Today I will describe how you can control JNDI data source connection pool.

As a reference, please check Steve Muench article - Understanding Application Module Pooling Concepts and Configuration Parameters. This post will be based mostly on Application Pool Cleanup Parameters section. Download sample application with optimized data source connection pool usage - DatabaseConnectionLeak.zip.

Main topic I will talk today is how to control data source connection pool grow. Most of ADF 11g applications are developed using JSF Fragments. Usually separate JSF Fragments are based on separate Application Modules. This means, if main page contains 10 JSF Fragments, 10 Application Modules will be involved during main page rendering. If there will be 10 users, there will be 10 x 10 = 100 connections created in data source connection pool. I don't want to say its not good to have many Application Modules, its good - work load will be distributed across different Application Modules. What is not good - by default, data source connections are removed back to available pool after really long time. Systems were we have many concurrent users can run out of available data source connections.

First thing WebLogic administrator will do is to enable Inactive Connection Timeout, hoping this will return inactive data source connections back to available pool. I have set it to 60 seconds:

My system contains two Application Modules, both are used from the same main page:

When user opens main page, from data source connection monitoring, we can see following picture:

Connection Pool Size increases up to 2 connections - thats correct, because main page involves two Application Modules. Connections in Use pool contains two active connections. As mentioned above, Inactive Connection Timeout was set to 60 seconds, and after 60 seconds we can see both reserved connections were removed from Connections in Use pool. Thats correct, but same time Connection Leaks were reported. This means, we can't use WebLogic Inactive Connection Timeout option to manage data source connections. ADF application keeps data source connection and WebLogic is removing it by force - its why leaked connections are reported. You can set Inactive Connection Timeout to be triggered once per day, to ensure any really leaked connections are removed.

What we could do is to play with Application Pool parameters. Connection Pool section is disabled, because we are using JNDI Data Source defined on WebLogic:

I'm interested in 3 parameters from Application Pool:

1. Idle Instance Timeout (10 minutes default)
2. Pool Pooling Interval (10 minutes default)
3. Maximum Instance Time to Live (available through Properties tab, 1 hour default)

For such systems, where we have many concurrent users with short requests, default values probably are not suitable. Let's change Idle Instance Timeout to 1 minute and Pool Pooling Interval to 0.5 minute (same as you can see on screenshot above). This will affect Application Pool, but not data source connection pool - reserved two connections will remain as Connections in Use, even when there will be no active users online:

Such behavior is not acceptable and will expand data source connection pool very fast, this will block your application.

There is Maximum Instance to Live property, it allows to control how long Application Module instance should be available in Application Pool:

Default value is 1 hour, while it is acceptable, in most of the systems it will be too long. While Application Module instance will be alive, it will keep opened database connection in use. Yes, new users will reuse Application Instances available in Application Module pool and this will prevent database connection grow. However, where we have many concurrent users and many Application Modules involved, 1 hour for Maximum Instance to Live can be too long.

You can test Maximum Instance to Live with 2 minutes time. I have enabled it only for one of my Application Modules.  Now we see such picture - 2 database connections reserved and 1 removed back to available connections pool after 2 minutes:

After 2 minutes, user comes back to his screen and makes some action related to data refresh:

Second data source connection is reserved again and remains reserved in 2 minutes time after last user activity:

While Idle Instance Timeout and Pool Pooling Interval will help you to optimize Application Pool, Maximum Instance Time to Live property will help to optimize entire application workload.