This content has been marked as final. Show 3 replies
hello1 person found this helpful
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
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
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.