Redo and Undo - Expert Oracle Database Architecture - page 346

So, instead of receiving an error, we completed successfully, and the undo grew to be large enough to
accommodate our needs. It is true that in this example, getting the error was purely due to the fact that we read the
table T via the index and performed random reads all over the table. If we had rapidly full-scanned the table instead,
there is a good chance we would not have received the ORA-01555 error in this particular case . This is because both
the SELECT and UPDATE would have been full-scanning T , and the SELECT could most likely race ahead of the UPDATE
during its scan (the SELECT just has to read, but the UPDATE must read and update and therefore could go slower).
By doing the random reads, we increase the probability that the SELECT will need to read a block, which the UPDATE
modified and committed many rows ago. This just demonstrates the somewhat insidious nature of the ORA-01555
error . Its occurrence depends on how concurrent sessions access and manipulate the underlying tables.

Delayed Block Cleanout
This cause of the ORA-01555 error is hard to eliminate entirely, but it is rare anyway, as the circumstances under which
it occurs do not happen frequently (at least not in Oracle8 i and above anymore). We have already discussed the
block cleanout mechanism, but to summarize, it is the process whereby the next session to access a block after it has
been modified may have to check to see if the transaction that last modified the block is still active. Once the process
determines that the transaction is not active, it cleans out the block so that the next session to access it does not have to
go through the same process again. To clean out the block, Oracle determines the undo segment used for the previous
transaction (from the block's header) and then determines whether the undo header indicates that the transaction
has been committed and, if so, when it committed. This confirmation is accomplished in one of two ways. One way is
that Oracle can determine that the transaction committed a long time ago, even though its transaction slot has been
overwritten in the undo segment transaction table. The other way is that the COMMIT SCN is still in the transaction table of
the undo segment, meaning the transaction committed a short time ago, and its transaction slot hasn't been overwritten.
To receive the ORA-01555 error from a delayed block cleanout, all of the following conditions must be met:
A modification is made and •

COMMIT ed, and the blocks are not cleaned out automatically (e.g.,
the transaction modified more blocks than can fit in 10 percent of the SGA block buffer cache).

• These blocks are not touched by another session and will not be touched until our unfortunate

query (displayed shortly) hits it.

• A long-running query begins. This query will ultimately read some of those blocks from

earlier. This query starts at SCN t1 , the read-consistent SCN it must roll data back to in order
to achieve read consistency. The transaction entry for the modification transaction is still in
the undo segment transaction table when we begin.

• During the query, many commits are made in the system. These transactions don't touch the

blocks in question (if they did, we wouldn't have the impending problem as they would clean
out the old transaction—solving the clean-out issue).

• The transaction tables in the undo segments roll around and reuse slots due to the high degree

of COMMIT s. Most important, the transaction entry for the original modification transaction is
cycled over and reused. In addition, the system has reused undo segment extents, preventing
a consistent read on the undo segment header block itself.

t1 (it is higher than
the read-consistent SCN of the query), due to the large number of commits.

• Additionally, the lowest SCN recorded in the undo segment now exceeds

When our query gets to the block that was modified and committed before it began, it is in trouble. Normally,
it would go to the undo segment pointed to by the block and find the status of the transaction that modified it (in
other words, it would find the COMMIT SCN of that transaction). If the COMMIT SCN is less than t1 , our query can use
this block. If the COMMIT SCN is greater than t1 , our query must roll back that block. The problem is, however, that our
query is unable to determine in this particular case if the COMMIT SCN of the block is greater than or less than t1 .
It is unsure as to whether it can use that block image or not. The ORA-01555 error then results.