Detailed description on Performance Improvement of PLSQL batch Forall operations

First, create a test table.

Create table test1 (c1 number, c2 number, c3 number );
Create table test2 (c1 number, c2 number, c3 number );

Start testing

SQL> declare
2
2 l_stat_ SQL varchar2 (2000): = 'select value from v $ mystat ms, v $ statname sn where ms. STATISTIC # = sn. STATISTIC # and name =: 1 ';
3 type t is table of test2 % rowtype;
4 l t: = t ();
5 l_undo_stat1 int;
6 Rochelle undo_stat2 int;
7 Rochelle undo_stat int;
8 l_redo_stat1 int;
9 l_redo_stat2 int;
10 l_redo_stat int;
11 l_time_stat1 int;
12 l_time_stat2 int;
13 l_time_stat int;
14 begin
15 l_time_stat1: = dbms_utility.get_time;
16 execute immediate l_stat_ SQL into l_redo_stat1 using 'redo size ';
17 execute immediate l_stat_ SQL into l_undo_stat1 using 'undo change vector size ';
18 for I in 1 .. 10000 loop
19 insert into test1 values (I, I/2, mod (I, 2 ));
20 end loop;
21 l_time_stat2: = dbms_utility.get_time;
22 execute immediate l_stat_ SQL into l_redo_stat2 using 'redo size ';
23 execute immediate l_stat_ SQL into l_undo_stat2 using 'undo change vector size ';
24 l. extend (10000 );
25 for I in 1 .. 10000 loop
26 l (I). c1: = I;
27 l (I). c2: = I/2;
28 l (I). c3: = mod (I, 2 );
29 end loop;
30 forall I in 1 .. l. last
31 insert into test2 values l (I );
32 l_time_stat3: = dbms_utility.get_time;
33 execute immediate l_stat_ SQL into l_redo_stat using 'redo size ';
34 execute immediate l_stat_ SQL into l_undo_stat using 'undo change vector size ';
35
36 dbms_output.put_line ('onebyone redo: '| (l_redo_stat2-l_redo_stat1 ));
37 dbms_output.put_line ('bulk redo: '| (l_redo_stat3-l_redo_stat2 ));
38 dbms_output.put_line ('-');
39 dbms_output.put_line ('onebyone undo: '| (l_undo_stat2-l_undo_stat1 ));
40 dbms_output.put_line ('bulk undo: '| (l_undo_stat3-l_undo_stat2 ));
41 dbms_output.put_line ('-');
42 dbms_output.put_line ('onebyone time: '| (l_time_stat2-l_time_stat1 ));
43 dbms_output.put_line ('bulk time: '| (l_time_stat3-l_time_stat2 ));
44 end;
45/
 
OneByOne redo: 2582244
Bulk redo: 228428
-
OneByOne undo: 681172
Bulk undo: 25432
-
OneByOne time: 84
Bulk time: 2
 
PL/SQL procedure successfully completed

-- It turns out that using bulk operations not only reduces the frequent switching between plsql and SQL engines, but also compares a single execution. It can also reduce the generation of redo and undo.
-- We can see that redo differs by 10 times, and undo differs by nearly 20 times.
-- In terms of time, a single execution takes 840 milliseconds, while the batch mode only takes 20 milliseconds.

Because it is in the same transaction, the scn number is the same

SQL> select ora_rowscn, t. * from test1 t where rownum <= 10;
 
ORA_ROWSCN C1 C2 C3
----------------------------------------
17108596 2289 1144.5 1
17108596 2290 1145 0
17108596 2291 1145.5 1
17108596 2292 1146 0
17108596 2293 1146.5 1
17108596 2294 1147 0
17108596 2295 1147.5 1
17108596 2296 1148 0
17108596 2297 1148.5 1
17108596 2298 1149 0
 
10 rows selected
 
SQL> select ora_rowscn, t. * from test2 t where rownum <= 10;
 
ORA_ROWSCN C1 C2 C3
----------------------------------------
17108596 2289 1144.5 1
17108596 2290 1145 0
17108596 2291 1145.5 1
17108596 2292 1146 0
17108596 2293 1146.5 1
17108596 2294 1147 0
17108596 2295 1147.5 1
17108596 2296 1148 0
17108596 2297 1148.5 1
17108596 2298 1149 0
 
10 rows selected

1 2 Next Page