session_cached_cursors and parse

The following  test case demonstrated the effects of session_cached_cursors on whether and how the PL/SQL caches cursors. I tested with either session_cached_cursors=0 or 20 and with a static SQL or an 'execute immediate' statement in a PL/SQL procedure, in a 9.2.0.8 database and a 10.2.0.1 database, respectively.

create or replace procedure  proc_p
is
obj_no number;
begin
for i in 1..100
loop
execute immediate 'select count(*)  from all_objects where object_id = :1 ' into obj_no using i;
dbms_output.put_line('i='  i  ' count='  obj_no);
end loop;
end;
/


create or replace procedure  proc_p2
is
obj_no number;
begin
for i in 1..100
loop
select count(*) into obj_no from all_objects where object_id = i;
dbms_output.put_line('i='  i  ' count='  obj_no);
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=0;
alter session set session_cached_cursors=20;

exec proc_p;
exec proc_p2;

alter session set sql_trace=false;
exit;

I used TKPROF to analyse the trace files. Below is the summary:

----------------------------------------------------------------------------
version  session_cached_cursors    SQL          # of parse    # of execution
----------------------------------------------------------------------------
9.2.0.8      0                     exe immed    100            100
9.2.0.8      0                     static       100            100
9.2.0.8      20                    exe immed    100            100
9.2.0.8      20                    static       1              100
10.2.0.1     0                     exe immed    100            100
10.2.0.1     0                     static       100            100
10.2.0.1     20                    exe immed    1              100
10.2.0.1     20                    static       1              100
----------------------------------------------------------------------------

It can be seen that with session_cached_cursors=0, no matter in which version and with what kind of SQL, Oracle needs to at least soft parse each SQL for each execution. So session_cached_cursors=0 effectively disable the ability of PL/SQL to cache cursors. With session_cached_cursors=20 (nozero essentially),in 9.2.0.8, for the static SQL it can be achieved "parase once, execute many times"; in 10.2.0.1, for both type of SQL, same effect of "parse once, execute many times" is observed. Whenever possible, we should use static SQL in PL/SQL code; if not, we should try to use 'execute immediate' with bind variables. It seems from 10g, PL/SQL engine can cache cursors from 'execute immediate' statement also.

I continued to experiment about the session_cached_cursors and the number of SQL that is repeatedly executed. The test code looks like:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
begin
for i in 1..100
loop
-- sql a
select count(*) into obj_no from all_objects where object_id = i; 
-- sql b
select max(object_name) into obj_name from all_objects where rownum <= i;
-- sql c 
select min(object_name) into obj_name from all_objects where rownum <= i;
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=3;

exec proc_p2;

alter session set sql_trace=false;
exit;

I tested with session_cached_cursors from 1 to 3 and number of SQL from 1-3 as well. Here is the summary:

-------------------------------------------------------------------------
version  session_cached_    # static SQL      # of parse    # of execution
cursors
-------------------------------------------------------------------------
10.2.0.1     1                      1                1             100
10.2.0.1     1                      2   (a)          100           100
                                        (b)          100           100
10.2.0.1     2                      2   (a)          100           100
10.2.0.1                                (b)          100           100
10.2.0.1     3                      2   (a)          1             100
                                        (b)          1             100
10.2.0.1     3                      3   (a)          100           100
                                        (b)          100           100
                                        (c)          100           100
-------------------------------------------------------------------------

In the case of session_cached_cursors=3 and three static SQLs, in the above test, I repeatedly executed all three SQLs together 100 times. How about repeatedly executing each SQL 100 times, for three SQLs total?. How about picking up the next SQL to be executed randomly? Can increase session_cached_cursors really help?. Below are the test codes and some results.

Test code of excuting each static SQL 100 times, for three SQLs total:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
begin
for i in 1..100
loop
-- sql a
select count(*) into obj_no from all_objects where object_id = i; 
end loop;     

for i in 1..100
loop  
-- sql b
select max(object_name) into obj_name from all_objects where rownum <= i;
end loop;

for i in 1..100
loop 
-- sql c 
select min(object_name) into obj_name from all_objects where rownum <= i;
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=3;

exec proc_p2;

alter session set sql_trace=false;
exit;

Test code of picking next static SQL to be executed randomly:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
r number;
begin
for i in 1..300
loop
r := dbms_random.value; 
if ( r <= 0.3333 ) then
select count(*) into obj_no from all_objects where object_id = i; 
elsif ( r >= 0.6667 ) then
select max(object_name) into obj_name from all_objects where rownum <= i;
else 
select min(object_name) into obj_name from all_objects where rownum <= i;
end if;
end loop;     
end;
/
show errors;

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=20;

exec proc_p2;

alter session set sql_trace=false;
-- exit;

Here is the result from TKPROF for above two cases:

------------------------------------------------------------------------
Order      session_cached_    # static SQL    # of parse  # of execution
cursors
------------------------------------------------------------------------
Each SQL
100 times      3                   3   (a)         1          100
                                       (b)         1          100
                                       (c)         1          100
Random         3                   3   (a)        33           90
                                       (b)        33          104
                                       (c)        33          106
Random         20                  3   (a)         1           96
                                       (b)         1          104
                                       (c)         1          100
------------------------------------------------------------------------