Sure, here was the script I ran, using my runstats framework. The goal was to use this to show that session_cached_cursors reduces the work done, reduces the LATCHES used by heavy recursive sql processing. I use a NOCACHE sequence to force artifically high recursive SQL:
ops$tkyte@ORA920> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.
ops$tkyte@ORA920> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;
View created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> drop sequence seq;
Sequence dropped.
ops$tkyte@ORA920> create sequence seq nocache;
Sequence created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter system flush shared_pool;
System altered.
ops$tkyte@ORA920> /*
DOC>now, we'll see about tons of recursive sql. one easy way to see that
DOC>will be with a sequence that is not cached (updates to seq$ --
DOC>tons of them)...
DOC>*/
ops$tkyte@ORA920> pause
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5
6 l_seq number;
7 begin
8 insert into run_stats select 'before', stats.* from stats;
9
10 l_start := dbms_utility.get_time;
11 execute immediate 'alter session set session_cached_cursors=0';
12 for i in 1 .. 10000
13 loop
14 select seq.nextval into l_seq from dual;
15 end loop;
16 l_run1 := (dbms_utility.get_time-l_start);
17 dbms_output.put_line( l_run1 || ' hsecs' );
18
19 insert into run_stats select 'after 1', stats.* from stats;
20 l_start := dbms_utility.get_time;
21 execute immediate 'alter session set session_cached_cursors=100';
22 for i in 1 .. 10000
23 loop
24 select seq.nextval into l_seq from dual;
25 end loop;
26 l_run2 := (dbms_utility.get_time-l_start);
27 dbms_output.put_line( l_run2 || ' hsecs' );
28 dbms_output.put_line
29 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
30
31 insert into run_stats select 'after 2', stats.* from stats;
32 execute immediate 'alter session set session_cached_cursors=0';
33 end;
34 /
1620 hsecs
1466 hsecs
run 1 ran in 110.5% of the time
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> pause
ops$tkyte@ORA920>
ops$tkyte@ORA920> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 and abs( (c.value-b.value)-(b.value-a.value)) > 250
12 order by abs( (c.value-b.value)-(b.value-a.value))
13 /
NAME RUN1 RUN2 DIFF
------------------------------ ---------- ---------- ----------
STAT...buffer is not pinned co 10354 10084 -270
unt
STAT...session logical reads 70823 70533 -290
LATCH.enqueue hash chains 40620 40928 308
STAT...consistent gets 40459 40132 -327
LATCH.session allocation 535 1216 681
STAT...recursive calls 151473 150566 -907
LATCH.cache buffers chains 194203 195242 1039
STAT...redo size 7267048 7268796 1748<b>
STAT...session cursor cache hi 0 10004 10004</b>
ts
LATCH.library cache pin 121182 100900 -20282
LATCH.shared pool 71584 51083 -20501
LATCH.library cache pin alloca 40792 692 -40100
tion
LATCH.library cache 162211 111753 -50458
STAT...session pga memory max 65536 0 -65536
14 rows selected.
ops$tkyte@ORA920> select run1, run2, diff, round( run1/run2*100, 2 ) run1_pct_of_run2
2 from (
3 select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
4 sum( (c.value-b.value)-(b.value-a.value)) diff
5 from run_stats a, run_stats b, run_stats c
6 where a.name = b.name
7 and b.name = c.name
8 and a.runid = 'before'
9 and b.runid = 'after 1'
10 and c.runid = 'after 2'
11 and (c.value-a.value) > 0
12 and (c.value-b.value) <> (b.value-a.value)
13 and a.name like 'LATCH%'
14 )
15 /
RUN1 RUN2 DIFF RUN1_PCT_OF_RUN2
---------- ---------- ---------- ----------------
784762 656052 -128710 119.62
<b>so, that seems to nail it right? got 10,000 cached cursor hits -- those are my updates to seq$ that are happening. Got lots less latches too -- all well and fine.
Well, Jonathan ran the test like this</b>
ops$tkyte@ORA920> alter system flush shared_pool;
System altered.
ops$tkyte@ORA920> /*
DOC>now, we'll see about tons of recursive sql. one easy way to see that
DOC>will be with a sequence that is not cached (updates to seq$ --
DOC>tons of them)...
DOC>*/
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5 l_seq number;
6 begin
7 insert into run_stats select 'before', stats.* from stats;
8
9 l_start := dbms_utility.get_time;
10 execute immediate 'alter session set session_cached_cursors=0';
11 for i in 1 .. 10000
12 loop
13 select seq.nextval into l_seq from dual;
14 end loop;
15 l_run1 := (dbms_utility.get_time-l_start);
16 dbms_output.put_line( l_run1 || ' hsecs' );
17 end;
18 /
1803 hsecs
PL/SQL procedure successfully completed.
<b>see, broke it into 2 bits here</b>
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5 l_seq number;
6 begin
7 insert into run_stats select 'after 1', stats.* from stats;
8 l_start := dbms_utility.get_time;
9 execute immediate 'alter session set session_cached_cursors=100';
10 for i in 1 .. 10000
11 loop
12 select seq.nextval into l_seq from dual;
13 end loop;
14 l_run2 := (dbms_utility.get_time-l_start);
15 dbms_output.put_line( l_run2 || ' hsecs' );
16 dbms_output.put_line
17 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
18
19 insert into run_stats select 'after 2', stats.* from stats;
20 execute immediate 'alter session set session_cached_cursors=0';
21 end;
22 /
1654 hsecs
run 1 ran in % of the time
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 and abs( (c.value-b.value)-(b.value-a.value)) > 250
12 order by abs( (c.value-b.value)-(b.value-a.value))
13 /
NAME RUN1 RUN2 DIFF
------------------------------ ---------- ---------- ----------
STAT...buffer is not pinned co 10346 10075 -271
unt
STAT...free buffer requested 5 298 293
LATCH.library cache load lock 308 12 -296
STAT...session logical reads 70793 70497 -296
STAT...bytes sent via SQL*Net 300 0 -300
to client
STAT...consistent gets 40446 40120 -326
LATCH.enqueue hash chains 40992 40626 -366
LATCH.cache buffers lru chain 263 784 521
LATCH.checkpoint queue latch 993 1539 546
LATCH.child cursor hash table 744 90 -654
STAT...recursive calls 151407 150535 -872
STAT...bytes received via SQL* 1106 0 -1106
Net from client
LATCH.row cache enqueue latch 21520 20210 -1310
STAT...CPU used by this sessio 1532 0 -1532
n
STAT...CPU used when call star 1532 0 -1532
ted
LATCH.row cache objects 61794 60226 -1568
LATCH.library cache pin alloca 43698 40528 -3170
tion
LATCH.library cache pin 126892 120692 -6200
LATCH.cache buffers chains 201966 195086 -6880
LATCH.shared pool 78339 70891 -7448
LATCH.enqueues 128 10046 9918
LATCH.session idle bit 11 10004 9993
LATCH.session allocation 35296 21060 -14236
STAT...session pga memory max 65536 0 -65536
STAT...redo size 7267188 8668040 1400852
25 rows selected.
<b>See we are missing the cached cursor hits, we didn't get them and worse:</b>
ops$tkyte@ORA920> select run1, run2, diff, round( run1/run2*100, 2 ) run1_pct_of_run2
2 from (
3 select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
4 sum( (c.value-b.value)-(b.value-a.value)) diff
5 from run_stats a, run_stats b, run_stats c
6 where a.name = b.name
7 and b.name = c.name
8 and a.runid = 'before'
9 and b.runid = 'after 1'
10 and c.runid = 'after 2'
11 and (c.value-a.value) > 0
12 and (c.value-b.value) <> (b.value-a.value)
13 and a.name like 'LATCH%'
14 )
15 /
RUN1 RUN2 DIFF RUN1_PCT_OF_RUN2
---------- ---------- ---------- ----------------
857464 835761 -21703 102.6
<b>we didn't fix the latching.... So, basically it "works sometimes" but not consistently</b>
Conclusion -- it can work to help sometimes -- so the "whole truth" is:
session_cached_cursors will help you increase the scalability of YOUR application when YOU don't cache the cursors. It can SOMETIMES, under SOME conditions help with recursive SQL.
Fortunately, I was most interested in the "YOUR APPLICATION" with "YOUR UNCHACHED cursors" -- the recursive sql was just a nice side effect, that apparently isn't 100% effective.
(please don't ask me why the above is true, it just is ;)