Redo and Undo - Expert Oracle Database Architecture - page 320

• Depending on the size of the preceding three items and the amount of 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.

Investigating Redo
As a developer, it's often important to be able to measure how much redo your operations generate. The more redo
you generate, the longer your operations may take, and the slower the entire system might be. You are not just
affecting your session, but every session. Redo management is a point of serialization within the database. There is
just one LGWR in any Oracle instance, and eventually all transactions end up at LGWR , asking it to manage their redo and
COMMIT their transaction. The more it has to do, the slower the system will be. By seeing how much redo an operation
tends to generate, and testing more than one approach to a problem, you can find the best way to do things.

■ Starting with oracle 12c (on multiprocessor systems) log writer worker processes ( LG00 ) are automatically
started to improve the performance of writing to the redo log file.

Note

Measuring Redo
It is pretty straightforward to see how much redo is being generated, as shown earlier in the chapter. I've used the
AUTOTRACE built-in feature of SQL*Plus. But AUTOTRACE works only with simple DML—it can't, for example, be
used to view what a stored procedure call did. I've also used my utility function presented earlier, GET_STAT_VAL , to
retrieve the “redo size” value from V$ tables. We'll continue to use that function for the following exercise.
Let's take a look at the difference in redo generated by conventional path INSERT s (the normal INSERT s you
and I do every day) and direct-path INSERT s—used when loading large amounts of data into the database. We'll use
AUTOTRACE and the previously created tables T and BIG_TABLE for this simple example. First we'll load the table
using a conventional-path INSERT :

EODA@ORA12CR1> set autotrace traceonly statistics;
EODA@ORA12CR1> truncate table t;
Table truncated.