RE: ORA-1000 and pl/sql cursor cache
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: -
SQLcreate 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
SQLselect 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
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect 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
RE: ORA-1000 and pl/sql cursor cache
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: -
SQLcreate 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
SQLselect 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
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect 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
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.
Bjrn 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: -
SQLcreate 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
SQLselect 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
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect 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
ORA-1000 and pl/sql cursor cache
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: -
SQLcreate 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
SQLselect 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
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect 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).
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: -
SQLcreate 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
SQLselect 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
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect 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).
Re: ORA-1000 and pl/sql cursor cache
are you looking for the init.ora max_open_cursors(dont think i typed it exactly right).
even if the cursors are cached, they should not be counted as open. they doesnt make
sense from an oracle design standpoint.
From: Lord David [EMAIL PROTECTED]
Date: 2003/11/26 Wed AM 10:34:34 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: ORA-1000 and pl/sql cursor cache
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: -
SQLcreate 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
SQLselect 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
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
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).
