ADF BC Tuning with Do Connection Pooling and TXN Disconnect Level

For one of my previous posts, Steve Muench was pointing out that there is option to tune data source usage in ADF by setting jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1. You can find Steve comment here - How To Speed Up Application Undeployment in JDeveloper 11g R2. If your application contains many application modules (let's say 50) and there are lots of concurrent users working at the same time, with default ADF BC settings we would reserve large number of database connections. Reserved connection will be hanging until AM instance will be destroyed. This is not what we want, especially with high number of concurrent users. If we set jbo.doconnectionpooling = true, database connection will be reserved only during HTTP request and then released. Thats not good, because lots of AM passivation/activation will happen. However, with second property - jbo.txn.disconnect_level = 1, ADF BC instead of passivating/activating  to database, will keep AM data in memory during HTTP requests. It will passivate/activate as expected, only if AM instance will be granted to another session (situation when no free AM instances in the pool). Based on chapter 44.2 Setting Pool Configuration Parameters, there is Oracle recommendation:

Leave the jbo.doconnectionpooling configuration parameter set to false for best performance without sacrificing scalability and reliability. Database connection pooling is still achieved through application module pooling. The only exception is when multiple application module pools (and therefore a large number of application module instances) share the same database, making the total available database connections the highest priority.

But few lines below that, there is another recommendation:

However, when minimizing the total overall number of database sessions is a priority, one situation in which it might be appropriate to use database connection pooling is when you have a large number of application module pools all needing to use database connections from the same underlying application user at the database level.

Finally chapter 44.3.2 What You May Need to Know About Database User State and jbo.doconnectionpooling = true says:

Alternatively, you can set jbo.txn.disconnect_level=1 (default is 0) to ensure that all application modules, view objects and row sets remain in memory and stay valid after their corresponding references to JDBC connections are dropped. Upon activation, the framework reexecutes and synchronizes the cursor positions. When used in conjunction with jbo.doconnectionpooling=true, setting jbo.txn.disconnect_level=1 reduces the memory overhead associated with this situation.

Before diving into technical experiment, lets draw some conclusion based on Oracle documentation:

1. If we don't have large number of concurrent users, and number of opened database connections is not priority - its better to use default ADF BC setting (jbo.doconnectionpooling = false). This will safe some performance, because it will not close/open database connection for each HTTP request
2. If opened database connections reduction is priority and there is large number of concurrent users, we can turn on database connection pooling and enable virtual memory to keep AM data in between requests (without passivating it to database). This will allow to detach AM instance from DB connection and prevent opened database connection hanging until timeout happens
3. Please test tuning options thoroughly, before applying - every tuning have its own side effect

I did couple of experiments, to test how these different settings affect application performance. Download sample application with database connection pooling and virtual memory for AM instance enabled - DoConnectionPoolingApp.zip.

Sample application is fairly simple, I'm testing only navigation between different rows. First, let's test with default ADF BC settings, when database connection pooling is disabled:

In average, Next/Previous operation completes in 750 ms:

Database connections are reserved for long time, until AM instance will timeout:

Situation with default settings is quite clear, let's now enable database connection pooling. Also override prepareSession(Session) method in AM, you will see it will be called now for every HTTP request - make sure it doesn't break any custom logic, if any implemented in prepareSession(Session) from your system:

Enable database connection pooling - set Disconnect Application Module Upon Release:

Test Next/Previous actions, when database connection pooling is enabled, scroll through row set - it works significantly slower - 3 seconds in average:

It is much slower, because for every HTTP request, passivation/activation to/from database happens - connection is detached from AM instance (it might be very slow, especially if VO SQL is complex, because it will be re-executed on each HTTP request):

We can see this from connection graph, database connection is reserved for 1 second and then returned back to the pool:

Now let's enable virtual memory for ADF BC and set jbo.txn.disconnect_level = 1 (default is 0):

With virtual memory enabled, we can scroll through row set much faster: 870 ms in average (it adds few ms perhaps because database connection is re-established on each HTTP request):

Big advantage - database connection in most of the cases is reserved for such short time, its even not registered. Only for longer requests, we can spot that database connection is used and then immediately released back - very good:

Let's see now, how this tuning behaves in stress test environment. We set Maximum Pool Size = 1 and Referenced Pool Size = 1, this means there is space only for 1 session - second session will be activated and first always passivated:

From session A, let's filter result set:

Open another session B:

Session A data will not be lost, it will be passivated into database in this case, because there is no space in AM pool:

When session A will be activated, SQL will be re-executed as expected:

This means, even when database connection pooling is ON and virtual memory is ON, passivation/activation mechanism still works - when there is not enough space inside AM pool. Thats good, try to apply jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1 and test to see positive/negative effect in your project.