Spotlight on Oracle 10.5 - Release Notes

Tune Rollback Segments

Rollback segments store original (or before-image) copies of database blocks that have been changed, but not committed. Rollback segments contain the information that must be restored if a ROLLBACK command is issued.

The configuration of your rollback segments can have an important effect on the performance of your database, especially for transactions that modify data. Any operation that modifies data in the database must create entries in a rollback segment. Queries that read data that has been modified by uncommitted transactions also need to access data within the rollback segments.

Poorly-tuned rollback segments can have the following consequences:

• If there are too few rollback segments, transactions may need to wait for entries in the rollback segment.
• If rollback segments are too small, they may have to grow dynamically during the transaction and later shrink back (if the rollback segment has an optimal size specified).

As well as these performance-related problems, poorly-tuned rollback segments can lead to transaction failure (failure to extend rollback segment), or query failure (snapshot too old).

The following guidelines serve as a starting point for rollback segment configuration for a transaction-processing environment:

• The number of rollback segments should normally be at least one quarter of the maximum number of concurrently active transactions. In batch environments, this can mean allocating a rollback segment for each concurrent job.
• Set OPTIMAL or MINEXTENTS so that the rollback segment has at least ten to twenty extents. This minimizes wastage and contention when a transaction tries to move into an already occupied extent.
• Make all extents the same size.
• Allow ample free space in the rollback segment tablespace for rollback segment expansion. Large, infrequent transactions can then extend a rollback segment when required. Use OPTIMAL to ensure that this space is reallocated when required.

It is very difficult to determine the optimal setting for rollback segments by theory alone. Rollback segments should be carefully observed and storage adjusted as required.