Skip to Main Content
  • Questions
  • pl/sql cursor cache changes in 9.2.0.5

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Alberto.

Asked: April 29, 2004 - 1:05 pm UTC

Last updated: April 21, 2009 - 3:18 pm UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

Hi Tom,

from the 9.2.0.5 patchset release notes:

"PL/SQL Cursor Caching

Prior to release of the 9.2.0.5.0 patch set, the maximum number of cursors that could be cached for fast lookup by PL/SQL was bounded by the value of the init.ora parameter open_cursors. If you currently have open_cursors set to a high value (for example, greater than 1000), it is likely that this is causing large numbers of PL/SQL cursors to be cached in the shared pool. This could lead to issues with memory management, frequent reloading of library cache objects and ORA-04031 errors.

Patch set 9.2.0.5.0 alleviates the issue by changing the init.ora parameter which determines the upper bound for PL/SQL cursor caching from open_cursors to session_cached_cursors.

Most users will not need to modify the value of either of these parameters. If you already have session_cached_cursors set to a value greater than the open_cursors parameter, then this change will have no performance impact upon your system.

However, if you have session_cached_cursors set to zero, or set at a value significantly lower than the open_cursors parameter, and you are concerned that PL/SQL cursors need to be cached for optimal performance, then you should ensure that the session_cached_cursors parameter is increased appropriately.

This issue is bug number 3150705. "

On almost all the instances, we have session_cached_cursors set to a low value and open_cursors to an high value (to let the pl/sql cache cache everything that it wants to). We must definitely increase session_cached_cursors, otherwise the pl/sql cursor cache would be, in practice, disabled.

We have never experienced "issues with memory management, frequent reloading of library cache objects and ORA-04031 errors", so for sure the current level of caching is safe.

What are your thoughts on this - especially, would you play more on the conservative side (say, setting session_cached_cursors to the current number of cursors as seen in v$open_cursor) or on the aggressive one (setting it equal to open_cursors) ?

If the answer is "in between", is there any v$ view (or other) that could assist in selecting a value to use as a starting point for testing ?

thanks in advance!
Alberto

and Tom said...

well, thanks for pointing this out -- this is a fundemental change indeed.

the bug text states:

If SESSION_CACHED_CURSORS is not set then a default of 50 is used.

though. when not set (zero), you'll have FIFTY.

when set (to one for example), PLSQL caching "goes away" and this is very bad indeed.

I'm of the school that plsql cursors *should be cached* (i don't think i like this "fix"). setting session_cached_cursors to 100 is in general a good rule of thumb in general

Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alberto Dell'Era, April 30, 2004 - 7:55 am UTC

I don't think I like this fix too, especially since I can't figure out the mutual interdependencies between the pl/sql and session cursor caches. E.g., say the caches are full, and a new pl/sql cursor needs to be cached - will it replace the coldest *pl/sql* cursor cached (=the caches are managed separately), or the coldest regardless of the type (= the caches are managed together) ?

Anyway - I could easily list 10+ questions about this change, but at the end it boils down to the big one, which may become a few slides of a presentation of yours probably, since it obviously hits your "research interests" : what are the mechanics/performances of the (now tightly coupled or perhaps even the same) cursor caches ?

Big question indeed ...
bye
Alberto



Cache behaviour

Jonathan Lewis, May 01, 2004 - 9:12 am UTC

The strange thing about this change is that I don't think the earlier mechanism could cause a problem. From the test cases I ran, cursors which were "secretly" in the pl/sql cache were closed if the open_cursors limit came under pressure, so if you set open_cursors to 1,000 because you thought your application needed it, then your memory demand was limited (for the session) to 1,000 cursors, and the fact the pl/sql used part of your allowance whenever possible should have been immaterial.


Tom Kyte
May 01, 2004 - 9:19 am UTC

they see to have added it in order to reduce ora-4031's from having too many things pointed to in the shared pool. the cursors were silently closed for real as open_cursors came under stress -- their concern was if you set it to 1,000 or 2,000 (as many <some language here> shops are like to do when they encounter a cursor leak and think it is a database bug, not a code bug) -- you'd potentially have thousands of shared sql's pointed to that wouldn't be flushed. I'm not convinced though that the "fix" was needed.

re: cache behaviour

Mark A. Williams, May 01, 2004 - 11:35 am UTC

> ...<some language here>...

Any door prizes?

> I'm not convinced...

Well, I don't want to go so far as to say that this "fix" would *encourage* continued poor programming practice, but it doesn't do alot to *discourage* it either from the looks of it. No (or reduced) penalty for poor cursor management?

Anyway, perhaps a future patch note will re-address the issue.

You must be joking........!

Connor McDonald, May 02, 2004 - 9:54 am UTC

Hmmmm...gotta be thinking that

a) every Forms 4/5/6/9/10g application out there is following the standard Forms advice of: "set open_cursors to (high) X" because cursors are not closed, and

b) they've got their databases with a "default" session_cached_cursors somewhere in the 0-100 range

and then voila! they move to 9205 and ..... oops!

Tom Kyte
May 02, 2004 - 10:21 am UTC

9205 or 10g actually, same thing in 10g.

I concurr. I'm not a fan of this one. I think it is a fix for a non-existent problem.

cache(s) dimension(s)

Alberto Dell'Era, May 02, 2004 - 1:45 pm UTC

It seems that the upper limit for cached cursors is TWICE the value of 'session_cached_cursors'.

And pl/sql ones are 'privileged', since their cache can grow up to the upper limit, evicting all the session ones, whose cache can grow at most up to the value of 'session_cached_cursors'.

It would be nice if one of you experts could confirm/comment these findings ...

That's something to be considered while tuning this (from now on) very important parameter.

Alberto

Test case (9.2.0.5):

create or replace procedure open_plsql_cursors
is
  l_dummy number;
