Sunday, February 14, 2010

Optimizing Oracle ADF Application Pool

If you was reading my previous post - Monitoring Data Source Connection Leaks, you got clear idea how you can monitor data source connection pool usage by Oracle ADF application. Basically speaking, Oracle ADF application works with two pools - Application and Database. First pool allows to optimize application work on middle tier, while second pool cares about database connections. Today I will describe how you can control JNDI data source connection pool.

As a reference, please check Steve Muench article - Understanding Application Module Pooling Concepts and Configuration Parameters. This post will be based mostly on Application Pool Cleanup Parameters section. Download sample application with optimized data source connection pool usage - DatabaseConnectionLeak.zip.

Main topic I will talk today is how to control data source connection pool grow. Most of ADF 11g applications are developed using JSF Fragments. Usually separate JSF Fragments are based on separate Application Modules. This means, if main page contains 10 JSF Fragments, 10 Application Modules will be involved during main page rendering. If there will be 10 users, there will be 10 x 10 = 100 connections created in data source connection pool. I don't want to say its not good to have many Application Modules, its good - work load will be distributed across different Application Modules. What is not good - by default, data source connections are removed back to available pool after really long time. Systems were we have many concurrent users can run out of available data source connections.

First thing WebLogic administrator will do is to enable Inactive Connection Timeout, hoping this will return inactive data source connections back to available pool. I have set it to 60 seconds:


My system contains two Application Modules, both are used from the same main page:


When user opens main page, from data source connection monitoring, we can see following picture:


Connection Pool Size increases up to 2 connections - thats correct, because main page involves two Application Modules. Connections in Use pool contains two active connections. As mentioned above, Inactive Connection Timeout was set to 60 seconds, and after 60 seconds we can see both reserved connections were removed from Connections in Use pool. Thats correct, but same time Connection Leaks were reported. This means, we can't use WebLogic Inactive Connection Timeout option to manage data source connections. ADF application keeps data source connection and WebLogic is removing it by force - its why leaked connections are reported. You can set Inactive Connection Timeout to be triggered once per day, to ensure any really leaked connections are removed.

What we could do is to play with Application Pool parameters. Connection Pool section is disabled, because we are using JNDI Data Source defined on WebLogic:


I'm interested in 3 parameters from Application Pool:

  1. Idle Instance Timeout (10 minutes default)
  2. Pool Pooling Interval (10 minutes default)
  3. Maximum Instance Time to Live (available through Properties tab, 1 hour default)

For such systems, where we have many concurrent users with short requests, default values probably are not suitable. Let's change Idle Instance Timeout to 1 minute and Pool Pooling Interval to 0.5 minute (same as you can see on screenshot above). This will affect Application Pool, but not data source connection pool - reserved two connections will remain as Connections in Use, even when there will be no active users online:


Such behavior is not acceptable and will expand data source connection pool very fast, this will block your application.

There is Maximum Instance to Live property, it allows to control how long Application Module instance should be available in Application Pool:


Default value is 1 hour, while it is acceptable, in most of the systems it will be too long. While Application Module instance will be alive, it will keep opened database connection in use. Yes, new users will reuse Application Instances available in Application Module pool and this will prevent database connection grow. However, where we have many concurrent users and many Application Modules involved, 1 hour for Maximum Instance to Live can be too long.

You can test Maximum Instance to Live with 2 minutes time. I have enabled it only for one of my Application Modules.  Now we see such picture - 2 database connections reserved and 1 removed back to available connections pool after 2 minutes:


After 2 minutes, user comes back to his screen and makes some action related to data refresh:


Second data source connection is reserved again and remains reserved in 2 minutes time after last user activity:


While Idle Instance Timeout and Pool Pooling Interval will help you to optimize Application Pool, Maximum Instance Time to Live property will help to optimize entire application workload.

