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.
Hi All, I have a strange situation here... I have the following the setup - Oracle 8.1.7 server - open_cursors=1000 When I run my application that uses stored procs. I am using cursors in the stored proc and once I start my application, the number of open cursors raise from 0 to 64 and suddenly shoots to 1040.. later it comes down to 0. As it is reducing to 0 I am concluding that I am closing all the opened cursors. Once it reaches 1040 as open_cursors, application throws "ORA-01000 maximum open cursors exceeded" Here are my questions Is it possible that number of open_cursors raise above maximum set limit? In my case how is it raising to 1040 (1040>1000). What might be the reason for a sudden shoot from 60 to 1040... Thanks in advance Mrutyunjay. ----------
How did you know that your session had 1040 cursors open? Did you check the v$open_cursor view? This view show you all cursors that are compiled and remains in SGA. V$OPEN_CURSOR contains every sessions' cursors. It includes oracle internal cursors too. Join v$session , v$sesstat and v$statname to see how many cursors you have been used. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user process.
It may be better to identify the problem rather than just blindly increasing OPEN_CURSORS further.The highest value you can set for open cursors parameter is operating system dependant. The overhead is about 250 bytes per open cursor within the shared pool.
If you explicitly open a cursor,you must explicitly close it. Make sure you close every resultset when you're done with it, and every statement (try to put in your finally blocks). Hope this helps Regards Beksy [ August 06, 2002: Message edited by: Beksy Kurian ]
Hi there Mrutyunjay, As for the number of open cursors exceeding the value you specified: I do hope you realize that the number of open cursors speciefied in the init.ora file is a per-session value. As your text is not exactly clear whether the number you give are for the instance as a whole or only for your session, the easiest explanation seems to be that those numbers are instance-wide. Good riding, Rudy.
Joined: Sep 26, 2001
Hi, I used "select count(*) from v$open_cursor where USER_NAME='mm_user'" to find out the number of open cursors. If I consider "OPEN_CURSORS determines the maximum number of cursors per user" as per Beksy,I feel that the number is the one which my application is using. Here mm_user is the user name my application uses to execute the stored procs. I think now it is clear what is my problem Thanks Mrutyunjay.
Joined: Jul 11, 2001
Here is what I got from Oracle website: fact: Oracle Server - Enterprise Edition symptom: More Cursors can be Opened in one Session than Specified with the Init.Ora Parameter Open_Cursors cause: As from Oracle Version 7.3 these structures are allocated in batches of 64 References: <Note:30781.1> Init.ora Parameter "OPEN_CURSORS" Reference Note fix: This is intended behaviour. Hummm! No further explanation. Beksy
Joined: Jul 27, 2002
Hi there Mrutyunjay, Yes, your problem is clear. All the more where 1040 is not a multiple of 64 (at least not in Dutch computing) . Good riding, Rudy.