begin
  -- open 40 pl/sql cursors
  select 01 into l_dummy from dual pl_sql;
  select 02 into l_dummy from dual pl_sql;
  select 03 into l_dummy from dual pl_sql;
  select 04 into l_dummy from dual pl_sql;
  select 05 into l_dummy from dual pl_sql;
  select 06 into l_dummy from dual pl_sql;
  select 07 into l_dummy from dual pl_sql;
  select 08 into l_dummy from dual pl_sql;
  select 09 into l_dummy from dual pl_sql;
  select 10 into l_dummy from dual pl_sql;
  select 11 into l_dummy from dual pl_sql;
  select 12 into l_dummy from dual pl_sql;
  select 13 into l_dummy from dual pl_sql;
  select 14 into l_dummy from dual pl_sql;
  select 15 into l_dummy from dual pl_sql;
  select 16 into l_dummy from dual pl_sql;
  select 17 into l_dummy from dual pl_sql;
  select 18 into l_dummy from dual pl_sql;
  select 19 into l_dummy from dual pl_sql;
  select 20 into l_dummy from dual pl_sql;
  select 21 into l_dummy from dual pl_sql;
  select 22 into l_dummy from dual pl_sql;
  select 23 into l_dummy from dual pl_sql;
  select 24 into l_dummy from dual pl_sql;
  select 25 into l_dummy from dual pl_sql;
  select 26 into l_dummy from dual pl_sql;
  select 27 into l_dummy from dual pl_sql;
  select 28 into l_dummy from dual pl_sql;
  select 29 into l_dummy from dual pl_sql;
  select 30 into l_dummy from dual pl_sql;
  select 31 into l_dummy from dual pl_sql;
  select 32 into l_dummy from dual pl_sql;
  select 33 into l_dummy from dual pl_sql;
  select 34 into l_dummy from dual pl_sql;
  select 35 into l_dummy from dual pl_sql;
  select 36 into l_dummy from dual pl_sql;
  select 37 into l_dummy from dual pl_sql;
  select 38 into l_dummy from dual pl_sql;
  select 39 into l_dummy from dual pl_sql;
  select 40 into l_dummy from dual pl_sql;
end;
/

create or replace procedure open_session_cursors 
is
  l_dummy number;
  l_stmt  varchar2(1000);
begin
  -- open 40 session cursors
  for i in 1..40 loop
    l_stmt := 'select /* session */ ' || i || ' from dual';
    
    -- a session cursor must be parsed at least 3 times to be considered for caching
    for j in 1..10 loop
      execute immediate l_stmt
      into l_dummy;
    end loop;
  end loop;
end;
/

SQL> exit

$ sqlplus dellera/oz

SQL> alter session set session_cached_cursors=15;

Session altered.

SQL> -- show that at most 'session_cached_cursors' session cursors can be cached
SQL> exec /* session */ open_session_cursors;

PL/SQL procedure successfully completed.

SQL> exec /* session */ open_session_cursors;

PL/SQL procedure successfully completed.

SQL> exec /* session */ open_session_cursors;

PL/SQL procedure successfully completed.

SQL>
SQL> select /* probe */ type, count(*)
  2    from (
  3  select
  4         case  when sql_text like '%/* probe */%'   then 'probe'
  5               when sql_text like '%/* session */%' then 'session'
  6                                                    else 'pl/sql'
  7         end as type
  8    from v$open_cursor
  9   where sid = (select sid from v$mystat where rownum=1)
 10         )
 11   group by type;

TYPE                    COUNT(*)
--------------------- ----------
probe                          1
session                       15

SQL>
SQL> -- show that pl/sql cursors can use all of the cache, evicting all cached session cursors
SQL>
SQL> exec open_plsql_cursors;

PL/SQL procedure successfully completed.

SQL> exec open_plsql_cursors;

PL/SQL procedure successfully completed.

SQL> exec open_plsql_cursors;

PL/SQL procedure successfully completed.

SQL>
SQL> select /* probe */ type, count(*)
  2    from (
  3  select
  4         case  when sql_text like '%/* probe */%'   then 'probe'
  5               when sql_text like '%/* session */%' then 'session'
  6                                                    else 'pl/sql'
  7         end as type
  8    from v$open_cursor
  9   where sid = (select sid from v$mystat where rownum=1)
 10         )
 11   group by type;

TYPE                    COUNT(*)
--------------------- ----------
pl/sql                        30
probe                          1

SQL>
SQL>
SQL> -- show that session cursors cannot evict all pl/sql cursors - 
SQL> -- at least 'session_cached_cursors' pl/sql cursors stay there
SQL>
SQL> exec /* session */ open_session_cursors;

PL/SQL procedure successfully completed.

SQL> exec /* session */ open_session_cursors;

PL/SQL procedure successfully completed.

SQL> exec /* session */ open_session_cursors;

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select /* probe */ type, count(*)
  2    from (
  3  select
  4         case  when sql_text like '%/* probe */%'   then 'probe'
  5               when sql_text like '%/* session */%' then 'session'
  6                                                    else 'pl/sql'
  7         end as type
  8    from v$open_cursor
  9   where sid = (select sid from v$mystat where rownum=1)
 10         )
 11   group by type;

TYPE                    COUNT(*)
--------------------- ----------
pl/sql                        15
probe                          1
session                       15
 

Tom Kyte
May 02, 2004 - 4:34 pm UTC

create or replace procedure open_plsql_cursors
as
l_dummy number;
begin
select 1 into l_dummy from dual;
select 2 into l_dummy from dual;
select 3 into l_dummy from dual;
select 4 into l_dummy from dual;
select 5 into l_dummy from dual;
select 6 into l_dummy from dual;
end;
/

alter session set session_cached_cursors = &1;
alter session set sql_trace=true;
exec open_plsql_cursors;
exec open_plsql_cursors;
exec open_plsql_cursors;





If you run that script with 5, you'll see each cursor is parsed 3 times. if you run that with 6, it'll show each cursor is parsed once. so, that blows the 2x theory :)


Use tkprof to see when parses are happening, v$open_cursor isn't going to be useful here.


Alberto Dell'Era, May 02, 2004 - 6:48 pm UTC

Reproduced! You're right :-) - thanks!

Another way to see this is by the last lines of the trace:

session_cached_cursors=5
EXEC #1:c=170245, (snip) <-- exec open_plsql_cursors;
XCTEND rlbk=0, rd_only=1 <-- sqlplus committing before exit
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '

