Database Reference
In-Depth Information
What Does a ROLLBACK Do?
By changing the
COMMIT
to
ROLLBACK
, we can expect a totally different result. The time to roll back is definitely
a function of the amount of data modified. I changed the script developed in the previous section to perform a
ROLLBACK
instead (simply change the
COMMIT
to
ROLLBACK
) and the timings are very different. Look at the results now:
EODA@ORA12CR1> declare
2 l_redo number;
3 l_cpu number;
4 l_ela number;
5 begin
6 dbms_output.put_line
7 ( '-' || ' Rows' || ' Redo' ||
8 ' CPU' || ' Elapsed' );
9 for i in 1 .. 6
10 loop
11 l_redo := get_stat_val( 'redo size' );
12 insert into t select * from big_table where rownum <= power(10,i);
13 l_cpu := dbms_utility.get_cpu_time;
14 l_ela := dbms_utility.get_time;
15 --commit work write wait;
16 rollback;
17 dbms_output.put_line
18 ( '-' ||
19 to_char( power( 10, i ), '9,999,999') ||
20 to_char( (get_stat_val('redo size')-l_redo), '999,999,999' ) ||
21 to_char( (dbms_utility.get_cpu_time-l_cpu), '999,999' ) ||
22 to_char( (dbms_utility.get_time-l_ela), '999,999' ) );
23 end loop;
24 end;
25 /
- Rows Redo CPU Elapsed
- 10 7,180 0 0
- 100 10,872 0 0
- 1,000 121,880 0 0
- 10,000 1,224,864 0 0
- 100,000 12,148,416 2 4
- 1,000,000 121,733,580 25 36
PL/SQL procedure successfully completed.
This difference in CPU and Elapsed timings is to be expected, as a
ROLLBACK
has to undo the work we've done.
Similar to a
COMMIT
, a series of operations must be performed. Before we even get to the
ROLLBACK
, the database has
already done a lot of work. To recap, the following would have happened:
•
Undo segment records have been generated in the SGA.
•
Modified data blocks have been generated in the SGA.
•
A buffered redo log for the preceding two items has been generated in the SGA.
