Transactions - Oracle Database Transactions and Locking Revealed - page 97

The dynamic performance view V$UNDOSTAT can be very useful to monitor the amount of undo you are generating and
the duration of your longest running queries. Many people consider things like temp, undo, and redo as overhead—
things to allocate as little storage to as possible. This is reminiscent of a problem the computer industry had on
January 1, 2000, which was all caused by trying to save 2 bytes in a date field. These components of the database are
not overhead, but rather are key components of the system. They must be sized appropriately
(not too big and not too small).

■ speaking of UNDO segments being too small, make sure to set your undo tablespace back to your regular one
after running these examples, otherwise you'll be hitting ORA-30036 errors for the rest of the topic!

Note

Restartable Processes Require Complex Logic
The most serious problem with the “commit before the logical transaction is over” approach is the fact that it
frequently leaves your database in an unknown state if the UPDATE fails halfway through. Unless you planned for this
ahead of time, it is very hard to restart the failed process, allowing it to pick up where it left off. For example, say we
were not applying the LOWER() function to the column, as in the previous example, but rather some other function of
the column, such as this:

last_ddl_time = last_ddl_time + 1;

If we halted the UPDATE loop partway through, how would we restart it? We could not just rerun it, as we would
end up adding 2 to some dates, and 1 to others. If we fail again, we would add 3 to some, 2 to others, 1 to the rest,
and so on. We need yet more complex logic—some way to “partition” the data. For example, we could process every
OBJECT_NAME that starts with A , and then B , and so on:

EODA@ORA12CR1> create table to_do
2 as
3 select distinct substr( object_name, 1,1 ) first_char
4 from T
5 /
Table created.

EODA@ORA12CR1> begin
2 for x in ( select * from to_do )
3 loop
4 update t set last_ddl_time = last_ddl_time+1
5 where object_name like x.first_char || '%';
6
7 dbms_output.put_line( sql%rowcount || ' rows updated' );
8 delete from to_do where first_char = x.first_char;
9
10 commit;
11 end loop;
12 end;
13 /