session_cached_cursors=6
EXEC #1:c=120172,(snip) <-- exec open_plsql_cursors;
XCTEND rlbk=0, rd_only=1 <-- sqlplus committing before exit
STAT #2 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #3 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #4 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #5 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #6 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #7 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '

The STAT lines are the cached cursors being closed.

Now I wonder about the meaning of the rows of v$open_cursor ...

thanks again
Alberto

cache architecture

Alberto Dell'Era, May 06, 2004 - 5:08 pm UTC

Is this drawing accurate ?

+----------+ +--------+
| PL/SQL | | PL/SQL | (a)
+-> | engine | --> | cursor | -+
| | | | cache | | +---------+ +--------+
| +----------+ +--------+ | | session | | SQL |
| +--->| cursor |-->| engine |
| | | cache | | |
connection | | +---------+ +--------+
------------+------------>-------------------+

key point is (a) - the PL/SQL engine doesn't bypass the session cursor cache.

I think that that was proven by my test case provided above - after the second run we had

TYPE COUNT(*)
--------------------- ----------
pl/sql 30
probe 1

since session_cached_cursors=15, and

1) you showed that the PL/SQL cache is exactly 15 in size
2) sqlplus doesn't open more than 1 cursor at a time (if not using refcursor bind variables or sql returning cursors as columns), which is 'probe' in this case

the remaining 15 open cursors have to belong to the session cursor cache - and they were opened by PL/SQL for sure.


Tom Kyte
May 06, 2004 - 8:59 pm UTC

well, it is more that the caches would be on the "same level" sort of.

It does seem that the plsql cursors can be in the session cursor cache (in 10g anyhow).

*I THINK* this shows that (hard to show really)


ops$tkyte@ORA10G> @connect /
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter session set session_cached_cursors=5;
 
Session altered.
 
ops$tkyte@ORA10G> SET SERVEROUTPUT OFF
<b>that is very very relevant, without it, (since i have it on by default) sqlplus runs some good bind sql for us... </b>


ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create or replace procedure foo1( x in number )
  2  as
  3          n number;
  4  begin
  5          select 1 into n from dual;
  6          select 2 into n from dual;
  7          select 3 into n from dual;
  8          select 4 into n from dual;
  9          select 5 into n from dual;
 10          select 6 into n from dual;
 11  end;
 12  /
 
Procedure created.
 
ops$tkyte@ORA10G> create or replace procedure foo2( x in number )
  2  as
  3          n number;
  4  begin
  5          select 1 into n from dual x;
  6          select 2 into n from dual x;
  7          select 3 into n from dual x;
  8          select 4 into n from dual x;
  9          select 5 into n from dual x;
 10          select 6 into n from dual x;
 11  end;
 12  /
 
Procedure created.
 
<b>so, anytime foo1/foo2 is run, one the first query will be pushed out of the plsql cache -- but maybe not the session cursor cache.  so every time we run foo1 or foo2 -- all of the sql will be soft parsed since there isn't enough room</b>


ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec foo1(1)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo2(2)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo1(3)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo2(4)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo1(5)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo2(6)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo1(7)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo2(8)
 
PL/SQL procedure successfully completed.
 
<b>just warm it all up, now lets get a baseline</b>
 

ops$tkyte@ORA10G> select 1, a.name, b.value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name in ( 'session cursor cache hits','session cursor cache count' )
  5  /
 
         1 NAME                                VALUE
---------- ------------------------------ ----------
         1 session cursor cache hits               0
         1 session cursor cache count              5
 
<b>no hits yet -- because foo1 after foo2 after foo1 would just slide things all around..</b>


ops$tkyte@ORA10G> exec foo1(9)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo2(10)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo2(101)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select 2, a.name, b.value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name in ( 'session cursor cache hits','session cursor cache count' )
  5  /
 
         2 NAME                                VALUE
---------- ------------------------------ ----------
         2 session cursor cache hits               6
         2 session cursor cache count              5

<b>after foo2 followed by foo2 -- we got 6 hits, because we had the same sql in there right after itself, foo1 didn't get in the way</b>

 
ops$tkyte@ORA10G> exec foo1(11)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo1(111)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec foo2(12)
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select 3, a.name, b.value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name in ( 'session cursor cache hits','session cursor cache count' )
  5  /
 
         3 NAME                                VALUE
---------- ------------------------------ ----------
         3 session cursor cache hits              12
         3 session cursor cache count              5

<b>same thing here I think</b>

but -- i'll leave it open to discussion.


