Sunday, February 14, 2010

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.

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:

Idle Instance Timeout (10 minutes default)

Pool Pooling Interval (10 minutes default)

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.

73 comments:

To clarify your last statement, what you're recommending is that we set Idle Instance Timeout, Pool Timeout and timeouttolive in combination, correct? ... not just 1 of these settings.

Also we note the ratios on your settings, Idle Instance = 60 sec, Pool Timeout = 30 sec, and timeouttolive = 120 sec. Is there a reason you set the values as you did in terms of timeouttolive > idle instance > pool timeout? In other words what's the relationship between the relative values, and what would be the effect if they were set the other way around?

Yes, thats right - all three settings should be used in combination, not just independently.

Regarding ratios - Idle Instance Timeout (60 sec - how long application module instance will stay idle until it will be marked as inactive), Pool Pooling Interval (30 sec - interval to check for inactive application module instances and remove them from the pool. this means, if after 60 sec instance is marked as inactive, during next 30 sec it will be removed from the pool), Maximum Instance Time to Live (even after application module instance is removed from the pool, its still alive and keeps DB connection reserved - in order to clean it, maximum time to live is set. it definitely should be longer comparing to Idle Instance Timeout + Pool Pooling Interval).

I have tried setting up the weblogic server with the em. But the issue is my EM is displaying only one node that is admin server under the wc_domain. The other node to check the connection leakage is not visible to me. Can you please share is there any other ear we need to deploy in weblogic to see this detail

When we refresh a page, it again occupies another 300 connections and thereby exceeding 1000 connections easily and causing "MAXIMUM SESSION EXCEEDED" problem from DB. Can you please give us a solution for this.

Also we have another problem here:

The connections are not released even when the screen is idle. So we used the appmodule level configuration which you have mentioned in the blog. We have set as

It'd be interesting once you figure out why the app is using 60 connections to post your results. If it's simply just a table in a page based on one AM and VO, it should be 1 connection, so something is extremely screwy in your app.

I strongly recommend not to set "Disconnect Upon Release", this will break ADF BC pooling for AM. Also, on each request ADF will passivate and activate AM instance. Also data might be lost during requests.

I'm also facing the same problem.Appmodules are setu up according to this blog post, and in test example of 3 concurrent users open cursor problem arrises.Any ideas? We're using Jdev 11.1.2. and weblogic 10.3.5. with sherman patch(adf runtime 11.2.).Regards,Patrik

This post doesnt describe anything related to cursors, it covers open db connections. I think its normal for Fusion apps to reserve cursors, its the reason while XA type data source is not suitable for Fusion apps (however I was not analyzing this deeply).

Hi,later today I've discovered something strange/interesting. According to your blog post I was "playing" with weblogic data source parameters(Home-Services-Data sources - myAppDS). I've put max open connections to be 75 as 15 is not enough for our project, but I've discovered that max open connection that is allowed is still 15(default)and app was craching right after 15th opened conn.

By further exploring of weblogic console, I've discovered that data source properties under Home-Deployments - myApp - Modules- myAppDS is different than one I've set up under Data sources node, despite the fact that we're refering to same database conn. After that, I've succesfully chnaged parameters of this one data source and everything is working as it should. Point is that I wasn't aware of possibility to have different tuning of same data source for different project.Maybe this "discovery" could help to Oceans and Dinesh too :)

2. If I use a getDBConnection() in Bean Class to perform DB Transaction. Do we need to close this in finally block . And Do we get this connection from the AM Connection pool or Weblogic Server connection Pool.

3. Can you please explain best practices for the Connection pool when we use Multiple Application Modules in our Application.

I used a application shared AppModule to store the static data of the application, but some times, we want to this AppModule to reload all the data from database immediately when the static data have some modifications. Can we have a way to do this?

Can you please let us know, what is the correct/optimal connection pool settings at AM Level and Weblogic console level JDBC settings? We deployed our ADF applications to Clustered Environment with 2 managed servers. We did not modify any default AM pool settings and at WLS level, we have max capacity 300 and Inactive Connection timeout is set to 600 sec.

I followed below oracle document to refresh the shared AM, but when grant change notification to , it make many other trouble, even I cannot check the refresh LOV is work properly or not.

Do you have any sample to refresh the shared AM. Thank you so much!

-----10.4.6 How to Automatically Refresh the View Object of the View Accessor

If you need to ensure that your view accessor always queries the latest data from the lookup table, you can set the Auto Refresh property on the destination view object. This property allows the view object instance to refresh itself after a change in the database. The typical use case is when you define a view accessor for the destination view object.

Because the auto-refresh feature relies on the database change notification feature, observe these restrictions when enabling auto-refresh for your view object:

The view objects should query as few read-only tables as possible. This will ensure the best performance and prevent the database invalidation queue from becoming too large.

The database user must have database notification privileges. For example, to accomplish this with a SQL*Plus command use grant change notification to .

When these restrictions are observed, the refresh is accomplished through the Oracle database change notification feature. Prior to executing the view object query, the framework will use the JDBC API to register the query for database notifications. When a notification arrives, the row sets of the corresponding view object instance are marked for refresh during the next checkout of the application module. Because the shared application module waits until the next checkout, the row set currency of the current transaction is maintained and the end user is not hampered by the update.

For example, assume that an LOV displays a list of zip codes that is managed in read-only fashion by a database administrator. After the administrator adds a new zip code as a row to the database, the shared application module detects a time when there are no outstanding requests and determines that a pending notification exists for the view instance that access the list of zip codes; at that point, the view object refreshes the data and all future requests will see the new zip code.

To enable auto-refresh for a view instance of a shared application module:

In the Application Navigator, double-click the view object that you want to receive database change notifications.

In the Property Inspector expand the Tuning Database Retrieve section, and select True for the Auto Refresh property.