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

For this example, I have determined my long-running process takes about 720 seconds to complete (I have about
72,000 records in the table, so at 0.01 seconds per row we have 720 seconds). My UNDO_RETENTION is set to 900 (this is
in seconds, so the undo retention is about 15 minutes). I altered the undo tablespace's data file to permit it to grow by
1MB at a time, up to 2GB in size:
EODA@ORA12CR1> alter database
2 datafile '/tmp/undo.dbf'
3 autoextend on
4 next 1m
5 maxsize 2048m;
Database altered.
When I ran the processes concurrently again, both ran to completion. The undo tablespace's data file grew this
time, because it was allowed to and the undo retention I set up said to.
EODA@ORA12CR1> select bytes/1024/1024
2 from dba_data_files
3 where tablespace_name = 'UNDO_SMALL';
BYTES/1024/1024
---------------
21
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 (in Chapter 7), 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.