(I *still* don't like this change at all) 

proof for the (a) link in the drawing

Alberto Dell'Era, May 07, 2004 - 6:00 pm UTC

I think I've been able to prove (and in a very simple way!) that the (a) arrow in the drawing is accurate, that is, the pl/sql cursor cache sits 'in front' of the session cursor cache.

create or replace procedure foo_with_tag
is
n number;
begin
SELECT 1 INTO N FROM DUAL TAGGED;
end;
/

The statement that the PL/SQL engine will send to the sql engine is:

SELECT 1 FROM DUAL TAGGED

So, my idea was to preload this statement in the session cursor cache, and then execute the procedure to see whether it would get a 'session cursor cache hit' or not.

The probe is the following statement, executed as a user not equal to DELLERA, the one used for the experiment.

col c1 form a35
select 'SQL : '|| sql_text c1, parse_calls c2, executions c3
from v$sql
where sql_text like '%TAGGED%'
and sql_text not like '%ignoreme%'
union
select 'STAT: '|| a.name, b.value, null ignoreme
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and a.name in ( 'session cursor cache hits','session cursor cache count' )
and b.sid = (select sid from v$session where username = 'DELLERA');

I installed the procedure and exited all sessions, flushing the shared_pool.

sqlplus dellera/u_ur_not_words

The probe tells:
C1 C2 C3
----------------------------------- ---------- ----------
STAT: session cursor cache count 0
STAT: session cursor cache hits 2

Quirk: why hits=2 and count=0 at connection time ? That doen't matter, anyway.

DELLERA> SELECT 1 FROM DUAL TAGGED;

C1 C2 C3
----------------------------------- ---------- ----------
SQL : SELECT 1 FROM DUAL TAGGED 1 1
STAT: session cursor cache count 0
STAT: session cursor cache hits 2

Statement loaded in the shared pool for the first time, no hits.

DELLERA> SELECT 1 FROM DUAL TAGGED;

C1 C2 C3
----------------------------------- ---------- ----------
SQL : SELECT 1 FROM DUAL TAGGED 2 2
STAT: session cursor cache count 0
STAT: session cursor cache hits 2

Still no hits after 2 parses/2 executes

DELLERA> SELECT 1 FROM DUAL TAGGED;

C1 C2 C3
----------------------------------- ---------- ----------
SQL : SELECT 1 FROM DUAL TAGGED 3 3
STAT: session cursor cache count 0
STAT: session cursor cache hits 2

Still no hits after 3 parses/3 executes

DELLERA> SELECT 1 FROM DUAL TAGGED;

C1 C2 C3
----------------------------------- ---------- ----------
SQL : SELECT 1 FROM DUAL TAGGED 4 4
STAT: session cursor cache count 0
STAT: session cursor cache hits 3 <-- cache hit

Statement found in session cursor cache at 4th parse/execute

DELLERA> exec foo_with_tag;

C1 C2 C3
----------------------------------- ---------- ----------
SQL : SELECT 1 FROM DUAL TAGGED 5 5
STAT: session cursor cache count 0
STAT: session cursor cache hits 4 <-- cache hit!

Bingo!

May you (or anyone else interested) try to break this ?

PS Obviously the increase in the v$sql.parse_calls has to be expected - the session cursor caching machinery turns a 'soft parse' into a 'softer soft parse', but a parse nonetheless. A parse is a parse is a parse ... where I've heard this ? ;-)

Tom Kyte
May 10, 2004 - 6:40 am UTC

wonder why the count is zero, but otherwise looks reasonable, yes. since you probed from another session (good idea, just like autotrace does with sqlplus)

explanation for 'session cursor cache count' = 0

Alberto Dell'Era, May 10, 2004 - 7:05 am UTC

> wonder why the count is zero

I wondered too, and yesterday I eventually understood: when the cursor is found in the session cursor cache it is "extracted" from the cache, cannot be "duplicated" as in regular caches.

So the sequence is

1) sqlplus has our cursor opened (SELECT 1 FROM DUAL TAGGED)
2) we enter "SELECT 1 FROM DUAL TAGGED" at the sqlplus prompt
(very same statement) so:
a - the previous cursor is closed by sqlplus
b - the session cursor cache intercepts the close and "kidnaps" the cursor
instead
c - the statement is parsed by sqlplus and immediately found in cache
d - the cursor is extracted from the cache and returned to sqlplus

The 'session cursor cache count' went to 1 after (b), but returned to 0 again after (d); and we read the statistics (from another session) only before (a) and after (d).

Thanks for the info about autotrace - NOW i understand some strange results I had some months ago!

Alberto

"proof" for the (b) link in the drawing

Alberto Dell'Era, May 10, 2004 - 6:31 pm UTC

Drawing verbatim [only (b) added]:

                +----------+     +--------+
                |  PL/SQL  |     | PL/SQL |   (a)
            +-> |  engine  | --> | cursor | -+
            |   |          |     | cache  |  |    +---------+   +--------+  
            |   +----------+     +--------+  |    | session |   | SQL    |
            |                                +--->| cursor  |-->| engine | 
            |                                |    | cache   |   |        |
 connection |                            (b) |    +---------+   +--------+
------------+------------>-------------------+ 


The "proof" for (a) only showed that the PL/SQL cache "sees" the session one, but the caches may be at the same level, with the cursors in the PL/SQL cache available for the connections. This is not the case: if we reply the proof for (a) until the end:

$ sqlplus dellera/azalea

DELLERA> SELECT 1 FROM DUAL TAGGED;
DELLERA> SELECT 1 FROM DUAL TAGGED;
DELLERA> SELECT 1 FROM DUAL TAGGED;
DELLERA> SELECT 1 FROM DUAL TAGGED;
DELLERA> exec foo_with_tag;

And then execute this sql from the probing session:

select sql_text from v$open_cursor
 where sid = (select sid from v$session where username='DELLERA')
   and sql_text like '%TAGGED%';

SQL_TEXT
--------------------------
SELECT 1 FROM DUAL TAGGED

We already know that the cursor is held open in the PL/SQL cache (it was opened,fetched and then closed by foo_with_tag) so this is expected. Now:

DELLERA> SELECT 1 FROM DUAL TAGGED;

SQL> select sql_text from v$open_cursor
  2   where sid = (select sid from v$session where username='DELLERA')
  3     and sql_text like '%TAGGED%';

SQL_TEXT
-----------------------------------------------------------------------
SELECT 1 FROM DUAL TAGGED
SELECT 1 FROM DUAL TAGGED

So the available PL/SQL cursor was not reused - another one was opened instead.

Would you concurr ? It seems ok, but I would like to know your opinion if possible, before using this 'model' as a basis for testing values for session_cached_cursors in 9.2.0.5 (and i hope that you find this interesting too ... ). 

Tom Kyte
May 10, 2004 - 8:59 pm UTC

Interesting, plsql kidnapped it. makes sense, since plsql keeps it open, it is "it's cursor" now -- no more parsing going with it (until plsql is forced to release it)

RE: sqlplus dellera/u_ur_not_words

Mark A. Williams, May 10, 2004 - 9:58 pm UTC

"sqlplus dellera/u_ur_not_words"

that was a hidden gem.

- Mark

Tom Kyte
May 11, 2004 - 8:12 am UTC

doh, didn't even catch that one :)

proof for the (c) link in the drawing ...

Alberto Dell'Era, May 11, 2004 - 6:35 pm UTC

... no I'm joking ;-)

