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