Time the PL/SQL block : Timing « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. Timing

SQL>
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> set timing on
SQL> declare
  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      myStart number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for 'select object_name from all_objects where object_id = ' || i;
 10          fetch l_rc into l_dummy;
 11          close l_rc;
 12      end loop;
 13      dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||' seconds...' );
 14  end;
 15  /
20.19 seconds...

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.27
SQL>
SQL> declare
  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      myStart number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for 'select object_name from all_objects where object_id = :x'
 10          using i;
 11          fetch l_rc into l_dummy;
 12          close l_rc;
 13      end loop;
 14      dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||' seconds...' );
 15  end;
 16  /
.19 seconds...

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
SQL>
SQL> set timing off