But dead seriously - thanks for your help and feedback on my ruminations, you can't imagine how much that helped.
Now that I'm confident that the drawing is correct "above any reasonable doubt", I can start experimenting to address the real big question - how much is the session cursor cache scalable (the pl/sql cursor cache being 'perfectly scalable', if my intuition on how it's implemented is correct). The impact of this 9.2.0.5 change all boils down to that.
(I've heard rumours that in 9i it's much more scalable btw).

tnx again
Alberto

PS I use "btw" and "tnx" as historical usenet abbreviations protected by UNESCO - the other keys are not stuck ;-)

very interesting thread

A reader, July 24, 2004 - 2:00 pm UTC

have some questions/observations:
1. "If you run that script with 5, you'll see each cursor
is parsed 3 times. if you run that with 6, it'll show
each cursor is parsed once. so, that blows the 2x
theory :)"

I did not quite understand this - how do these
observations blow away the theory that "the upper limit
for cached cursors is TWICE the value of
'session_cached_cursors'"?

2. This is on alberto's observation:
"Another way to see this is by the last lines of the trace:

session_cached_cursors=5
EXEC #1:c=170245, (snip) <-- exec open_plsql_cursors;
XCTEND rlbk=0, rd_only=1 <-- sqlplus committing before exit
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '

session_cached_cursors=6
EXEC #1:c=120172,(snip) <-- exec open_plsql_cursors;
XCTEND rlbk=0, rd_only=1 <-- sqlplus committing before exit
STAT #2 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #3 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #4 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #5 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #6 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '
STAT #7 id=1 cnt=3 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL DUAL '

The STAT lines are the cached cursors being closed."

I did not quite follow this observation...what in the stat line indicates that they show cursors being closed and
how does the above blow the 2x theory again?



3. "create or replace procedure foo2( x in number )
2 as
3 n number;
4 begin
5 select 1 into n from dual x;"

This is the first time i saw a parameter being
used as a tag (is that what is going on here? -the
parameter x being used as a tag for table dual?


3. This is related to alberto's comment:
"I wondered too, and yesterday I eventually understood: when the cursor is found
in the session cursor cache it is "extracted" from the cache, cannot be
"duplicated" as in regular caches."

If that is the case, wont the cursor be removed from
the cache each time it is read?

Thanx!


Tom Kyte
July 24, 2004 - 9:08 pm UTC

1) there was the comment:

It seems that the upper limit for cached cursors is TWICE the value of
'session_cached_cursors'.

so I setup a procedure that would parse 6 cursors. if you had session-cached-cursors = 5 and ran that procedure 3 times -- each cursor was parsed 3 times. if you set that parameter to 6, they were parsed once.

that shows that is was not 2xsession-cached-cursors -- if it were, both 5 and 6 would result in "the same number of parses" since the caching would have been 10 and 12 respectively.

it must have been "5" for the first run since 6 cursors in the cache would have aged out the oldest. the next run would parse the first -- therefore putting it back in the cache (removing the second), the second would remove the third and so on.


2) that observation is based on the knowledge that stat records for a query are written out only when the cursor is closed -- so he is seeing that 5 and then 6 cursors are closed respectively.


3) yes, no, sort of.

the session cursor cache is a pointer to the shared pool in effect. if you really open a cursor -- it is a hard pointer, dependent on the shared pool. if you just have it in the cursor cache, it is just sort of pointing there -- in the hopes it remains there.


thank you!

A reader, July 25, 2004 - 1:02 am UTC


pl/sql cursor cache

A reader, July 31, 2004 - 6:56 pm UTC

Hi Tom
One quick question.
If there is a cache hit in pl/sql cursor cache, we
avoid a soft parse completely. However suppose
the schema has changed during an implicit for loop
(that uses pl/sql cursor cache) - in this case, suddenly
the cursor can become invalidated. Does not this imply
that PL/SQL has to *always* go to the shared pool to check if the cache is valid or not (just like in the case of session cache cursors)? Which means that it can not avoid
a soft parse (just like session cursor cache can not
avoid a soft parse) which we know is not true for static
cursors (excluding ref cursors.) Any thoughts?

My guess is that the pl/sql cache is very specific
to the PL/SQL VM and the cache is under its control.
Thus it takes care to invalidate the cursor somehow
when a schema changes (or something else that effects
the plan changes) Thus as long as the cursor is there
in the cache and is valid, it does not have to do the
shared pool check..


Thank you!

Tom Kyte
July 31, 2004 - 7:13 pm UTC

the ddl would have invalidated the cursor, plsql (and anything really) would have been notified that way and under the covers, another parse would silently take place.

the plsql cache can be done by you! in all of your code! It is called "parse once, execute many!!!"

java can do it, vb can do it, c can do it, anyone can do it.

conn.setAutoCommit(false);
Statement stmt = conn.createStatement ();
stmt.execute( "alter session set sql_trace=true" );

PreparedStatement pstat =
conn.prepareStatement
("select ename from emp where ename like ?" );

System.out.println( "------------------------" );
pstat.setString( 1, "%A%" );
ResultSet rset = pstat.executeQuery();
while( rset.next() ) System.out.println( rset.getString(1) );
rset.close();

System.out.println( "------------------------" );
pstat.setString( 1, "%B%" );
rset = pstat.executeQuery();
while( rset.next() ) System.out.println( rset.getString(1) );
rset.close();

stmt.execute( "alter table emp add x number" );

System.out.println( "------------------------" );
pstat.setString( 1, "%A%" );
rset = pstat.executeQuery();
while( rset.next() ) System.out.println( rset.getString(1) );
rset.close();

System.out.println( "------------------------" );
pstat.setString( 1, "%B%" );
rset = pstat.executeQuery();
while( rset.next() ) System.out.println( rset.getString(1) );
rset.close();

stmt.execute( "alter table emp drop column x" );

System.out.println( "------------------------" );
pstat.setString( 1, "%A%" );
rset = pstat.executeQuery();
while( rset.next() ) System.out.println( rset.getString(1) );
rset.close();

System.out.println( "------------------------" );
pstat.setString( 1, "%B%" );
rset = pstat.executeQuery();
while( rset.next() ) System.out.println( rset.getString(1) );
rset.close();

will show:

