Investigating Undo - Oracle Database Transactions and Locking Revealed - page 143

Chapter 8
Investigating Undo
In the previous couple of chapters we discussed several undo segment topics. We've seen how they are used during
recovery, how they interact with the redo logs, and how they are used for consistent, nonblocking reads of data. In this
chapter, we'll look at the most frequently raised issues with undo segments.
In this chapter the bulk of our time will be spent on the infamous ORA-01555: snapshot too old error, as
this single issue causes more confusion than any other topic in the entire set of database topics. Before we do this,
however, we'll investigate one other undo-related issue: the question of what type of DML operation generates the
most and least undo (you might already be able to answer that yourself, given the examples in the preceding chapter
with temporary tables).
What Generates the Most and Least Undo?
This is a frequently asked but easily answered question. The presence of indexes (or the fact that a table is an index-
organized table) may affect the amount of undo generated dramatically, as indexes are complex data structures and
may generate copious amounts of undo information.
That said, an INSERT will, in general, generate the least amount of undo, since all Oracle needs to record for this is a
rowid to “delete.” An UPDATE is typically second in the race (in most cases). All that needs to be recorded are the changed
bytes. It is most common that you UPDATE some small fraction of the entire row's data. Therefore, a small fraction of
the row must be remembered in the undo. Many of the previous examples run counter to this rule of thumb, but that's
because they update large, fixed-sized rows and they update the entire row. It is much more common to UPDATE a row
and change a small percentage of the total row. A DELETE will, in general, generate the most undo. For a DELETE , Oracle
must record the entire row's before image into the undo segment. The previous temporary table example (in Chapter 7),
with regard to redo generation, demonstrated that fact: the DELETE generated the most redo, and since the only logged
element of the DML operation on a temporary table is the undo, we in fact observed that the DELETE generated the most
undo. The INSERT generated very little undo that needed to be logged. The UPDATE generated an amount equal to the
before image of the data that was changed, and the DELETE generated the entire set of data written to the undo segment.
As previously mentioned, you must also take into consideration the work performed on an index. You'll find that
an update of an unindexed column not only executes much faster, it also tends to generate significantly less undo
than an update of an indexed column. For example, we'll create a table with two columns, both containing the same
information, and index one of them:
EODA@ORA12CR1> create table t
2 as
3 select object_name unindexed,
4 object_name indexed
5 from all_objects
6 /
Table created.