Rollback segments are one of the least focussed areas in database tuning for many DBAs. And most of the DBAs pay very little attention for tuning the rollback segments. Tuning Rollback segment needs a greater understanding of internal workings of oracle and difficult to tune by normal means and this small writeup gives a brief introduction to transacions and rollback segments.

A database read modify write life cycle is called as a transaction. Rollback entry is made for all transactions unless specified. A rollback entry consists of pre image value, block address, datafile number, transaction ID and status of the transaction (Active or Commited) as a single rollback entry. For some reason the transaction fails, the old image is taken from the rollback segment and this is called transaction rollback. Rollback segment is owned by SYS irrespective of who creates it and accessable to only to Oracle.

Transaction Layer Components:

Rollback Segment are managed by transaction layer (KT) which directly talks to Cache Layer(KC) and Data Layer (KD). Transaction Layer is responsible for all transactions inside the database (including the recursive transactions) like undo segment allocation, undo generation, ITL changes in the data block and transaction control mechanisms like Rollback, Savepoint and Commits. It also responsible for freelist management and extent allocation inside the tablespaces.

Transaction Properties:

Atomicity: A transaction is an automic unit of processing; it is either performaned in its entirety or NOT performed at all. For example: A mail note is sent only after one commits. When it is sent the entire mail is sent.

Oracle’s implementaion of ACID :

When a transaction modifies a block (insert, delete or update) it records the transaction identifier in the ITL* of that datablock which is the part of varaibale header (transaction layer copmponet in the datablock KT). The transaction identifier contains the address of the rollback segment number (USN), slot number and wrap number.The ITL entry is used to lock the row till the transaction is commited or rolled back.

Let us take a typical banking transaction transfering $100 from account A to account B. Considering Account A and account B having $X and $Y in their respective accounts. At t1 the transaction starts and it ends at t4.

This simple transaction consists of the follwing steps.

We read the current (pre transaction) values of accounts and record the pre image in the rollback segment. Any other transactions looking for the account details between time t1 to t4 will get the consistent copy from the rollback segments.This helps in maintaining the consistency throughout the lifecycle of the transaction.

Let us consider the transaction fails at time t3 (i.e after X-100 but before Y+100) the database is now in inconsistent state. The changes are rolled bacl tot he old values ( X and Y respectively) are restored to make it appear as though the transaction never happened. This ensures the transaction is Atomic

At t4 we issue a commit to make the tranaction complete. Transaction log ensures the completeness of the transaction and this makes the transaction durable , even if the system fails after commit. After t4 any transactions reads accounts A and B will be getting X-100 and Y+100 respectively.

During the interval t1 to t4 any subsequent SELECTs from A and B will be able to see only the inconsistent data. This is because the transactions are not serialized.Here the famous row level locks comes in to picute. The isolation is ensured by row level locks which does not allow the other transactions to change the data, because only the commited information is visible across sessions.

Isolation ensures that concurrently executing transactions isolated from one another such that each has the impression that no other transaction is executing concurrently with it. The default isolation level in oracle is ‘read commited ‘ and the details about other isolation levels are beyond the scope of this discussion.

A transaction can not read an uncommited value because the item remains locked until it reaches the commit point. Row level locks which are implemented through ITL table ensures this.

ITL and delayed block cleanouts

Each datablock will have a Interested Transaction List (ITL) which holds the transaction id of that block during the life cycle of the transaction modifying that datablock. A transaction which modifies a record in the datablock must get an ITL slot in that datablock. The number of ITL slots in a datablock is defined by the INITRANS (which defaults 1 for data blocks and 2 for index blocks) and MAXTRANS.

While formatting a new block Oracle creates the transaction slots specified by INITRANS parameter. MAXTRANS specifies maximum number of ITLs created for a datablock and it defaults to 255. In practical you don’t need more MAXTRANS unless your AVG_ROW_LENGTH is very small and the segment is frequently updated.

The creation additional of Interested Transaction Lists (ITL) slots is subject to free space in the datablock because each ITL takes approximately 24 bytes of free space in the variable header of that datablock. Initial space reserved by INITRANS cannot be reused for data insertion. But if a datablock is fully packed due to less PCTFREE or PCTFREE=0 and when two transactions are accessing the same block, one has to wait till the transaction commits (or rollbacks). Thus row level locks are escalated in to block level locks.

The cost of dynamic creation of transaction slots is trivial and it is better to keep data density higher than compromising data density. The space acquired by dynamically created transaction slots can be reclaimed for future data inserts. Any change in INITRANS will reflect only for newly formatted blocks. So you have to rebuild the table if you want to have more INITRANS for that segment.

ITL slots are acquired in every DMLs happening in that datablock. ITL contains the transaction id for that transaction which is the pointer to an entry in the transaction table* of a rollback segment. Another transaction can always read the data from the rollback segment and (CR) the consistent read statistic is incremented. If new transactions want to update the data it has to wait till the current transaction commits or rollback.

Fig 1. UPDATE operation with an ITL entry pointing to the rollback segment.

While the transaction commits oracle does a fast commit by updating the flag in the transaction table in the rollback segment. In this point of time the ITL in the datablock (called open ITLs) is still pointing to the transaction table of the corresponding to the rollback segments.

Fig 2. Transaction is commited (But the ITL is still open)

If at the later time another transaction visits the datablock, which has an open ITL, to get a consistent copy (CR) it looks transaction table and it is deemed committed. So the transaction revisits the datablock and clears the ITL. This action is called block cleanout. The block clean out is delayed by sometime because of the fast commit and is called delayed block cleanout.

This block cleanout can be forced by a simple full table scan after the transaction or setting the parameter ‘delayed_block_cleanout’ to FALSE. How ever this is no more tunable parameter incurrent versions of Oracle and it defaults to TRUE.

In the slot, there is a pointer to the actual undo records for the transaction which are located in the body of the rollback segment.

Suppose the ITL is still open and the transaction is not yet committed:

A reader on finding the ITL will create a consistent read copy of the block because a view of the block before the change was made is required. To make a CR copy, the undo from the rollback segment is applied to he block.

An updater or deleter on finding the ITL will have to wait.

If the transaction is committed, the event is marked in the transaction table but the block is not revisited, and so the ITL may remain open for some time afterwards. When Oracle next reads the block, the transaction table is checked, the commit is confirmed, and the ITL is closed. This is known as delayed block clean-out.

Fig 3 . Block Cleanout (delayed block cleanout)

If the transaction is rolled back, the insert, update or delete is undone immediately and the ITL is cleared. There is no delay on rollback.

If Oracle crashes before the transaction is committed or rolled back, transaction recovery is performed on opening the database because uncommitted transaction have to be rolled back.