select ename
from
emp where ename like :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.01 0.00 0 2 0 0
Fetch 6 0.00 0.00 0 18 0 24
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.01 0.00 0 20 0 24

Misses in library cache during parse: 0
Misses in library cache during execute: 2
<<== it fixes itself
Optimizer goal: CHOOSE
Parsing user id: 92


thanx!

A reader, July 31, 2004 - 7:34 pm UTC

"the ddl would have invalidated the cursor, plsql (and anything really) would
have been notified that way and under the covers, another parse would silently
take place."
That is what I thought but put it in a wrong fashion:)

Now the question is why cant the same mechanism be used
by the session cursor cache? It can also
use the same mechanism wherein it gets notified
if its cursor becomes invalid. Thus instead of
making the soft parse "softer" it can make it
non-existent!:)

My guess is that this may not be feasible since the session cache stores things such as ref cursors which are more tricky to cache (since they are pointers.)

What are your thoughts?

Regarding parse once execute many - I am aware
of that...In many cases it can avoid
soft parses completely like you said. In cases
where it becomes cumbersome (say due to complexity
of products with cursors sprinkled all over), it
can still be achieved by features such as
statement caching in JDBC and other similar
APIs in OCI/ODBC etc.

Thanx for a great resource on the web!
I have another question for you - have you considered
yourself for the guinees book of world records for max
number of questions answered by a single person
during the period of what 4 years? - I am sure
you will qualify for it! (I am serious!)

Regards

Tom Kyte
August 01, 2004 - 10:16 am UTC

the session cursor cache would have to become infinitely more sophisticated to go that level.

the plsql cursor cache - when pressured for space - truly closes a cursor to make room. It also implies a certain amount of memory (resources) are maintained in both the client as well as the server side (cga memory -- in the uga). session cached cursors is a good compromise -- the softer soft parse.




thanx!

A reader, August 01, 2004 - 4:53 pm UTC

"the session cursor cache would have to become infinitely more sophisticated to go that level."

Agreed - it stores all kinds of cursors including
ref cursors - and if it has to cache with the intent
of avoiding soft parses then the logic does become
quite complicated (perhaps for the same reason
pl/sql cache does not store ref cursors - I know that
they are pointers, can escape to clients etc, but still
the main reason could be that they complicate the
cache mechanism.) I think the most important
reason for not providing this feature in session cache
is that it is provided by APIs such as JDBC/OCI etc...




A reader, August 30, 2004 - 4:13 pm UTC


A reader, August 30, 2004 - 4:30 pm UTC


Rehash of thread?

bob, October 13, 2004 - 9:58 am UTC

Tom,

Could you summarize the lessons learned here with regards to the original behavior and the change in your words of the session_cached_cursor and open_cursors parameter? I have read this thread a few times and it isn't sinking in very well. It seems like there was some discoveries along the way about the whether this change was needed and what it really means that I am not clear about.

Also, I believe you have advocated that the JDBC folks to cache their own preparedStatements across invocations if possible for optimum performance to avoid the softparse altogether. Can/Should this same thing be done in pl/sql to avoid the soft parse for dbms_sql the first time. Or is that what session_cached_cursors (the softer soft parse) is for?

Tom Kyte
October 13, 2004 - 10:20 am UTC

plsql caches STATIC SQL cursors for us. So, if you have a procedure:


create procedure p
as
begin
for x in ( select * from t ) loop......


and you called "p" 50 billion times -- the query "select * from t" would be parsed ONLY ONCE for your session....

ASSUMING, we didn't run out of cursors of course. What I mean was, support open_cursors was set to 100. When you call this plsql routine -- at least 2 cursors would be opened (the client opens "begin p; end;", the procedure p opens "select * from t")....

Now, say your application opens another 99 cursors -- 99+2 = 101, something has to give so PLSQL would give up this "select * from t" and it would be parsed next time around again (silently, nothing needs be done).

The size of the cache of cursors plsql would keep "open" for us would be based on open_cursors -- 100 in this case.


Now, in 9205 -- the size of this cache is based on session_cached_cursors -- NOT open_cursors. So, if you have open_cursors = 1000, session_cached_cursors = 100 in 9205, plsql will cache at most 100 cursors, not 1000. Now, these cached cursors might be closed in the server on us still even though plsql is caching them (hence the "dubious" perhaps need for this) but in general, this plsql cursor caching is very beneficial. in 9205 and above, you might well see your soft parse count start to go up in large applications -- you would have to set session cached cursors up in order to alleviate that.


In jdbc 3.0, the jdbc api lets the coder set a flag on the connection/statement indicating whether statements should be transparently cached. It would be my advice to set it ON at the connection level and turn it off on statements that are known to be "one off, never reused" -- this will work for connection pools and the like nicely. It will dramatically reduce the soft parse count.


Isn't this a bug fix?

Arun Gupta, October 13, 2004 - 1:36 pm UTC

Tom,
From the Oracle Reference manual, if I go by the definitions, then:
================================
OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.
=================================
and:
SESSION_CACHED_CURSORS lets you specify the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor.
===================================

Doesn't it look like Oracle has made the behaviour in line with documentation?

Thanks

Tom Kyte
October 13, 2004 - 2:58 pm UTC

No, plsql cached cursors are not the same as session cached cursors at all -- they predate that init.ora parameter by many many many releases. different concept all together. session_cached_cursors is for 3gls that do explicit cursor mgmt.

open_cursors and session_cached_cursors

A reader, January 22, 2005 - 4:54 am UTC

Hi Tom,
On a 10g system, let's say open_cursors = 1000, session_cached_cursors = 50.

Question 1: If a 3GL opens up 1000 cursors, can PL/SQL still open up 50 more? Or will PL/SQL get an ORA-1000 error when it tries to open a cursor? In other words, is the actual maximum number of open cursors = open_cursors + session_cached_cursors? Or is the maximum number of open cursors simply = open_cursors, regardless of whether the cursor is a 3GL cursor or a PL/SQL cursor? I would guess the latter, but don't have a 10g system to test it on.

