Use DBMS_PROFILER to compare the performance differences between row-at-a-time processing and bulk processing : DBMS_PROFILER « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_PROFILER

SQL>
SQL> create table t1 as select * from dba_objects where 0=1;

Table created.

SQL> create table t2 as select * from dba_objects where 0=1;

Table created.

SQL>
SQL>
SQL> create or replace procedure row_at_a_time_test
  2  as
  3  begin
  4      dbms_profiler.start_profiler('row test');
  5      for x in ( select * from all_objects )
  6      loop
  7          insert into t1 values X;
  8      end loop;
  9      dbms_profiler.stop_profiler;
 10  end;
 11  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> create or replace procedure bulk_test
  2  as
  3    type array is table of all_objects%rowtype;
  4    l_data array;
  5    cursor c is select * from all_objects;
  6  begin
  7    dbms_profiler.start_profiler('bulk test');
  8    open c;
  9    loop
 10       fetch c bulk collect into l_data LIMIT 100;
 11
 12       forall i in 1 .. l_data.count
 13           insert into t2 values l_data(i);
 14
 15       exit when c%notfound;
 16    end loop;
 17    dbms_profiler.stop_profiler;
 18  end;
 19  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL>
SQL>
SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL>
SQL>