Jared Thanks for the response. I've had a play and here are the conclusions: -
1) The cache controlled by session_cached_cursors is entirely separate from the pl/sql static cursor cache. You can turn the former off by setting session_cached_cursors to zero, but you can only turn the latter off by logging out, dropping or recompiling the pl/sql block. 2) The latter cache only operates on *named* blocks: procedure, function or package (not sure about triggers). Hence, your script will not show the behaviour since it uses an anonymous block. 3) _close_cached_open_cursors does indeed close the cursors on commit. Handy, but I don't really want to commit every time I run a query (plus it starts with an underscore:-O). -- David Lord > -----Original Message----- > From: Jared Still [mailto:[EMAIL PROTECTED] > Sent: 27 November 2003 20:05 > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-1000 and pl/sql cursor cache > > > Try playing with alternately setting session_cached_cursors > to 0 and some non-zero value and run the following script. > > Try setting _close_cached_open_cursors to both true and > false, changing which 'commit' is used, and omitting the > 'commit' altogether. > > On 9i I find that with session_cached_cursors = 0, and > a 'commit' following the pl/sql block, the only cursor > appearing in v$open_cursor is the final 'commit'. > > HTH > > Jared > > > ---------------------- > alter session set "_close_cached_open_cursors" = false; > > declare > xyz varchar2(30); > begin > select user into xyz from dual; > commit; > end; > / > > --commit; > ---------------------- > > On Thu, 2003-11-27 at 01:09, Lord David wrote: > > 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). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > 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). > -- 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).
