Saturday, October 15, 2011

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.

25 comments:

Chris Muir said...

It's a valuable post Andrejus, thanks for posting it.

You'll remember from recent blog entries I demonstrated the changing nature of BTF automatic AM nesting between 11.1.1.X and 11.1.2.X. In the later AMs of child BTFs are no longer auto nested under the parent BTF's AM, all become a root AM. This means our application if using an AM per BTF when moving to 11.1.2.X will suddenly use a huge amount of db connections.

Regards,

CM.

Andrejus Baranovskis said...

Thanks for feedback Chris. Yes, its always good to know those little, but very important tricks :)

Andrejus

Chris Muir said...

Hi Andrejus

I was having a bit of a further think about this over the weekend.

Setting jbo.txn.disconnect_level=1 which causes the state of VOs and more to be placed in memory wouldn't be suitable for a clustered/failover environment would it? Presumably ADF relies on the ADF memory state to be stored in the database passivation store to support failover?

What's your thoughts?

CM.

Andrejus Baranovskis said...

Hi Chris,

I did a test run with following settings:

jbo.doconnectionpooling = true
jbo.txn.disconnect_level = 1
jbo.dofailover=true (enable cluster support)

With such setup, it behaves as it would ignore jbo.txn.disconnect_level = 1. As expected, it performs passivation after each request - in order to support cluster failover.

However, because jbo.doconnectionpooling = true, it still able to release DB connections back to the pool - as it is with single node environment.

From what I can see, when jbo.dofailover=true, jbo.txn.disconnect_level = 1 setting is ignored and it works as it would be jbo.txn.disconnect_level = 0

Regards,
Andrejus

oceans said...

Hi Andrejus,

Thanks for the great tip, up there. I noticed a rather strange behavior which I would like to share with you.

Say we have a application with one AM and a single view object, connected to database through a datasource. I have two view controller projects say VC1 and VC2 sharing the same model. I created two pages in each of the viewController project as follows.

1)In VC1,P1 Normal JSPX page with the view object used in it.

2)In VC2,P1 Normal JSPX page with a inline frame calling the P1 in the VC1 through URL.

It works fine no issues, But what I noticed is rather a something I couldn't understand,

Every time I refresh the P1, only one connection is used every time. But when I refresh the P2, for every refresh one connection gets added up.

The only difference in P2 is the using the INLINE FRAME calling the URL in other project. But y is that creating new connections instead of using the existing one like P1.

Regards,
Oceanvijai

Andrejus Baranovskis said...

Hard to answer correctly, without seeing sample code. You can send it to my email, I will look.

Generally using INLINE FRAME is bad practice.

Andrejus

oceans said...

Hi Andrew,

I have mailed you the sample, It is actually your sample with little bit modifications.

Regards,
Oceanvijai

Andrejus Baranovskis said...

Hi,

Yes, I reproduced your behavior. As I said earlier, this happens because of Inline Frame. Its very bad practice to use Inline Frames, when browser refresh happens it loads new session for Inline Frame. Its why new AM instance is created.

Andrejus

oceans said...

Hi Andrejus,

Thank you for your reply.

In our application we use INLINE FRAME predominantly for calling the jspx from one viewController to another viewController.

So could you suggest an alternative for the INLINE FRAME so many AM instances are not created for the single user session.

Thanks,
Oceanvijai

Andrejus Baranovskis said...

Use ADF Libraries and include Task Flows through ADF Regions.

Andrejus

oceans said...

Hi Andrejus,

Thank you for your reply.

If we use model through ADF library, which DATASOURCE will it take.

Say I have a application with DS1 and I import another application which has a model project of its own with DS2. Which datasource will the view objects form the library take in the runtime.

Regards,
Oceanvijai

Andrejus Baranovskis said...

It will take both DS1 and DS2. View Objects are not taking data source, but Application Module. If two Application Modules are defined with DS1 and DS2, those two data source will be used on runtime.

Andrejus

Anonymous said...

Andrejus I have been testing these settings to free up db connections. what i am seeing is with the ordDocDomain attribut for file uploads that the metadata seems to be persisted but the actual file is not uploaded.

Andrejus Baranovskis said...

Hi,

You might be interested to tune Idle Instance Timeout and Pool Pooling Interval instead - http://andrejusb.blogspot.com/2011/11/stress-testing-oracle-adf-bc.html

Andrejus

ADF New Learner said...

Hi Andrejus,
Can we connect to two connection in one application. For eg. I login to one application and then moving to one of the jsf page, can we have a button "Another Conn" which will connect to another db.

Can this be achieved in ADF 11g.

Waiting for your quick response.

Andrejus Baranovskis said...

Hi,

Probably this post will help you: http://andrejusb.blogspot.com/2012/08/sample-application-for-switching.html

Andrejus

Đặng Việt Hà said...

Hi Andrejus,

What if we set the issupportspassivayion=false?

Andrejus Baranovskis said...

Then you will start loosing data, when there are more concurrent users. This is bad :)

Andrejus

Suresh K said...

Hi Andrejus,

Say if we have 600 concurrent users are accessing my application, how should we define the parameters for connection pooling ?

Also, how to simulate this scenario to test, during the development time?

Thanks in advance,
Regards,
Suresh

Andrejus Baranovskis said...

You can simulate using JMeter.

Andrejus

animesh said...

Hi Andrejus,

We have a AppModule which consits of 3 VOs. All VOs are static/rows populated programmatically.

And tall the VO's consists of transient attributes.

So everything works fine in Development environment. However it does not works properly in a clustured envirnment.

Can you please point out what changes I need to do.

Thanks in advance.

Roy

Andrejus Baranovskis said...

Hi,

You should enable failover and implement passivation/activation support for transient VO's.

Regards,
Andrejus

animesh said...

Hi Andrejus,

I have no done all the below changes

jbo.doconnectionpooling = true
jbo.txn.disconnect_level = 1
jbo.dofailover=true (enable cluster support).
So all the options under configurations under pooling and scalability are checked now.

And regarding transient VO. I have checked the "include all transient attributes" under passivate state of VO.

So, are these changes sufficient for the clustered environment testing.

Note: All the transient attributes which only used for showing data in pivot table. So now on the VOs I have marked passivate state under VO tuning. Also checked include all transient attributes.

Thanks,
Animesh

Andrejus Baranovskis said...

Yes, this should be sufficient - it still doesnt work?

Regards,
Andrejus

animesh said...

Hi Andrejus,
Thanks for confirming. I will let you know after testing tomorrow.

thanks
animesh