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

The first two points are directly related to Oracle's read-consistency model. As you recall from Chapter 4, the
results of your query are preordained , meaning they are well-defined before Oracle goes to retrieve even the first
row. Oracle provides this consistent point in time “snapshot” of the database by using the undo segments to roll back
blocks that have changed since your query began. Every statement you execute, such as the following:

update t set x = 5 where x = 2;
insert into t select * from t where x = 2;
delete from t where x = 2;
select * from t where x = 2;

will see a read-consistent view of T and the set of rows where X=2 , regardless of any other concurrent activity in
the database.

■ the four statements presented here are just examples of the types of statements that would see a
read-consistent view of T . they are not meant to be run as a single transaction in the database, as the first update
would cause the following three statements to see no records. they are purely illustrative.

Note

All statements that “read” the table take advantage of this read consistency. In the example just shown, the
UPDATE reads the table to find rows where x=2 (and then UPDATE s them). The INSERT reads the table to find rows
where X=2 , and then INSERT s them, and so on. It is this dual use of the undo segments, both to roll back failed
transactions and to provide for read consistency that results in the ORA-01555 error.
The third item in the previous list is a more insidious cause of ORA-01555 in that it can happen in a database
where there is a single session, and this session is not modifying the tables that are being queried when the ORA-01555
error is raised! This doesn't seem possible—why would we need undo data for a table we can guarantee is not being
modified? We'll find out shortly.
Before we take a look at all three cases with illustrations, I'd like to share with you the solutions to the ORA-01555
error, in general:

• Set the parameter

UNDO_RETENTION properly (larger than the amount of time it takes to execute
your longest-running transaction). V$UNDOSTAT can be used to determine the duration of your
long-running queries. Also, ensure sufficient space on disk has been set aside so the undo
segments are allowed to grow to the size they need to be based on the requested
UNDO_RETENTION .
Increase the size of or add more undo segments when using manual undo management. •

This decreases the likelihood of undo data being overwritten during the course of your
long-running query. This method goes toward solving all three of the previous points.
Note that this is definitely not the preferred method; automatic undo management is
highly recommended.

• Reduce the runtime of your query (tune it). This is always a good thing if possible, so it might

be the first thing you try. It reduces the need for larger undo segments. This method goes
toward solving all three of the previous points.

• Gather statistics on related objects. This helps avoid the third point listed earlier. Since the

block cleanout is the result of a very large mass UPDATE or INSERT , statistics-gathering needs to
be done anyway after a mass UPDATE or large load.
We'll come back to these solutions, as they are important to know. It seemed appropriate to display them
prominently before we begin.