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

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.

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

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

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.
To see this, we will create many blocks in a table that need to be cleaned out. We will then open a cursor on that
table and allow many small transactions to take place against some other table—not the table we just updated and
opened the cursor on. Finally, we will attempt to fetch the data for the cursor. Now, we know that the data required by
the cursor will be “OK”—we should be able to see all of it since the modifications to the table would have taken place
and been committed before we open the cursor. When we get an ORA-01555 error this time, it will be because of the
previously described issue with delayed block cleanout. To set up for this example, we'll use

UNDO_SMALL undo tablespace. • The 4MB

• A 16MB buffer cache, which is enough to hold about 2,000 blocks. This is so we can get some

dirty blocks flushed to disk to observe this phenomenon.

Before we start, we'll create the undo tablespace and the “big” table we'll be querying:

EODA@ORA12CR1> create undo tablespace undo_small
2 datafile '/tmp/undo.dbf' size 4m
3 autoextend off
4 /
Tablespace created.

EODA@ORA12CR1> create table big
2 as
3 select a.*, rpad('*',1000,'*') data