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