Overview
This article will discuss the circumstances under which a query can return the Oracle error ORA-01555 "snapshot too old (rollback segment too small)". The article will then proceed to discuss actions that can be taken to avoid the error and finally will provide some simple PL/SQL scripts that illustrate the issues discussed.
Terminology
It is assumed that the reader is familiar with standard Oracle terminology such as 'rollback segment' and 'SCN'. If not, the reader should first read the Oracle Server Concepts manual and related Oracle documentation.
In addition to this, two key concepts are briefly covered below which help in the understanding of ORA-01555:
1. READ CONSISTENCY:
====================
This is documented in the Oracle Server Concepts manual and so will not be discussed further. However, for the purposes of this article this should be read and understood if not understood already.
Oracle Server has the ability to have multi-version read consistency which is invaluable to you because it guarantees that you are seeing a consistent view of the data (no 'dirty reads').
2. DELAYED BLOCK CLEANOUT:
==========================
This is best illustrated with an example: Consider a transaction that updates a million row table. This obviously visits a large number of database blocks to make the change to the data. When the user commits the transaction Oracle does NOT go back and revisit these blocks to make the change permanent. It is left for the next transaction that visits any block affected by the update to 'tidy up' the block (hence the term 'delayed block cleanout').
Whenever Oracle changes a database block (index, table, cluster) it stores a pointer in the header of the data block which identifies the rollback segment used to hold the rollback information for the changes made by the transaction. (This is required if the user later elects to not commit the changes and wishes to 'undo' the changes made.)
Upon commit, the database simply marks the relevant rollback segment header entry as committed. Now, when one of the changed blocks is revisited Oracle examines the header of the data block which indicates that it has been changed at some point. The database needs to confirm whether the change has been committed or whether it is currently uncommitted. To do this, Oracle determines the rollback segment used for the previous transaction (from the block's header) and then determines whether the rollback header indicates whether it has been committed or not.
If it is found
that the block is committed then the header of the data block is updated so that subsequent accesses to the block do not incur this processing.
This behaviour is illustrated in a very simplified way below. Here we walk through the stages involved in updating a data block.
STAGE 1 - No changes made
Description: This is the starting point. At the top of the data block we have an area used to link active transactions to a rollback segment (the 'tx' part), and the rollback segment header has a table that stores information upon all the latest transactions that have used that rollback segment.
In our example, we have two active transaction slots (01 and 02)and the next free slot is slot 03. (Since we are free to overwrite committed transactions.)
Data Block 500
Rollback Segment Header 5
+----+--------------+
+----------------------+---------+
| tx | None
|
| transaction entry 01 |ACTIVE
|
+----+--------------+
| transaction entry 02 |ACTIVE
|
| row 1
|
| transaction entry 03 |COMMITTED|
| row 2
|
| transaction entry 04 |COMMITTED|
| ... ..
|
|
...
...
.. |
...
|
| row n
|
| transaction entry nn |COMMITTED|
+-------------------+
+--------------------------------+
STAGE 2 - Row 2 is updated
Description: We have now updated row 2 of block 500. Note that the data block header is updated to point to the rollback segment 5, transaction slot 3 (5.3) and that it is marked uncommitted (Active).
Data Block 500
Rollback Segment Header 5
+----+--------------+
+----------------------+---------+
| tx |5.3uncommitted|-+
| transaction entry 01 |ACTIVE
|
+----+--------------+ |
| transaction entry 02 |ACTIVE
|
| row 1
| +--| transaction entry 03 |ACTIVE
|
| row 2 *changed*
|
| transaction entry 04 |COMMITTED|
| ... ..
|
|
...
...
.. |
...
|
| row n
|
| transaction entry nn |COMMITTED|
+------------------+
+--------------------------------+
STAGE 3 - The user issues a commit
Description: Next the user hits commit. Note that all that this does is it updates the rollback segment header's corresponding transaction slot as committed. It does *nothing* to the data block.
Data Block 500
Rollback Segment Header 5
+----+--------------+
+----------------------+---------+
| tx |5.3uncommitted|--+
| transaction entry 01 |ACTIVE
|
+----+--------------+
|
| transaction entry 02 |ACTIVE
|
| row 1
|
+---| transaction entry 03 |COMMITTED|
| row 2 *changed*
|
| transaction entry 04 |COMMITTED|
| ... ..
|
|
...
...
.. |
...
|
| row n
|
| transaction entry nn |COMMITTED|
+------------------+
+--------------------------------+
STAGE 4 - Another user selects data block 500
Description: Some time later another user (or the same user) revisits data block 500. We can see that there is an uncommitted change in the data block according to the data block's header.
Oracle then uses the data block header to look up the corresponding rollback segment transaction table slot, sees that it has been committed, and changes data block 500 to reflect the true state of the datablock. (i.e. it performs delayed cleanout).
Data Block 500
Rollback Segment Header 5
+----+--------------+
+----------------------+---------+
| tx | None
|
| transaction entry 01 |ACTIVE
|
+----+--------------+
| transaction entry 02 |ACTIVE
|
| row 1
|
| transaction entry 03 |COMMITTED|
| row 2
|
| transaction entry 04 |COMMITTED|
| ... ..
|
|
...
...
.. |
...
|
| row n
|
| transaction entry nn |COMMITTED|
+------------------+
+--------------------------------+
ORA-01555 Explanation
There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a 'read consistent' image. These are :
o The rollback information itself is overwritten so that Oracle is unable to rollback the (committed) transaction entries to attain a sufficiently old enough version of the block.
o The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.
Both of these situations are discussed below with the series of steps that cause the ORA-01555. In the steps, reference is made to 'QENV'. 'QENV' is short for 'Query Environment', which can be thought of as the environment that existed when a query is first star