Question 2: Can open_cursors in 10g still "steal a cursor" from the PL/SQL cursor cache, or did that behavior stop in conjunction with the recent change in the effects of the session_cached_cursors parameter? For example, if PL/SQL opens up 50 cursors, and then a 3GL opens up 950 cursors, what happens when the 3GL tries to open up the 951st cursor? Does the 3GL steal a cursor from the PL/SQL cursor cache? Or does the 3GL get an ORA-1000 error?

Question 3: Is it correct to state: The PL/SQL cursor cache and the session cursor cache are two separate caches. Both caches are controlled by the same parameter, session_cached_cursors. However, the value of session_cached_cursors applies separately to both caches.
If session_cached_cursors = 50, there can be 50 open cursors in the PL/SQL cursor cache. Simultaneously, there can be 50 cursors in in the session cursor cache.
In other words, the session_cached_cursors value of 50 does is not a limit on the sum of the cursors in the PL/SQL cursor cache + the cursors in the session cursor cache. Rather, it means that each cache can independently and simultaneously contain up to 50 cursors.

Thanks!!

Tom Kyte
January 22, 2005 - 10:00 am UTC

question 1: no. plsql will get ora-1000 due to the fact that the 3gl which does not parpicipate in Oracles cursor cache has all of the slots filled up with it's stuff.

the actual max is open_cursors. it is the hard limit.

question 2: it still "steals"

3gl will steal a slot.


question 3: yes, it is correct to state. However, the size of the plsql cursor cache (which is a more direct link to the cursor than session cache cursors is) is now controlled by the value you set in session cached cursors.

plsql_cached_cursors

A reader, January 22, 2005 - 4:19 pm UTC

So if open_cursors = 200 and session_cached_cursors = 500,
the maximum number of cursors in the pl/sql cursor cache is 200. And I presume the maximum number of cursors in the session cursor cache is 500. That is, the session cursor cache is not constrained by open_cursors.

Rant/opinion: This "fix" should have been implemented as a separate parameter (such as "PLSQL_CACHED_CURSORS") instead of annexing and confusing the session_cached_cursors parameter. The issue is that it is presently impossible
to set "PLSQL_CACHED_CURSORS" and session_cached_cursors to different values, which is exactly what I'd like to do. For example, 100 or lower for session_cached_cursors, and 300 or higher for "PLSQL_CACHED_CURSORS".


Clarification on Alberto's original question

Kari, May 02, 2005 - 4:29 pm UTC

In Alberto's original question, he asked if there were some v$ that could be used to determine a good starting point for setting session_cached_cursors. Or is this it?
select 'STAT: '|| a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and a.name in ( 'session cursor cache hits','session cursor cache count' )
and b.sid = (select sid from v$session where username = '<myuser>');

We are upgrading to 9.2.0.5 and currently have open_cursors set to 1000 and session_cached_cursors set to 50.

Thanks




Tom Kyte
May 03, 2005 - 7:29 am UTC

you'll most likely want to bump up session_cached_cursors to about 100.

collect your soft parse counts now (before upgrade) and after and see if you see a large jump, if you do, you might need to bump it higher.

open_cursors

reader, May 10, 2005 - 11:18 pm UTC

In summary, from 9.2.0.5 onwards, one needs to set open_cursors to an appropriate value and no need to worry about session_cached_cursors parameter?

Tom Kyte
May 10, 2005 - 11:35 pm UTC

*no*

not at all..


open_cursors always needed to be set appropriately

and in 9205 and up, sesssion_cached_cursors controls the size of the plsql cursor cache AS WELL as the session cached cursor cache for all other cursors.

Can't get it

A reader, November 03, 2005 - 4:48 pm UTC

HI Tom I don't understand the difference.

reading the documentation this paragraph moved from open_cursor on 9i to session_cached curors on 10g
This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user."

New features says
"Change in Behavior for SESSION_CACHED_CURSORS
In previous Oracle Database releases, the number of SQL cursors cached by PL/SQL was determined by the OPEN_CURSORS initialization parameter. Starting with Oracle Database 10g release 10.1, the number of cached cursors is determined by the SESSION_CACHED_CURSORS initialization parameter."

Now what does it means, if for example I had set on 9i my parameter in this way, what I had to change.
session_cached_cursors=300
open_cursors=800

What I understand is the following
Oracle says "to optimize the size of the PL/SQL cursor cache, now session_cursor_cache determinse that size. But open_cursors still defines the maximum number of open cursors"

Thanks

Tom Kyte
November 04, 2005 - 2:54 am UTC

it means that before 9205 of Oracle - open_cursors was the size of the plsql cursor cache.

In 9205 and above, session_cached_cursors controls the size of this cache.


You "had to change" nothing. You have to understand that in 9205 - with session_cached_cursors - plsql would cache 300 cursors, not 800 as it had in 9204 and before.

., November 04, 2005 - 7:37 am UTC

Thanks Tom, that is what I thought.

what is _close_cached_open_cursors

kiko, August 30, 2007 - 5:47 am UTC

Hi

I just came to a new job and I noticed in one of database where a full PL/SQL packaged application runs _close_cached_open_cursors is set to TRUE.

The database is 9.2.0.6. I wonder what does this parameter do?

I also would like to know the memory pressure of having many open cursors. For example each cursor can consume 300 bytes in the Shared pool and PGA?
javascript:doSubmit('SUBMIT_REVIEW')
Submit Review
Thanks
Tom Kyte
September 04, 2007 - 5:00 pm UTC

that parameter makes our performance "bad" as you have it set.

you control the number of cursors plsql will cache by setting session_cached_cursors (in 9205 and above, open_cursors in 9204 and before).

this should (in my opinion) NOT BE SET - unless you were told to set it by support.

it causes plsql to actually close all cursors everytime you commit, forcing it to reopen them afterwards.

check this code out:


drop table t;

create table t as select * from dual;
@trace

alter session set "_close_cached_open_cursors" = false;
begin
    for i in 1 .. 1000
    loop
        for x in (select * from t set_false)
        loop
            null;
        end loop;
        commit;
    end loop;
end;
/

