Barb Thanks for the link. Unfortunately, it again hints at the behaviour, but doesn't really come out with it.
What's happening is that when you execute a *static* sql statement such as 'select user into xyz from dual', the cursor remains open (as shown in v$open_cursors) after the statement has finished. This also happens with explicit cursors, even if you close them! And it happens whether session_cached_cursors is set to zero or not. I assume that the cursors are cached within the session context for the package or procedure since it only happens for named pl/sql blocks and they get closed if you recompile the block. My guess is that it is a deliberate performance optimisation within the pl/sql engine, but it does mean that to avoid ora-1000 errors, you need to set open_cursors to be greater than the *total* number of static sql statements that a session can open in its lifetime, not just the number concurrently open. Of course you have also got to include room for dynamic and recursive sql as well as cursors cached using session_cached_cursors. -- David Lord > -----Original Message----- > From: Barbara Baker [mailto:[EMAIL PROTECTED] > Sent: 26 November 2003 16:49 > To: Multiple recipients of list ORACLE-L > Subject: Re: ORA-1000 and pl/sql cursor cache > > > David: > I don't really know if this will help you, but it > might be worth a try. You could try setting session_cached_cursors. > > Bjørn Engsig's white paper "Efficient use of bind > variables, cursor_sharing and related cursor > parameters" describes this parameter a bit. It can be > found at http://miracleas.dk in the Technical > Information section) ( Guy Harrison's tuning book > also talks about this parameter. ) > > good luck! > Barb > > > > --- Lord David <[EMAIL PROTECTED]> wrote: > > Hi > > > > Does anyone know whether its possible to control the > > size of the pl/sql > > static cursor cache. > > > > I'm running into ORA-01000: maximum number of open > > cursors exceeded errors > > and part of the problem (apart from the usual > > developers not closing > > explicit cursors) is that _all_ static sql > > statements in compiled pl/sql > > units seem to be getting cached. I can't find any documentation of > > this feature apart from a few hints in the pl/sql and > > application development > > docs. Here's an example from an 8.1.7 database: - > > > > SQL>create or replace procedure foobar is > > 2 v_result varchar2(30); > > 3 begin > > 4 select user into v_result from dual; > > 5 end; > > 6 / > > > > Procedure created. > > > > SQL> > > SQL>select b.sql_text > > 2 from v$session a, v$open_cursor b > > 3 where a.sid = b.sid > > 4 and a.audsid = userenv('SESSIONID') > > 5 / > > > > SQL_TEXT > > > ------------------------------------------------------------ > > SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL > > select b.sql_text from v$session a, v$open_cursor b > > where a. > > > > SQL> > > SQL>exec foobar > > > > PL/SQL procedure successfully completed. > > > > SQL> > > SQL>select b.sql_text > > 2 from v$session a, v$open_cursor b > > 3 where a.sid = b.sid > > 4 and a.audsid = userenv('SESSIONID') > > 5 / > > > > SQL_TEXT > > > ------------------------------------------------------------ > > SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL > > select b.sql_text from v$session a, v$open_cursor b > > where a. > > SELECT USER FROM DUAL > > > > TIA > > -- > > David Lord > > Senior DBA > > Iron Mountain Europe > > > > > > *** *** *** *** *** *** *** *** *** *** *** *** *** > > *** *** *** *** *** > > This e-mail and its attachments are intended > > for the > > author's addressee only and may be > > confidential. > > > > If they have come to you in error you must take > > no > > action based on them, nor must you copy or show > > > > them to anyone; please reply to this e-mail and > > > > highlight the error. > > > > Please note that this e-mail has been created > > in the > > knowledge that Internet e-mail is not a 100% > > secure > > communications medium. We advise that you > > understand and observe this lack of security > > when > > e-mailing us. Steps have been taken to ensure > > this > > e-mail and attachments are free from any virus, > > but > > advise the recipient to ensure they are > > actually virus > > free. > > > > The views, opinions and judgments expressed in > > this > > message are solely those of the author. The > > message > > contents have not been reviewed or approved by > > Iron > > Mountain. > > > > *** *** *** *** *** *** *** *** *** *** *** *** *** > > *** *** *** *** *** > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: Lord David > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com > > San Diego, California -- Mailing list and web > > hosting services > > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > > ORACLE-L > > (or the name of mailing list you want to be removed > > from). You may > > also send the HELP command for other information > > (like subscribing). > > > __________________________________ > Do you Yahoo!? > Free Pop-Up Blocker - Get it now > http://companion.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Barbara Baker > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (or the name of mailing list you want to be removed > from). You may also send the HELP command for other > information (like subscribing). > *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** This e-mail and its attachments are intended for the author's addressee only and may be confidential. If they have come to you in error you must take no action based on them, nor must you copy or show them to anyone; please reply to this e-mail and highlight the error. Please note that this e-mail has been created in the knowledge that Internet e-mail is not a 100% secure communications medium. We advise that you understand and observe this lack of security when e-mailing us. Steps have been taken to ensure this e-mail and attachments are free from any virus, but advise the recipient to ensure they are actually virus free. The views, opinions and judgments expressed in this message are solely those of the author. The message contents have not been reviewed or approved by Iron Mountain. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lord David INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
