Tuesday, July 16, 2013

Researching ADF 12c Connection Threshold Property Functionality

ADF 12c developer guide describes new ADF BC tuning property - Disconnect Application Module Upon Reaching Maximum Connection Threshold (jbo.ampool.connection_threshold). I was doing a bit of research around this property and testing how it works. It looks to me - it doesn't work yet, as it should. You should be careful using jbo.ampool.connection_threshold. As per developer guide, connection threshold functionality is enabled when threshold value is set to positive number. Once AM pool will grow more than this number, ADF BC will start disconnecting and releasing DB connections for the least used AM's. Disconnect will happen during AM pool cleanup, developer guide recommends to configure shorter pool cleanup interval (jbo.ampool.monitorsleepinterval). With normal ADF BC DB pooling enabled, disconnect will happen after each request. In theory connection threshold is supposed to optimize this and do disconnect only when exceeding configured threshold and during pool cleanup cycle. However this doesn't seem to work, at least based on my test.

My test was based on these tuning settings:


- Minimum Available Size changed to be 0, all AM instances will be cleaned

- Pool Pooling Interval changed to be 30 seconds. Meaning in 30 seconds interval AM pool cleanup monitor will wake up and try to disconnect AM instances exceeding connection threshold or inactive AM instances

- Idle Instance Timeout property is kept default 10 minutes on purpose, in order to force earlier disconnect from jbo.ampool.connection_threshold

- Disconnect Application Module Upon Release is not set. As it is stated in the developer guide, when positive threshold value is set - DB connection disconnect mode is set automatically (meaning no need to set it by yourself directly)

Connection threshold (jbo.ampool.connection_threshold) is set to be 3. Meaning when there will be more than 3 active AM's with DB connections, disconnect mechanism should start during next pool cleanup cycle:


I was running this test with 6 concurrent users, starting with the first user - completing first user actions and then starting with the next user. By the time 6th user starts his session, 30 seconds interval from the first user session was passed - pool monitor should disconnect exceeded number of connections - but this didn't happen. All 6 connections still remain reserved, with connection threshold set to 3 and pool monitor interval set to 30 seconds:


Here you can download sample application I was using for this test - ADFBCConnThresholdApp.zip. This tuning option looks to me very powerful for performance optimization and more optimal DB connection usage, hopefully it will be fixed soon and we could use it. Or may be my test was wrong, and it works already - let's see if we get more input regarding this tuning option.
at

20 comments:

  1. AnonymousJuly 18, 2013 at 9:44 AM

    And this is Oracle, I always wonder if it test its product before they release them.

    ReplyDelete
  2. UnknownAugust 4, 2013 at 6:53 PM

    Hi,

    I am using 11.1.1.5 version. According to my understand, if there is no unreferenced AM instance, system will passivate and activate AM. If I set the Maximum Pool Size and Referenced Pool Size to a very very large value, the data control can always use the same AM for every HTTP request and no passivation is needed. The reason why I do not set the size to a very large value is that system will request operating system to allocate more memory and memory swap will occur. Swap is slower than passivation and that is the reason why we need to adjust the value. Right?

    ReplyDelete
  3. Andrej BaranovskijAugust 4, 2013 at 10:03 PM

    You should set referenced pool size to estimated number of concurrent users.

    Andrejus

    ReplyDelete
  4. UnknownAugust 5, 2013 at 6:37 PM

    You assume that one user session will use only one instance of the AM, so set referenced pool size to estimated no of concurrent user. Right?
    No matter the memory size of the server?

    How about the Maximum Pool Size?

    ReplyDelete
  5. Andrej BaranovskijAugust 5, 2013 at 8:50 PM

    Yes, this is how is supposed to be.

    Maximum pool size is usually irrelevant, it can be any high number.

    Andrejus

    ReplyDelete
  6. UnknownAugust 6, 2013 at 6:49 PM

    Thanks for your help.

    Besides AM configuration, do you suggest developer to create a shared AM which contains all the LOV of the system?

    If yes, any benefit besides lessen data source connection? Say memory consumption? Is the improvement significant?

    Tony

    ReplyDelete
  7. Andrej BaranovskijAugust 6, 2013 at 7:57 PM

    There is no need to include lov into am at all, lov are consumed from vo. Usually i dont see a need for shared am, as it is useless with adf shared libs.

    Andrejus

    ReplyDelete
  8. UnknownAugust 7, 2013 at 7:00 PM

    I am using version 11.1.1.5 and trying to tune the performance.
    According to the suggestion from the site ( http://www.oracle.com/technetwork/developer-tools/adf/learnmore/87-lov-using-shared-am-444769.pdf), they suggest to create a shared AM to minimize the data access from database. Do you suggest system to follow? If yes, will it improve the memory, database connection in version 11.1.1.5?

    Thanks for your help in advance.

    Tony

    ReplyDelete
  9. Andrej BaranovskijAugust 7, 2013 at 8:02 PM

    I think i already explained :)

    ReplyDelete
  10. UnknownOctober 1, 2013 at 5:45 PM

    Hi Andrejus,

    For java class type DataControl/AM, does the system use the same java instance over several HTTP requests? If not, can I assume that the java instances are LOGICALLY the same? say the value of java attributes can carry forward to the next HTTP request.


    Thanks,

    Tony

    ReplyDelete
  11. UnknownOctober 16, 2013 at 6:48 PM

    Hi Andrejus,

    Any relationship between Referenced Pool Size, Minimum Available Size and Maximum Available Size? Say Minimum < Referenced Pool Size < Maximum ?

    If yes, any suggestion on the min and max value if referenced pool size is 1000?

    Thanks.

    Tony

    ReplyDelete
  12. Andrej BaranovskijOctober 16, 2013 at 7:29 PM

    It depends, I would need to test your application to answer properly.

    Andrejus

    ReplyDelete
  13. UnknownOctober 21, 2013 at 7:04 PM

    Hi Andrejus,

    Referenced pool size should be set to no of concurrent user. What do you mean concurrent user?
    Assume the application has only one AM and 1000 users login the system . Around only 5% of users will submit the request at a particular moment. Should the referenced pool size be 1000? or 50?

    In most application, it is no need to set "jbo.doconnectionpooling=true". Is it correct?

    If we set jbo.doconnectionpooling to true, ADF will release the connection immediately after the HTTP request is completed and no need to wait for any monitoring task to clean up?

    Thanks.

    Tony

    ReplyDelete
  14. Andrej BaranovskijOctober 21, 2013 at 9:43 PM

    Hi,

    Concurrent users - users working at the same time.

    Not correct, I recommend to set "jbo.doconnectionpooling=true" almost in every application - this allows to clean statement cache in ADF and release memory.

    Andrejus

    ReplyDelete
  15. UnknownOctober 22, 2013 at 6:31 PM

    Hi Andrejus,

    Thanks for your prompt reply.
    In my previous example, 1000 users login the system and submit requests to the system randomly. At a particular moment, maybe just 50 request. So concurrent user = 1000. right?

    ReplyDelete
  16. Andrej BaranovskijOctober 22, 2013 at 6:43 PM

    Hi,

    No, this means 50 concurrent users.

    Andrejus

    ReplyDelete
  17. UnknownOctober 23, 2013 at 7:04 PM

    Hi Andrejus,

    There are 1000 users using the system in my example. For example, user01 - user50 insert a record to the view object but not yet committed. Then 10 seconds later, user51 - user100 insert a record to the view object without save. The 50 AM in the pool will passivate the data of user01-50 and serve user51 - user100. Is it correct?

    If yes, there are 1000 users using the system and the AM will passivate frequently. It seems that setting the referenced pool size to 50, but not 1000 cannot prevent passivation. I do not why we need to set the referenced pool size to no of concurrent users, but not no of users using the system.

    Appreciate for your prompt reply.

    Thanks.

    Regards,
    Tony

    ReplyDelete
  18. Andrej BaranovskijOctober 23, 2013 at 7:56 PM

    I would need to come to your office or we could have conference call by remote to discuss it. This is quite complicate to give you precise answer through blog comment.

    If you are interested - send me email.

    Andrejus

    ReplyDelete
  19. UnknownOctober 24, 2013 at 7:12 PM

    Hi Andrejus,

    Deeply appreciate your help and prompt reply.

    I will send to your gmail account later.

    Thanks again.

    Tony

    ReplyDelete
  20. Jayaram DokalaDecember 22, 2016 at 5:57 AM

    Hi Andrejus,

    We have an application using Dynamic UI tab shell. It has a single AM. The following are the AM settings

    jbo.ampool.connection_threshold = 3
    Failover Transaction State Upon Managed Realease = False
    Disconnect Application Module Upon Release = False
    Idle Instance Timeout = 18000 sec
    Pool Poling Interval = 18000 sec

    Ideally, this should imply that beyond 3 datasource connections, all AM's that take a DS connection will release it when checked back into the AM pool. However, this is not happening.

    We use v$session to monitor the number of connections for the datasource user and the number of connections for the logged-in (into the app) user which uses the datasource user as a proxy (alter user app_user grant connect through datasource_user). What we see is
    1. when Disconnect App Module Upon Release is TRUE: the datasource user in v$sessions keeps increasing but the appuser count in v$session is 1 (meaning that AM's release the datasource connection as soon as they are checked-in into the AM pool). This is as expected.
    2. when Disconnect App... is FALSE, and jbo.ampool.connection_threshold is 3, the v$session counts of both datasource user and appuser keep increasing. This shows that the AM instances are not releasing the datasource connections. This is NOT as per the Developer Guide definition of this parameter (connection_threshold).

    Do we know what may be the actual reason of this? Can it be that this feature doesn't work with Dynamic UI tab shell?

    Thanks in advance!

    Jayaram Dokala

    ReplyDelete