Mega Code Archive
Use DBMS_PROFILER to compare the performance differences between row-at-a-time processing and bulk processing
<![CDATA[
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> 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> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL>
]]>