alter session set "_close_cached_open_cursors" = true;
begin
    for i in 1 .. 1000
    loop
        for x in (select * from t set_true)
        loop
            null;
        end loop;
        commit;
    end loop;
end;
/


SELECT * FROM T SET_FALSE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute   1000      0.05       0.03          0          0          0           0
Fetch     1000      0.03       0.03          0       3000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.09       0.07          0       3001          0        1000

********************************************************************************
SELECT * FROM T SET_TRUE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1000      0.03       0.02          0          1          0           0
Execute   1000      0.03       0.03          0          0          0           0
Fetch     1000      0.04       0.04          0       3000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3000      0.11       0.10          0       3001          0        1000




see the parse counts - if you use something that shows latching (latches are locks = serialization devices = inhibit your ability to SCALE and do things concurrently), you'll see how bad this is....



open_cursors & session_cached_cursors

A reader, April 20, 2009 - 8:19 am UTC

Hi Tom,

Thanks for your support to Oracle community. I had a question for you about open_cursors. We have just applied 9.2.0.8 patch to one of our production databases. The database was initially running on 9.2.0.4. 

We had following parameter set in init.ora for the database when it was running on 9.2.0.4. 
OPEN_CURSORS=2000
SESSION_CACHED_CURSORS=500

Above values were unchanged after applying 9.2.0.8 patch. The patch was applied 2 days ago. This morning we got ORA-4031 in the database. To resolve the problem we increased LARGE_POOL_SIZE parameter value from 150M to 350M and we also reduced value to OPEN_CURSOR to 800 (From 2000). The problem was resolved by the changes we performed but I think this is just a temporary solution. 

I would like to fix the problem properly without impacting application performance. Meanwhile I got a query from one of the Metalink Notes and had a question for you...

21:48:49 SQL> l
  1  select
  2    'session_cached_cursors'  parameter,
  3    lpad(value, 5)  value,
  4    decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
  5  from
  6    ( select
  7        max(s.value)  used
  8      from
  9        v$statname  n,
 10        v$sesstat  s
 11      where
 12        n.name = 'session cursor cache count' and
 13        s.statistic# = n.statistic#
 14    ),
 15    ( select
 16        value
 17      from
 18        v$parameter
 19      where
 20        name = 'session_cached_cursors'
 21    )
 22  union all
 23  select
 24    'open_cursors',
 25    lpad(value, 5),
 26    to_char(100 * used / value,  '990') || '%'
 27  from
 28    ( select
 29        max(sum(s.value))  used
 30      from
 31        v$statname  n,
 32        v$sesstat  s
 33      where
 34        n.name in ('opened cursors current', 'session cursor cache count') and
 35        s.statistic# = n.statistic#
 36      group by
 37        s.sid
 38    ),
 39    ( select
 40        value
 41      from
 42        v$parameter
 43      where
 44        name = 'open_cursors'
 45*   )
21:59:00 SQL> /

PARAMETER              VALUE USAGE
---------------------- ----- -----
session_cached_cursors   500  100%
open_cursors             800   90%

As you can see, session_cached_cursors is 100% used while open_cursors is 90% used. Does it mean that if open_cursor will reach to 100%, the application will start producing error as it will not be able to open any new cursor as all 800 cursors are open???

I am right now checking SQLs with do not use BIND variables. Do you know any way to ageout SQLs from SHAREDPOOL which are not using bind variables?

Once again thanks for your help...

Cheers,



Tom Kyte
April 21, 2009 - 1:43 pm UTC

I don't know why you increased the large pool AND changed parameters for the shared pool - since the ora-4031 would have been for one or the other but not both??



if the application tries to have more than 800 concurrently opened cursors, it will receive an error message - yes.

... Do you know any
way to ageout SQLs from SHAREDPOOL which are not using bind variables?
...

no, they do not look any different to us than ones with bind variables - it is all just sql.

However, that said, they will tend to age themselves out - since they are not reused, they are typically used ONCE and then go away

open_cursors & session_cached_cursors

A reader, April 21, 2009 - 6:09 am UTC

Hi Tom,

Just had an another question for you...

Can you please inform at what time session_cached_cursors will start aging out from the session? Is it when number of open cursors are close to open_cursors parameter?

Cheers
Tom Kyte
April 21, 2009 - 3:18 pm UTC

session_cached_cursors does two things

a) controls the size of the plsql cursor cache
b) controls the size of a client side cache of pointers into the SGA


more on (a)

when you code in plsql and close a cursor, plsql will NOT CLOSE IT (thankfully, this is good). It'll keep it open in a cache of opened cursors and then the next time you say "open cursor" (implicitly, explicitly, whatever), plsql will see if it is already open and just use it - this cuts down MASSIVELY on parsing (hugely, this is GREAT)


ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3          cursor c is select * from dual;
  4          l_rec dual%rowtype;
  5  begin
  6          open c;
  7          fetch c into l_rec;
  8          close c;
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  p;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SELECT *
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute   1000      0.06       0.05          0          0          0           0
Fetch     1000      0.07       0.08          0       3000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.14       0.15          0       3000          0        1000


so, plsql parsed once, executed 1000 times (perfection). The size of this plsql cache is controlled by session cached cursors (since 9205, prior to that, it was controlled by open cursors).

If your application runs out of cursors (say you have session cached cursors set to 100, open cursors set to 150, say plsql has 100 cached cursors - say you have in your client 50 open cursors - you have 150 total open cursors), you try to open one more cursor in your application - instead of failing - PLSQL will close one of the 100 (plsql will have 99 cached) and your client will have 51 open cursor handles. So this plsql cursor cache is

1) SAFE
2) EFFICIENT
3) a really good thing



Now, for (b) - the client side cache. With session cached cursors when your client closes a cursor, we "close" it - but retain a pointer into the SGA as to where it 'was'. If you open a query later, we'll search a list in the client and see if we have a pointer for that query. If so, we'll latch into the SGA based on that point and see if the copy of the query in the SGA is still usable by us - if the query in the SGA is good to go, we execute it (this is a softer soft parse) - else, if the copy in the SGA isn't relevant for us (we changed a session setting like optimizer goal, the query was aged out, whatever) we go through the entire parse.