Thursday, June 20, 2013

ADF BC 11g PS6 DB Pooling Threshold (New) Property

During OOW'12, it was announced by ADF BC team - new tuning property for ADF BC with DB pooling enabled, coming in the next releases - jbo.ampool.connection_threshold. The main idea is to keep connected JDBC connections until predefined threshold limit is reached, once limit is reached - ADF BC will be returning least used JDBC connections back to the pool. This would allow to keep less open JDBC connections and at the same time for active AM's it will not disconnect JDBC connection on each request. Currently with DB pooling enabled, JDBC connection is disconnected and returned back to the pool after each request (still it works quite well) - Stress Testing Oracle ADF BC Applications - Do Connection Pooling and TXN Disconnect Level.

As per Jobinesh post - Smart database connection pooling with new jbo.ampool.connection_threshold parameter, threshold property for ADF BC DB connection pooling was introduced already in ADF 11g PS6 (11.1.1.7). However there is no official documentation regarding this yet, at least in ADF 11g PS6 (11.1.1.7) developer guide.

You should keep in mind - connection threshold works only when DB pooling is turned off. I was expecting opposite behavior - to disable connection pooling until threshold limit is reached and then do connection pooling. While with threshold setting, ADF behaves in default way until limit is reached and only later after AM timeout it will start to release connections back to the DB pool.

So, I decided to give a test and see how it works. Download sample application test case - DBConnectionThresholdApp.zip.

The test is really simple to check if ADF BC in 11g PS6 is able to keep threshold of so called sticky DB connections. Set now new property jbo.ampool.connection_threshold = 5. You can set any number here, doesn't matter. I just set 5 to assume that threshold will be 5 sticky JDBC connections, and when there will be more than 5 AM instances created in parallel and JDBC connection usage will grow, some of the JDBC connections will be returned back to the pool:


I will be testing ADF application with 6 concurrent users, meaning 1 JDBC connection should be released and 5 will be reserved.

Open ADF application test case and click around. I have started 7 diffent sessions:


As I can see from the log, 7 DB connections were reserved:


Two of these connections will be released later, when AM pool monitor will be cleaning inactive AM's. This is good, but still it seems to be implicit DB pooling is better.

I will do stress test to compare threshold tuning vs. DB pooling and post results.

UPDATE 2013/06/21 - Property jbo.ampool.connection_threshold is an undocumented feature and there is no guarantee that it will work across all servers and platforms. And this is not for public use at the moment.

No comments: