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
------------------------------------------------------------------------
Posts