73 comments:

  1. Hi Andrejus

    Thanks once again for the really useful article.

    To clarify your last statement, what you're recommending is that we set Idle Instance Timeout, Pool Timeout and timeouttolive in combination, correct? ... not just 1 of these settings.

    Also we note the ratios on your settings, Idle Instance = 60 sec, Pool Timeout = 30 sec, and timeouttolive = 120 sec. Is there a reason you set the values as you did in terms of timeouttolive > idle instance > pool timeout? In other words what's the relationship between the relative values, and what would be the effect if they were set the other way around?

    Thanks again,

    CM.

    ReplyDelete
  2. Hi Chris,

    Yes, thats right - all three settings should be used in combination, not just independently.

    Regarding ratios - Idle Instance Timeout (60 sec - how long application module instance will stay idle until it will be marked as inactive), Pool Pooling Interval (30 sec - interval to check for inactive application module instances and remove them from the pool. this means, if after 60 sec instance is marked as inactive, during next 30 sec it will be removed from the pool), Maximum Instance Time to Live (even after application module instance is removed from the pool, its still alive and keeps DB connection reserved - in order to clean it, maximum time to live is set. it definitely should be longer comparing to Idle Instance Timeout + Pool Pooling Interval).

    Regards,
    Andrejus

    ReplyDelete
  3. Thanks Andrejus, appreciate your follow up. See you soon!

    CM.

    ReplyDelete
  4. Hi Andre,

    I have tried setting up the weblogic server with the em. But the issue is my EM is displaying only one node that is admin server under the wc_domain. The other node to check the connection leakage is not visible to me. Can you please share is there any other ear we need to deploy in weblogic to see this detail

    Thanks
    Vipin

    ReplyDelete
  5. You mean its not displaying Managed Servers?

    Andrejus

    ReplyDelete
  6. Yes, I guess these are managed server, which are displaying the information about the connections.

    Actually I am trying to run the application in the example and its behavior in connection graphs, as given in this example.

    --Thanks
    Vipin

    ReplyDelete
  7. Hi,

    AdminServer can display same information as well, should not be a problem.

    Andrejus

    ReplyDelete
  8. I face the following issue in ADF 10g.

    I have an ADF page with some input fields and Apply button which saves the input data.
    Each row in the table is identified by a unique ID generated by a sequence.

    When I leave the page idle for 5-10 mins and then select Apply button, the entered data gets updated in an already existing row in the table instead of creating a new row.

    The page uses PPR on some of the fields.

    What can be the possible reasons for this behaviour?

    ReplyDelete
  9. Hi,

    We are using Jdev 11.1.1.4 to develop our application. We are facing some problems regarding the connection with DB.

    We have a Dashboard page which when loaded uses nearly 300
    connections / sessions (we found it using the below query).

    SELECT s.machine, count(1)
    FROM v$open_cursor oc, v$session s
    WHERE oc.sid = s.sid
    GROUP BY s.machine
    HAVING COUNT(1) > 2
    Order By Count(1) Desc

    When we refresh a page, it again occupies another 300 connections and thereby exceeding 1000 connections easily and causing "MAXIMUM SESSION EXCEEDED" problem from DB. Can you please give us a solution for this.

    Also we have another problem here:

    The connections are not released even when the screen is idle. So we used the appmodule level configuration which you have mentioned in the blog.
    We have set as

    max Inactive Age = 60000
    monitor sleep interval = 30000
    time to live = 120000

    After we have set them, we have the connections released after 2min

    but most of the time it is not happening.
    Is there any specific configuration to be done prior to this?

    Please get back to us for any clarification on the issue.

    Thanks,
    Dinesh

    ReplyDelete
  10. Connection will be released, if no user is using it. Keep in mind, connections can be reused by other users.

    Andrejus

    ReplyDelete
  11. Hi Andrejus,

    Thanks for the reply.

    Please clarify the below :

    In our application, we have a read only view object which fetches around 1000 records and we display this view object as af:table in a jspx page.
    When we run this page it takes nearly 60 connections.

    Is the number of connections(i.e 60) taken up is normal or high?

    Also, when we load the same page in new tab in browser, it again takes up another 60 connections.

    Is this the way how ADF behaves?

    Thanks,
    Dinesh

    ReplyDelete
  12. Looks like your application design is invalid, something really not good. Its unrelated to ADF, but most probably related to wrong ADF usage.

    Andrejus

    ReplyDelete
  13. It'd be interesting once you figure out why the app is using 60 connections to post your results. If it's simply just a table in a page based on one AM and VO, it should be 1 connection, so something is extremely screwy in your app.

    By chance have the AM pooling options been changed from the default?

    CM.

    ReplyDelete
  14. Hi Andrejus and Chris,

    Thanks for the info.

    In order to find out why huge connections are taken up, I tried to create a new project where I have only one appmodule.
    I have a .jspx page which displays the contents from one view object.

    It took 11 connections each time when the screen is loaded.

    Chris said that it should take only one connection.

    Could you please give me your suggestions.

    ReplyDelete
  15. Thats impossible to have 11 connections, if you have 1 AM instance.

    Please send me your sample application with 11 connections, I will take a look.

    Andrejus

    ReplyDelete
  16. Hi Andrejus,

    You are right that the number of connections taken up is only one when there is only one AM and one View Object.

    I was actually using the below query:

    Select S.Machine, Count(1)
    From V$open_Cursor Oc, V$session S
    WHERE oc.sid = s.sid
    GROUP BY s.machine
    Having Count(1) > 2
    Order By Count(1) Desc

    and this showed the number of cursors(I misunderstood this as the number of connections) used up.

    With one AM and one VO, the number of cursors taken up is 10.

    In our application, it shows the error "maximum number of sessions exceeded" when the number of cursors reaches 1000.

    Could you please give me a solution.

    Thanks,
    Dinesh

    ReplyDelete
  17. Hi,

    Try to change VO rows fetching to FETCH_ALL, as per this blog - http://andrejusb.blogspot.com/2009/03/ora-01000-maximum-open-cursors-exceeded.html

    How many cursors are opened now?

    Andrejus

    ReplyDelete
  18. Hi Andrejus,

    Thanks for the info.

    I have used the FETCH_ALL configuration in the View object. I can see the changes in the number of rows fetched.

    But I have used the below query

    select sum(value) from (
    select a.sid, a.value, b.name
    from v$sesstat a, v$statname b Where A.Statistic# = B.Statistic#
    And B.Name = 'opened cursors current' and a.value>0)

    to check the number of opened cursors when the page is loaded.

    Opened cursors = 3 (each time the page is loaded).

    The opened cursors value is same for both FETCH_AS_NEEDED and FETCH_ALL configuration.

    I repeatedly loaded the page and opened Cursors value kept increasing.

    I couldn't identify whether cursors are closed or not.

    Could you please give your suggestion.

    Thanks,
    Dinesh

    ReplyDelete
  19. You can send me you sample app? I will do same test on my side.

    Regards,
    Andrejus

    ReplyDelete
  20. Hi Andrejus,

    I have sent our sample application to your mail.

    Please check and give us your suggestion.

    Thanks,
    Dinesh

    ReplyDelete
  21. Ok, I will review it and will get back to you.

    Andrejus

    ReplyDelete
  22. Hi,

    I have tested, it consumes only 1 cursor on page load for every web session in your app. After timeout, this cursor is released.

    Andrejus

    ReplyDelete
  23. Hi Andrejus,

    Thanks for the reply.

    We have found the number of opened cursors using the below query.

    select sum(value) from (
    select a.sid, a.value, b.name
    from v$sesstat a, v$statname b Where A.Statistic# = B.Statistic#
    And B.Name = 'opened cursors current' and a.value>0)

    Please tell me if the query is wrong or can you share the query which you have used to check the number of cursors opened.

    Did you do any other changes in the AM or in view object configuration because we always had a minimum of 3 cursors when the page is loaded.

    Thanks,
    Dinesh

    ReplyDelete
  24. I was using same query as yours. No, I just run your application, and on every new web session it adds only 1 cursor.

    Andrejus

    ReplyDelete
  25. Hi Andrejus,

    Can you please share your weblogic connection pool configuration.

    Thanks,
    Dinesh

    ReplyDelete
  26. I was testing through JDBC URL and using ADF BC Connection Pool. Its uses 1 cursor then.

    With WebLogic default pool settings, its using 3 cursors - tested.

    Andrejus

    ReplyDelete
  27. Hi Andrejus,

    Thank you. Hope my setting are right.

    Thanks,
    Dinesh

    ReplyDelete
  28. Hi Andrejus,

    I have checked the variations in the number of cursors and I need some clarifications here.

    When will the cursors get closed?
    1. Immediately after when the page is loaded.
    or
    2. The page has to be idle for some time?

    Thanks,
    Dinesh

    ReplyDelete
  29. After page will be idle for some time, when AM pool instance will be gone (after maximum instance time to live expired).

    Andrejus

    ReplyDelete
  30. Hi Andrejus,

    Do you mean the below settings in the appmodule.

    jbo.ampool.timetolive

    Thanks,
    Dinesh

    ReplyDelete
  31. Hi Andrejus,

    Thanks for the reply.

    But the appmodule settings is for closing the connection.

    Is there any way to close the cursor alone once the particular query is executed?

    Thanks,
    Dinesh

    ReplyDelete
  32. Hi Andrejus,

    Could you please give me some solution for that.

    Thanks,
    Dinesh

    ReplyDelete
  33. Not now, I will need to research this.

    Andrejus

    ReplyDelete
  34. Hi Andrejus,

    Need a clarification for the below:

    50% of my application uses getDBTransaction() for connecting to Database and I have never closed it.

    Does the usage of getDBTransaction() method a better choice compared to the usage of a View Object?

    If getDBTransaction() method is used, should that be closed explicitly?

    Please give your suggestions.

    Thanks,
    Dinesh

    ReplyDelete
  35. I don't understand statement - "Does the usage of getDBTransaction() method a better choice compared to the usage of a View Object?"

    Those two things can't be compared :)

    ReplyDelete
  36. Hi Andrejus,

    I mean we can do some DB transactions with the help of view objects or by using getDBTransaction() method. Which one would be better?

    Then, is it fine to use the getDBTransaction() method?

    In getDBTransaction() method, Should we close the transaction explicitly?

    Thanks,
    Dinesh

    ReplyDelete
  37. With ADF BC, we never need to close DB connection explicitly (framework takes care for this). There is no difference of using VO or AM getDBTransaction() in respect to open cursors.

    Andrejus

    ReplyDelete
  38. Hi Andrejus,

    Thanks for the clarification.

    Thanks,
    Dinesh

    ReplyDelete
  39. Hi Andrejus,

    Could you please inform, once you research gets completed on the below question.

    Is there any way to close the cursor alone once the particular query is executed? (without closing the connection)

    Thanks,
    Dinesh

    ReplyDelete
  40. Its on my todo list, I will post a blog - once I will finish it.

    Andrejus

    ReplyDelete
  41. Hi Andrejus,

    Thank you. Let me know the link once it is completed.

    Thanks,
    Dinesh

    ReplyDelete
  42. It can be completed any time, may be after 1 week, may be after 3 months - I can't promise. It all depends on inspiration and time availability :) Anyway, you will see it on the blog.

    Andrejus

    ReplyDelete
  43. Awsme.You certainly helped me.You clear my concern..thanks alot....

    ReplyDelete
  44. Hi Andrejus,

    Hope you are good.

    For the cursor issue which we have already discussed in the blog, I have made a change in the appmodule --> "Pooling and Scalability" --> Disconnect application module upon release.

    By default, the check box is not selected. I selected it and the application is running fine now.
    The cursors are getting released.

    But I would like to know whether it causes any problem or affects the performance of the application in some other ways.

    Please give me your suggestion.

    Thanks,
    Dinesh

    ReplyDelete
  45. Hi,

    I strongly recommend not to set "Disconnect Upon Release", this will break ADF BC pooling for AM. Also, on each request ADF will passivate and activate AM instance. Also data might be lost during requests.

    Its okej to have cursors reserved, they will be reused.

    Andrejus

    ReplyDelete
  46. Hi Andrejus,

    Thanks for the clarification.

    But the problem in my application is when 3 or more users access our application then the cursors reach the maximum limit of 300 and application gets hanged.

    Thanks,
    Dinesh

    ReplyDelete
  47. I never faced such problem with cursors.

    Andrejus

    ReplyDelete
  48. Hi Andrejus and dinesh,

    I am also facing the exact issue where even after setting the 3 parameters I still find the open cursors issues.

    When I was exploring on this fix I read that the statement cache might retain open cursors (by setting Statement Cache Size:10 by default) on weblogic side at times.

    Is this open cursors due to this weblogic cache or some other caching mechanism in appmodule which holds the open cursors

    ReplyDelete
  49. I'm also facing the same problem.
    Appmodules are setu up according to this blog post, and in test example of 3 concurrent users open cursor problem arrises.
    Any ideas? We're using Jdev 11.1.2. and weblogic 10.3.5. with sherman patch(adf runtime 11.2.).
    Regards,
    Patrik

    ReplyDelete
  50. Hi,

    This post doesnt describe anything related to cursors, it covers open db connections. I think its normal for Fusion apps to reserve cursors, its the reason while XA type data source is not suitable for Fusion apps (however I was not analyzing this deeply).

    Andrejus

    ReplyDelete
  51. Hi,
    later today I've discovered something strange/interesting. According to your blog post I was "playing" with weblogic data source parameters(Home-Services-Data sources - myAppDS). I've put max open connections to be 75 as 15 is not enough for our project, but I've discovered that max open connection that is allowed is still 15(default)and app was craching right after 15th opened conn.

    By further exploring of weblogic console, I've discovered that data source properties under Home-Deployments - myApp - Modules- myAppDS is different than one I've set up under Data sources node, despite the fact that we're refering to same database conn.
    After that, I've succesfully chnaged parameters of this one data source and everything is working as it should.
    Point is that I wasn't aware of possibility to have different tuning of same data source for different project.
    Maybe this "discovery" could help to Oceans and Dinesh too :)

    Regards,
    Patrik

    ReplyDelete
  52. Okej, may be you have set auto generate JDBC descriptors as by default. Then its possible to have internal JDBC connection.

    See here: http://andrejusb.blogspot.com/2009/09/manual-oracle-adf-application.html

    Andrejus

    ReplyDelete
  53. Yes, you're right(as allways :-)! I wasn't aware of that feature, although heard about it but never thought about it.

    Thanx again.

    Regards,
    Patrik

    ReplyDelete
  54. Hi Partik and Andrew,
    Thanks you for giving me few leads,I almost thought that this was the root cause ,but in my project environment the checkbox u mentioned in the link in unchecked,

    And too I am not able to see any datasource related info from my deployment module nodes as partik said..... Is that the way it should be

    ReplyDelete
  55. Hi Andrejus,

    Thanks for all your really usefull entrys.

    Do you know if it is possible to tune the appModule properties you described directly in em o weblogic console, avoiding to redeploy de ear project?

    Thanks,

    Josue

    ReplyDelete
  56. Hi,

    Some of them yes, you can do that from Enterprise Manager. Select ADF application and go to ADF Settings.

    Andrejus

    ReplyDelete
  57. Link to Steve's article is now http://www.oracle.com/technetwork/developer-tools/jdev/index-097578.html

    ReplyDelete
  58. Thanks - I have update link in the blog.

    Andrejus

    ReplyDelete
  59. This comment has been removed by the author.

    ReplyDelete
  60. Hi Andrejus,

    Recently started working on ADF and Need your inputs on the Below.

    1. Relation or Difference between Weblogic Server Connection Pooling and Application Module Connection Pooling?

    2. If I use a getDBConnection() in Bean Class to perform DB Transaction. Do we need to close this in finally block . And Do we get this connection from the AM Connection pool or Weblogic Server connection Pool.

    3. Can you please explain best practices for the Connection pool when we use Multiple Application Modules in our Application.

    Thanks in Advance,
    Nag

    ReplyDelete
  61. Hi Nag,

    May be this explanation will help: http://andrejusb.blogspot.com/2011/11/stress-testing-oracle-adf-bc_16.html

    Andrejus

    ReplyDelete
  62. Hi Andrejus,

    I used a application shared AppModule to store the static data of the application, but some times, we want to this AppModule to reload all the data from database immediately when the static data have some modifications. Can we have a way to do this?

    Thank you so much!

    Ha

    ReplyDelete
  63. I think its possible, I seen information about it in ADF docs on OTN...

    Andrejus

    ReplyDelete
  64. Hi Andrejus,

    I am configure the parameter for Application module as suggested by you.

    But when on transaction page some user leaves the page for 5 to 6 minute and press the apply or save button the iterator gives nullponter exception and also transient value from VO is disable.

    Can you please help on this.


    Thanks again.

    PM

    ReplyDelete
  65. Hi,

    I would suggest to apply better these settings: http://andrejusb.blogspot.com/2011/11/stress-testing-oracle-adf-bc_16.html

    This will be more effective.

    Andrejus

    ReplyDelete
  66. Hi Andrejus,

    Can you please let us know, what is the correct/optimal connection pool settings at AM Level and Weblogic console level JDBC settings? We deployed our ADF applications to Clustered Environment with 2 managed servers. We did not modify any default AM pool settings and at WLS level, we have max capacity 300 and Inactive Connection timeout is set to 600 sec.

    We are facing below issue in our ADF application

    java.sql.SQLException: ORA-12537: TNS:connection closed
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

    Please Help.

    ReplyDelete
  67. Hi,

    You should not use WebLogic Inactive Connection Timeout at all. May be this post will help to tune your app: http://andrejusb.blogspot.com/2011/11/stress-testing-oracle-adf-bc_16.html

    Regards,
    Andrejus

    ReplyDelete
  68. Hi,

    I am facing an issue connection failed 503. on my final page only if that page is having 1000 records.

    For less number of records that page is working fine. If data is large it is going to time out.

    Please suggest if it is configuration, pooling settings issue ? or i have to change my code

    ReplyDelete
  69. Hi Andrej,

    I followed below oracle document to refresh the shared AM, but when grant change notification to , it make many other trouble, even I cannot check the refresh LOV is work properly or not.

    Do you have any sample to refresh the shared AM. Thank you so much!

    -----
    10.4.6 How to Automatically Refresh the View Object of the View Accessor

    If you need to ensure that your view accessor always queries the latest data from the lookup table, you can set the Auto Refresh property on the destination view object. This property allows the view object instance to refresh itself after a change in the database. The typical use case is when you define a view accessor for the destination view object.

    Because the auto-refresh feature relies on the database change notification feature, observe these restrictions when enabling auto-refresh for your view object:

    The view objects should query as few read-only tables as possible. This will ensure the best performance and prevent the database invalidation queue from becoming too large.

    The database user must have database notification privileges. For example, to accomplish this with a SQL*Plus command use grant change notification to .

    When these restrictions are observed, the refresh is accomplished through the Oracle database change notification feature. Prior to executing the view object query, the framework will use the JDBC API to register the query for database notifications. When a notification arrives, the row sets of the corresponding view object instance are marked for refresh during the next checkout of the application module. Because the shared application module waits until the next checkout, the row set currency of the current transaction is maintained and the end user is not hampered by the update.

    For example, assume that an LOV displays a list of zip codes that is managed in read-only fashion by a database administrator. After the administrator adds a new zip code as a row to the database, the shared application module detects a time when there are no outstanding requests and determines that a pending notification exists for the view instance that access the list of zip codes; at that point, the view object refreshes the data and all future requests will see the new zip code.

    To enable auto-refresh for a view instance of a shared application module:

    In the Application Navigator, double-click the view object that you want to receive database change notifications.

    In the Property Inspector expand the Tuning Database Retrieve section, and select True for the Auto Refresh property.

    ReplyDelete
  70. Hi Andrejus,
    Recently I have entered to ADF development and your blog posts, would say sometimes are life savers. :). Grateful for that.
    I have this issue of connections are not been released back hence after time goes reaches to
    maximum pool limit.
    I was referencing to "http://andrejusb.blogspot.com/2011/10/adf-bc-tuning-with-do-connection.html" article. And version I use, 12.1.3, above settings are default.
    And I yet to apply changes mentioned here and will come back with details.
    I have a strange issue, which occurs only when deployed in server. I'm using ADF link to navigate to next page. But when deployed to server, user needs to click several times to get that activity performed.
    1) This issue is not there, when the application is run locally.
    2) ADF Application is called from the Oracle ERP
    3) At beginning, action of the link was a task flow activity and even changed that to get cal pro grammatically.
    4) No errors shown in the log. (System.out is enabled)

    Since i have this, connection pool issue, is this related to that ? Anyway you could help?

    Regards,
    Lalama

    ReplyDelete