Jared
Thanks for the response. I've had a play and here are the conclusions: -
- 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.
- 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.
- _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:jkstill_at_cybcon.com]
> 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:barbarabbaker_at_yahoo.com]
> > > 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 <DLord_at_ironmountain.co.uk> 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: DLord_at_ironmountain.co.uk
> > > >
> > > > 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: ListGuru_at_fatcity.com (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: barbarabbaker_at_yahoo.com
> > >
> > > 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: ListGuru_at_fatcity.com (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: DLord_at_ironmountain.co.uk
> >
> > 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: ListGuru_at_fatcity.com (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: jkstill_at_cybcon.com
>
> 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: ListGuru_at_fatcity.com (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: DLord_at_ironmountain.co.uk
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: ListGuru_at_fatcity.com (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).
Received on Fri Nov 28 2003 - 03:39:25 CST
