This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
would the database performance get affected with that many open cursors ?
Cursors are database resources, and if you keep using them without returning them, eventually you will run out. Without any references I can point you at, I believe that yes, it is a problem, and yes, you will need to fix the code if it is causing a resource leak.
a> Open Cursors is a limit at DB session level. This maps to a JDBC connection on Java. When you get a max open cursors exceeded exception, that is for a specific connection which being used. Other threads using different connections to the same database, which haven't hit that limit will continue as normal. When you shut down the app server, you have closed the JDBC connections and so those open cursors have gotten closed.
b> When you have 1000 users accessing the application, you'll probably have fewer pooled JDBC connections being used. So if you return a connection which has exceeded the limit back to the pool, it will be unusable when someone gets it again from the pool, and either the cursors need to be closed or the connection needs to be closed. Oracle can easily handle thousands of sessions with hundreds of open cursors in each session. However if reopening the same cursor multiple times uses unnecessary resources, reducing efficiency. Further it can cause chaining on some of the internal hash tables because your cursors are identical causing increased resource usage and contention in the library cache.
<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>