Today I will describe how jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1 properties affect ADF application performance. Read more about these properties - ADF BC Tuning with Do Connection Pooling and TXN Disconnect Level. Previous posts related to ADF BC application stress testing - Stress Testing Oracle ADF BC Applications - Internal Connections.
We will see that with jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1, average request processing time is faster. Performance boost is achieved, because passivation/activation circle happens in memory (data remains in memory) - data is not being stored into PS_TXN table, this allows to save execution time. After each request, we are releasing DB connection, this allows to save server resources as well. However, please keep in mind - this tuning approach is suitable mostly for such use cases, where database connection doesn't keep temporary data in database (for example - post changes or long running locks). Because of frequent database connection switch, you may loose such data.
Download sample test case - stresstest_v3.zip.
Stress test is performed with 80 concurrent users, 16000 transactions in 10 minutes (see previous posts for more details on JMeter script).
jbo.doconnectionpooling = true
jbo.txn.disconnect_level = 1
Average request processing time is quite interesting - it is a bit slow for initial requests, but improves with time:
If we compare the same type of graph, when jbo.doconnectionpooling = false and jbo.txn.disconnect_level = 0, number of passivations/activations is reported to be the same. Average request processing time is slightly longer:
Average request processing time with jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1 is 43.12 (ms):
Average request processing time with jbo.doconnectionpooling = false and jbo.txn.disconnect_level = 0 is 75.23 (ms):
Database connection usage with jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1 is reduced significantly. There should not be PS_TXN passivation/activation happening, but it still reports some internal connections consumed by AM:
As expected - much more database connections are reserved with default jbo.doconnectionpooling = false and jbo.txn.disconnect_level = 0:
We will see that with jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1, average request processing time is faster. Performance boost is achieved, because passivation/activation circle happens in memory (data remains in memory) - data is not being stored into PS_TXN table, this allows to save execution time. After each request, we are releasing DB connection, this allows to save server resources as well. However, please keep in mind - this tuning approach is suitable mostly for such use cases, where database connection doesn't keep temporary data in database (for example - post changes or long running locks). Because of frequent database connection switch, you may loose such data.
Download sample test case - stresstest_v3.zip.
Stress test is performed with 80 concurrent users, 16000 transactions in 10 minutes (see previous posts for more details on JMeter script).
jbo.doconnectionpooling = true
jbo.txn.disconnect_level = 1
Average request processing time is quite interesting - it is a bit slow for initial requests, but improves with time:
If we compare the same type of graph, when jbo.doconnectionpooling = false and jbo.txn.disconnect_level = 0, number of passivations/activations is reported to be the same. Average request processing time is slightly longer:
Average request processing time with jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1 is 43.12 (ms):
Average request processing time with jbo.doconnectionpooling = false and jbo.txn.disconnect_level = 0 is 75.23 (ms):
Database connection usage with jbo.doconnectionpooling = true and jbo.txn.disconnect_level = 1 is reduced significantly. There should not be PS_TXN passivation/activation happening, but it still reports some internal connections consumed by AM:
As expected - much more database connections are reserved with default jbo.doconnectionpooling = false and jbo.txn.disconnect_level = 0:
Excelent post. Straight to the point! :-)
ReplyDeleteThanks Maiko, cool :-)
ReplyDeleteAndrejus we were attempting to test these changes in our applicaiton but we have been having trouble with a file upload component. First of all i cannot get the file upload to work through jmeter at all. And second upon setting the disconnect level to 1 and using the app through the web browser the file upload is failing. The odd this with this is that the metadata of the file is making it into the ordsys column but the file itself is not there. Can you provide any insite or testing of the file upload with or without the disconnect level property.
ReplyDeleteHi,
ReplyDeleteIt would help if you could send me sample app with reproducable case. Is it possible?
Regards,
Andrejus
I could reproduce something but not until next week. Have you tested anything using file upload.
ReplyDeleteNo, I didnt had such requirement to test.
ReplyDeleteAndrejus
Veri nice post. informative and I like it
ReplyDeleteIn our next major release, we've further enhanced this mechanism to support a recycle threshold concept for the DB connection pool just like the AM pool offers. Within a given recycle threshold, the DB connection will attempt to stay "sticky" to a given user, but like the AM pool it's not a guarantee, so you might end up getting any JDBC connection in the pool, but if we are able to keep it sticky then there's a bit of time we can save for that user.
ReplyDeleteHi,Andrejusb
ReplyDeleteHow to show image like this http://3.bp.blogspot.com/-wDV_AtGZCeA/TsQ3IYvrG3I/AAAAAAAAFow/OLowauRsriY/s1600/3b.png
. I saw jdbc dashboard on jmeter .
but I couldn't found last screen shots
thanks.
Hi,
ReplyDeleteThis is not part of JMeter. All statistics are extracted from Oracle Enterprise Manager.
Andrejus
Hi Andrejus,
ReplyDelete"this tuning approach is suitable mostly for such use cases, where database connection doesn't keep temporary data in database"
How could I know that "database connection doesn't keep temporary data in database" in my ADF application? When this happens?
Cheers,
Ferez
Hi,
ReplyDeleteThis depends on architecture of your system. For example if there is PL/SQL code that holds temporary variables in DB between requests for current JDBC connection. Which is considered bad practice anyway.
Andrejus
Andrejus,
ReplyDeleteThank you very much, with your explanation it seems that it is OK for me to use these settings in my application but I encountered an strange problem using these settings which I posted here:
https://forums.oracle.com/thread/2549773
I would be thankful if you could take a look at it.
Thanks,
Ferez