• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
----------
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic