conceptually speaking - we have the "pointer to it" in the shared pool. If you re-open that cursor, we dereference the pointer and see if what used to be pointed to is still there.

that is the level to which we need to know - that we keep a pointer to it, and when you re-open it, we look quick in the shared pool to see if it is the same cursor as before.

sql_id's and hashes are involved - as are hundreds of other bits of data.

forget sqlplus for a moment, it is just a simple command line tool, a program, a client, something you could have written yourself.

In plsql - oracle caches cursors for us, when you say "close cursor" (either implicitly as you did above with the cursor for loop - by exiting the loop, or explicitly using close C;) plsql says "yeah, sure, you'll be back, i'll just keep this open for now" - it caches them for us transparently.

That example of yours "keeps the cursor open", you need do nothing (that is the beauty of writing your code in plsql, so many optimizations are part of the language.


In a program outside of the database, that is where you can do this - and you would just not "close" the prepared statement handle and just reuse it over and over.

1) as best as it can, yes.

3) wrong, it'll cache that - it is rather easy to SEE. consider:

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure foo
  2  as
  3  begin
  4          insert into t values ( 1, 2 );
  5          update t set x = 5;
  6          delete from t;
  7          merge into t using (select 1 x, 2 y from dual) d on (t.x = d.x) when matched then update set y = d.y;
  8
  9          execute immediate 'insert into t values ( 100, 200 )';
 10          execute immediate 'update t set x = 500';
 11          execute immediate 'delete from t where 1=1';
 12          execute immediate 'merge into t using (select 10 x, 20 y from dual) d on (t.x = d.x) when matched then update set y = d.y';
 13  end;
 14  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> exec foo

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec foo

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec foo

tkprof will show 1 parse, 3 executes - all cached in this case

INSERT INTO T VALUES ( 1, 2 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          1         23           3
********************************************************************************
UPDATE T SET X = 5

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      3      0.00       0.00          0         21          3           3
********************************************************************************
DELETE FROM T

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      3      0.00       0.00          0         21          6           3
********************************************************************************
MERGE INTO T USING (SELECT 1 X, 2 Y FROM DUAL) D ON (T.X = D.X) WHEN MATCHED
  THEN UPDATE SET Y = D.Y

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      3      0.00       0.00          0         21          0           0
********************************************************************************
insert into t values ( 100, 200 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          1          5           3
********************************************************************************
update t set x = 500

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      3      0.00       0.00          0         21          3           3
********************************************************************************
delete from t where 1=1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      3      0.00       0.00          0         21          6           3
********************************************************************************
merge into t using (select 10 x, 20 y from dual) d on (t.x = d.x) when
  matched then update set y = d.y

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          2          0           0
Execute      3      0.00       0.00          0         21          0           0

interesting, I didn't see that.

looks like an oversight - they are only caching insert/update/delete..

i filed bug 7831098

100 is a pretty normal setting for session_cached_cursors.

This parameter does two things

a) it controls the size of the plsql cursor cache. When you have a "close cursor" (be it implicit or explicit) in plsql, plsql doesn't really close the cursor - it keeps it open. The size of the cache that plsql keeps open is controlled by session_cached_cursors.


b) it controls how many cursors in other languages outside of plsql would be cached in a state that makes a 'softer soft parse' possible.


While you could increase it to cache all cursors an application would have open, that wouldn't be good as the number goes up and up and up. You'll need more client memory (that is where the (b) cache is kept) and you might experience shared pool issues (larger shared pool) due to (a) and (b). Also, the larger the client cache, the long it will take to parse as we have to search this cache before parsing anything.


100 (low 100's) is a pretty good setting