LISTING 1: Testing Session-Cached Cursors Performance

SQL> create table emp as select * from scott.emp;
Table created.


SQL> declare
  2      l_start number;
  3          l_cnt   number;
  4  begin
  5      execute immediate 'alter session set session_cached_cursors=0';
  6      insert into run_stats select 'before', stats.* from stats;

  7  
  8      l_start := dbms_utility.get_time;
  9      for i in 1 .. 1000
 10      loop
 11            execute immediate 'select count(*) from emp' into l_cnt;
 12      end loop;
 13      dbms_output.put_line((dbms_utility.get_time-l_start) || ' hsecs' );

 14  
 15      execute immediate 'alter session set session_cached_cursors=100';
 16      insert into run_stats select 'after 1', stats.* from stats;
 17  
 18      l_start := dbms_utility.get_time;
 19      for i in 1 .. 1000
 20      loop
 21            execute immediate 'select count(*) from emp' into l_cnt;

 22      end loop;
 23      dbms_output.put_line((dbms_utility.get_time-l_start) || ' hsecs' );
 24  
 25      insert into run_stats select 'after 2', stats.* from stats;
 26  end;
 27  /
45 hsecs
35 hsecs


PL/SQL procedure successfully completed.