Database Reference
In-Depth Information
•
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.
Undo Segments Are in Fact Too Small
The scenario is this: you have a system where the transactions are small. As a result, you need very little undo segment
space allocated. Say, for example, the following is true:
•
Each transaction generates 8KB of undo on average.
•
You do five of these transactions per second on average (40KB of undo per second, 2,400KB
per minute).
•
You have a transaction that generates 1MB of undo that occurs once per minute on average. In
total, you generate about 3.5MB of undo per minute.
•
You have 15MB of undo configured for the system.
That is more than sufficient undo for this database when processing transactions. The undo segments will wrap
around and reuse space about every three to four minutes or so, on average. If you sized undo segments based on your
transactions that do modifications, you did all right.
In this same environment, however, you have some reporting needs. Some of these queries take a really long
time to run—five minutes, perhaps. Here is where the problem comes in. If these queries take five minutes to execute
and
they need a view of the data as it existed when the query began, you have a very good probability of the
ORA-01555
error occurring. Since your undo segments will wrap during this query execution, you know that some
undo information generated since your query began is gone—it has been overwritten. If you hit a block that was
modified near the time you started your query, the undo information for this block will be missing, and you will
receive the
ORA-01555
error.
Here's a small example. Let's say we have a table with blocks 1, 2, 3, . . . 1,000,000 in it. Table
9-2
shows a sequence
of events that could occur.
