3 Replies Latest reply on Jun 26, 2012 5:54 PM by Cspencer-Oracle

    Optimizing Oracle ADF connection Pool

      I am currently using jdeveloper with weblogic 11 server in my application where I face problem where I end up creating more number of open cursors while refreshing a jspx page leading to my database sever overload.

      The problem is that the open cursors are not closing as in while the page is closed or even logged out immediately.Actually in my application for a single page it takes large number cursors and its not getting closed to suffix the next requests.

      I found this blog briefing quite well about this - http://andrejusb.blogspot.com/2010/02/optimizing-oracle-adf-application-pool.html

      please help me if there is a way to close these cursors as in while they served the request or what is that holding the cursors without releasing. My simple guess is like some cache might hold it in weblogic or ADF side.
        • 1. Re: Optimizing Oracle ADF connection Pool

          i am not sure that you are diagnosing your problem correctly. Open cursors do not load your database , your current query under execution might.
          your should worry more about the number of database sessions opened rather than the open cursors. Cursor are normally kept in the oracle SGA to minimize reparsing.
          each Application Module is bound to one database session. you can control the number of sessions by configuring the max number of active AM in the pool

          ammar sajdi
          1 person found this helpful
          • 2. Re: Optimizing Oracle ADF connection Pool

            Thank you for your reply,

            I have totally 12 projects in my application and 19 App modules. U mean to say that each app module is consuming too much DB sessions and which holds the db resources ? U mean to say connections or sessions ?..... correct me if am wrong please......

            If it is so how to monitor how many each consumes to confirm and report to my boss.

            And also please tell me how to control the number of sessions by configuring the max number of active AM in the pool. I mean to say where should I look for this settings in App module or weblogic.

            Thanks in advance......

            Edited by: vijai on Sep 10, 2011 2:08 AM
            • 3. Re: Optimizing Oracle ADF connection Pool
              There is a setting of each application module that controls when ADF/JBO is going to start freeing up prepared statements and the cursors associated with them. Open the application module's configuration and on the Properties tab you'll find jbo.max.cursors. The default value is 50 which means JBO will not start closing prepared statements until that AM instance hits that high-water mark. If you have a potential of 50 cursors per AM per application, this number will quickly skyrocket. You want to lower this number down closer to what you expect to be the number of "live" view objects, those that will be reused again and again in the UI. In your case there may be view objects that were only needed in AM method calls or were only needed once during initialization or in special circumstances. These can be reclaimed and will be once you lower the jbo.max.cursors number down.

              Secondly, make sure that you don't have leaks: make sure if you create rowsetiterators that you then close them, if you create prepareStatement objects, that you close them, if you open rowsets, that you close them.

              Lastly, on application exit, you can clean up JBO resources by issuing a rollback on the transaction and calling API methods like ApplicationModule.resetState(false) to clear all EO/VO caches and immediately hand the AM instance back into the pool. After this you will see the cursor count drop.