Rollback segments are one of the least understood areas in database tuning for many DBAs. Most DBAs pay very little attention to tuning rollback segments. Tuning Rollback segment requires a greater understanding of the internal workings of oracle and are difficult to tune by normal means. This note gives a brief introduction to transactions and rollback segments and goes beyond the basics of ‘rollback segment is used for transaction rollback’, so the reader is familiar with what is going on inside a rollback segment.

A database read, modify and write life cycle is called a transaction. A Rollback entry is made for all transactions unless specific clauses are specified. A rollback entry consists of pre-update image value, block address, and data file number, transaction ID and status of the transaction (Active or Committed), this is a single rollback entry. If the transaction fails for any reason, the old image is taken from the rollback segment and this is called transaction rollback. Rollback segments are owned by SYS irrespective of who creates them and are accessible only to Oracle, never to a user.

The Oracle Kernel has many ‘layers’*. Specifically, the Transaction layer (KT) which is between the Cache Layer (KC) and Data Layer (KD) manage rollback Segments. The control is passed from Data Layer to Cache layer through the Transaction layer. The Transaction Layer is responsible for all transactions (including recursive transactions) inside the database such as undo segment allocation, undo generation, ITL changes in the data block and transaction control mechanisms such as Rollback, Savepoint and Commits. It is also responsible for freelist management and extent allocation inside the tablespaces.

Atomicity: A transactions is an atomic unit of processing; it is either performed 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.

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 variable header (transaction layer component 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 committed or rolled back.

Let us take a typical banking transaction transferring $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 following 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 back to the 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 transaction complete. The 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 lock comes in to picture. Row level locks ensure the isolation, which does not allow the other transactions to change the data, because only the committed 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 committed ‘ and the details about other isolation levels are beyond the scope of this discussion.

A transaction cannot read an uncommitted value because the item remains locked until it reaches the commit point. Row level locks that are implemented through ITL data structures ensure this.

Fig 1. ITL entry and Rollback Segments

Each datablock will have an Interested Transaction List (ITL) that 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 practice you don’t need more MAXTRANS unless your AVG_ROW_LENGTH is very small and the segment is frequently updated.

The creation of additional 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). Here 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 for every DML that affects that datablock. An 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. If new transactions want to update the data it has to wait till the current transaction commits or rollback.

While the transaction commits oracle does a fast commit by updating the flag in the transaction table in the rollback segment but the block is not revisited. 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. If Oracle crashes before the transaction is committed (or rolled back) the transaction recovery is performed while opening the database next time by the data from the rollback segments

If at the later time another transaction visits the datablock, which has an open ITL, to get a consistent copy (CR) it looks up the transaction table to find it 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 some discrete time interval because of the fast commit and this 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. Setting this parameter to true some times makes the DBWR overactive. How ever this is no more tunable parameter in current version of Oracle and it defaults to TRUE.

Because of the wider scope on this subject most of the topics are treated lightly and some of the topics like the dreaded ORA-01555 and recovering data from the corrupted rollback segment are left for the next topic. However I believe I have given a detailed outline about the rollback segments and their role in transactions, which will help a DBA in tuning the rollback segments by understanding the internals of the rollback segments related to transactions.