Listing 6: Testing without and with Bind Variables


SQL> declare
  2      l_start number;
  3          l_cnt   number;
  4  begin

  5      insert into run_stats select 'before', stats.* from stats;
  6  
  7      l_start := dbms_utility.get_time;
  8      for i in 1 .. 1000
  9      loop
 10         execute immediate 'select count(*) from t where x = ' || i into l_cnt;

 11      end loop;
 12      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 13  
 14      insert into run_stats select 'after 1', stats.* from stats;
 15  
 16      l_start := dbms_utility.get_time;
 17      for i in 1 .. 1000

 18      loop
 19          select count(*) into l_cnt from t where x = i;
 20      end loop;
 21      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 22  
 23      insert into run_stats select 'after 2', stats.* from stats;
 24  end;
 25  /

108 hsecs
18 hsecs

PL/SQL procedure successfully completed.