Database Reference
In-Depth Information
So, we had nine sessions inside of a tight loop initiate many transactions. The
run.sh
script waited for the nine
SQL*Plus sessions to complete their work, and then we returned to our session, the one with the open cursor. Upon
attempting to print it out, we observe the following:
EODA@ORA12CR1> print x
ERROR:
ORA-01555: snapshot too old: rollback segment number 17 with name
"_SYSSMU17_452567810$" too small
no rows selected
As I said, the preceding is a rare case. It took a lot of conditions, all of which must exist simultaneously to
occur. We needed blocks that were in need of a cleanout to exist, and these blocks are rare in Oracle8
i
and above.
A
DBMS_STATS
call to collect statistics gets rid of them so the most common causes—large mass updates and bulk
loads—should not be a concern, since the tables need to be analyzed after such operations anyway. Most transactions
tend to touch less than 10 percent of the blocks in the buffer cache; hence, they do not generate blocks that need to
be cleaned out. If you believe you've encountered this issue, in which a
SELECT
against a table that has no other DML
applied to it is raising the
ORA-01555 error
, try the following solutions:
Ensure you are using “right-sized” transactions in the first place. Make sure you are not
•
committing more frequently than you should.
•
Use
DBMS_STATS
to scan the related objects, cleaning them out after the load. Since the block
cleanout is the result of a very large mass
UPDATE
or
INSERT
, this needs to be done anyway.
Allow the undo tablespace to grow by giving it the room to extend and increasing the undo
•
retention. This decreases the likelihood of an undo segment transaction table slot being
overwritten during the course of your long-running query. This is the same as the solution for
the other cause of an
ORA-01555
error (the two are very much related; you experience undo
segment reuse during the processing of your query). In fact, I reran the preceding example
with the undo tablespace set to autoextend 1MB at a time, with an undo retention of 900
seconds. The query against the table
BIG
completed successfully.
Reduce the runtime of your query—tune it. This is always good if possible, so it might be the
•
first thing you try.
Summary
In this chapter we investigated which statements generate the least and most undo. In general an
INSERT
generates
the least amount, an
UPDATE
generates more than
INSERT
, and a
DELETE
generates the most undo.
The bulk of this chapter explored the causes of the infamous
ORA-01555
error (snapshot too old). This error can
occur because the undo tablespace has been sized too small. The DBA must ensure that the undo tablespace is sized
large enough mostly eliminate this as a cause for the error. We also looked at how a delayed block cleanout can cause
issues. If you've correctly sized your transactions and your undo tablespace, you will probably rarely run into this
error. Tuning the query that throws the
ORA-01555
error should always be one of the first methods employed to resolve
the issue.