Is it possible that number of open_cursors raise above maximum set limit

[Mrutyunjay Hanchinal]

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.
----------

[Beksy Kurian]

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 ]

[Rudy Dakota]

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.

[Mrutyunjay Hanchinal]

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.

[Beksy Kurian]

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

[Rudy Dakota]

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.