To understand the ORA-01555 – Snapshot too old error, it is important to know what delayed block cleanout is, and this post aims to explain the concept behind this term with a simple example.
To explain this term, consider a transaction in Oracle that is updating a table which contains a million rows. To make changes to the underlying data, this transaction will obviously have to visit a large number of data blocks. Now, if the transaction issues an update after making the changes, Oracle does not go and visit every impacted block to make these changes a permanent one. It rather leaves this job to the next transaction that visits the block which was impacted by this update. The next transaction that visits this block will have to “clean” the block and set it right. This is termed as Delayed Block Cleanout.
When a data block is changed by Oracle during a transaction, a reference is stored in the header of the data block that will identify the rollback segment information for the changes made by the transaction.We discussed about this earlier during our snapshot too old explanation, and this information helps to rollback the transaction in case of a ROLLBACK. Now, when the transaction COMMITs the information, the rollback segment entry is marked as COMMITTed. No other activity is done at this point.
When another transaction visits this changed data block, Oracle checks the header of the data block and finds that it has been changed at some point in time. The database needs to confirm if this change is COMMITTed or not. Oracle tries to get this information from the rollback segment header, by reading the address stored in the header of the data block.If it finds that the changes are COMMITTed, then it reflects this information in the data block header so that any further selects to the data block does not repeat this task. We will now try to explain all this theory behind Delayed Block Cleanout with a simple example.
Consider a data block setup as shown below and corresponding rollback segment setup
 |
| Initial Data Block Setup - No updates done to the data yet |
Here, at Data block 101 we have an area (marked as None) that links active transactions to rollback segment header. The rollback segment header has a table that stores information of all latest transactions that have used that rollback segment. Our Header 6, has two ‘Active’ slots, and some free slots. (marked as Committed). Committed ones are free to be reused. When we update row 3 of data block 101, the data block header address points to 6.4 and it is marked as ‘Active’..(Not COMMITTED)..As an example
 |
| Row R3 Modified, Data block header holding the Address of Rollback Segment, Rollback Segment Header Active |
When the user issues a COMMIT at this point, what happens is that the entry in the rollback segment header is marked as COMMITTed. The data block is not impacted at this stage, as shown below
 |
| Setup when the transaction is committed. Data block Not Updated |
After a while, when some other transaction visits the Data block 101, it detects that there is a uncommitted change to the data block as per the data block header. Oracle now takes the address from the data block header, and does a lookup to the corresponding rollback segment transaction slot, and verifies that it has been COMITTed. At this point, it changes the data block 101 to reflect the actual status. In essence, it performs a delayed block cleanout, as shown below
 |
| Setup when another transaction reads the modified Data Block - Delayed Block Cleanout |
Hi,
ReplyDeleteIn this post, you had mentioned that the block in the rollback segment can be overritten if the data is committed. Would that mean the block in the rollback segment which are committed would be cleaned out, irrespective of whether data is written back to the data block?
@Wils, when a COMMIT is done, the area becomes available for reuse to other transactions. In a very active database with a small rollback segment, this can possibly happen in a short span of time.
ReplyDelete