Database Reference
In-Depth Information
Table 9-2.
Long-Running Query Timeline
Time (Minutes:Seconds)
Action
0:00
Our query begins.
Another session
UPDATE
s block 1,000,000. Undo information for this is recorded into
some undo segment.
0:01
This
UPDATE
session
COMMIT
s. The undo data it generated is still there, but is now subject
to being overwritten if we need the space.
0:01
1:00
Our query is still chugging along. It is at block 200,000.
1:01
Lots of activity going on. We have generated a little over 14MB of undo by now.
3:00
Our query is still going strong. We are at block 600,000 or so by now.
4:00
Our undo segments start to wrap around and reuse the space that was active when our
query began at time 0:00. Specifically, we have just reused the undo segment space that
the
UPDATE
to block 1,000,000 used back at time 0:01.
5:00
Our query finally gets to block 1,000,000. It finds it has been modified since the query
began. It goes to the undo segment and attempts to find the undo for that block to get a
consistent read on it. At this point, it discovers the information it needs no longer exists.
ORA-01555
is raised and the query fails.
This is all it takes. If your undo segments are sized such that they have a good chance of being reused during the
execution of your queries, and your queries access data that will probably be modified, you stand a very good chance
of hitting the
ORA-01555
error on a recurring basis. If this is the case, you must set your
UNDO_RETENTION
parameter
higher and let Oracle take care of figuring out how much undo to retain (this is the suggested approach; it's much
easier than trying to figure out the perfect undo size yourself ) or resize your undo segments and make them larger
(or have more of them). You need enough undo configured to last as long as your long-running queries. The system
was sized for the transactions that modify data—you forgot to size for the other components of the system.
With Oracle9
i
and above, there are two methods to manage undo in the system:
•
Automatic undo management
: Here, Oracle is told how long to retain undo for, via the
UNDO_RETENTION
parameter. Oracle will determine how many undo segments to create based
on concurrent workload and how big each should be. The database can even reallocate
extents between individual undo segments at runtime to meet the
UNDO_RETENTION
goal set by
the DBA. This is the recommended approach for undo management.
•
Manual undo management
: Here, the DBA does the work. The DBA determines how many
undo segments to manually create, based on the estimated or observed workload. The DBA
determines how big the segments should be based on transaction volume (how much undo is
generated) and the length of the long-running queries.
Manual undo management, where a DBA figures out how many undo segments to have and how big each should
be, is where one of the points of confusion comes into play. People say, “Well, we have XMB of undo configured, but
this can grow. We have
MAXEXTENTS
set at 500 and each extent is 1MB, so the undo can get quite large.” The problem
is that the manually managed undo segments will never grow due to a query; they will grow only due to
INSERT
s,
UPDATE
s, and
DELETE
s. The fact that a long-running query is executing does not cause Oracle to grow a manual undo
segment to retain the data in case it might need it. Only a long-running
UPDATE
transaction would do this. In the
preceding example, even if the manual undo segments had the potential to grow, they will not. What you need to do
for this system is have manual undo segments that are already big. You need to permanently allocate space to the
undo segments, not give them the opportunity to grow on their own.
