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:

$ sqlplus eoda/foo@PDB1
SQL> set serverout on
SQL> declarel_redo number;l_cpu number;l_ela number;begindbms_output.put_line( ‘-‘ || ‘ Rows’ || ‘ Redo’ ||’ CPU’ || ‘ Elapsed’ ); for i in 1 .. 6

l_redo := get_stat_val( ‘redo size’ );insert into t select * from big_table where rownum <= power(10,i);l_cpu := dbms_utility.get_cpu_time;l_ela := dbms_utility.get_time;–commit work write wait;rollback;dbms_output.put_line

( ‘-‘ ||to_char( power( 10, i ), ‘9,999,999’) ||to_char( (get_stat_val(‘redo size’)-l_redo), ‘999,999,999’ ) ||to_char( (dbms_utility.get_cpu_time-l_cpu), ‘999,999’ ) || to_char( (dbms_utility.get_time-l_ela), ‘999,999’ ) );end loop;end;/

• Rows Redo CPU Elapsed
• 10 6,672 0 1
• 100 10,884 1 1
• 1,000 122,840 1 0
• 10,000 1,239,080 1 2
• 100,000 14,098,264 7 92
• 1,000,000 71,917,008 36 121

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.
•\ Depending on the size of the preceding three items and the amountof time spent, some combination of the previous data may be flushed onto disk already.
•\ All locks have been acquired.

When we ROLLBACK

•\ We undo all of the changes made. This is accomplished by reading the data back from the undo segment and, in effect, reversing our operation and then marking the undo entry as applied. If we inserted a row, a ROLLBACK will delete it. If we updated a row, a rollback will reverse the update. If we deleted a row, a rollback will reinsert it again.
•\ All locks held by our session are released, and everyone who was enqueued waiting on locks we held will be released.

A COMMIT, on the other hand, just flushes any remaining data in the redo log buffers. It does very little work compared to a ROLLBACK. The point here is that you don’t want to roll back unless you have to. It is expensive since you spend a lot of time doing the work, and you’ll also spend a lot of time undoing the work. Don’t do work unless you’re sure you are going to want to COMMIT it. This sounds like common sense—of course I wouldn’t do all of the work unless I wanted to COMMIT it. However, I’ve often seen a developer use a “real” table as a temporary table, fill it up with data, report on it, and then roll back to get rid of the temporary data. Later, we’ll talk about true temporary tables and how to avoid this issue.