Recently, we bounced our database for maintenance. When it came back up, we had open_cursors set to 1000. Last night, some of our clients didn't get their reports because maximum open cursors was exceeded. I set open_cursors back to 5000, the value before the database was bounced, so they could run the necessary reports. One of the developers made this comment " We do close the connection and that closes associated resultsets also. The connection is passed to ReportSessionBean.writeReport() and is closed in finally clause. ". A few questions come to mind:
1. Does closing a connection really close the associated resultsets?
2. 5000 open_cursors seems excessive to me. How do I determine if this is really what's needed or do we have other "code" options?
2. What can I do to help my developers? I'm new to the development DBA world and not familiar with Java yet. Also new to the company so I don't understand all the code relationships yet.
3. What effect do all these open cursors have on my database? Can we improve our efficiency/performance by explicitly closing cursors?
I would appreciate any insights.