What are Rollback Segments?

The primary task of Rollback Segments is to keep the "before image" of data records until changes of transactions have been committed. This makes sure that ROLLBACKs are possible - either explicit by the user or implicit by the PMON process. Oracle uses rollback segments for all transactions that change the database and assigns every such transaction to one of the available rollback segments. Every rollback segment has a transaction table in its header and every write transaction, moreover, must periodically acquire update access to the transaction table of its rollback segment.

Starting with Oracle 9i automatic Undo handling has been introduced. By this, you do no longer have to take care for Rollback Segments manually, but Oracle itself will do so automatically. It is recommended to use this feature. In order to do so, create an Undo tablespace and set UNDO_MANAGEMENT in your init.ora to AUTO.

What do the columns of this report table mean?

ColumnExplanation
SegmentThe segments name
StatusWhether this segment is currently available (ONLINE) or not
SizeThe actual size of this segment
OptSizeThe optimal size of this segment as defined at creation of the segment or with the ALTER command at a later time
HWMSizeSize reached by the largest transaction we've had
WaitsIndicates contention for RBS extents - if this value is "large" then there my be a requirement for more RBS extents; see recommendations below
XActscurrent transactions
ShrinksNumber of growth beyond the OPTIMAL value (see OptSize in this table) that have been shrunk afterwards
WrapsWraps occur whenever a new extent is needed but the next extent in the current RBS is still in use by a transaction, so a new extent has to be allocated. This column tells how many times a wrap occured.
AveShrinkAverage amount that this RBS has been shrunk
AveActiveAverage transaction size for this RBS

What are recommended actions to take?

Cumulative # of Shrinks AveShrink Recommendation
Low
Low
If the value for AveActive is close to OptSize, the settings are correct. If not, then the settings for OPTIMAL are too large.
Note: Be aware that it is sometimes better to have a larger OPTIMAL value - depending on the nature of the applications running, reducing it towards AveActive may cause some applications to start experiencing ORA-01555.
Low
High
Excellent - few, large shrinks!
High
Low
Too many shrinks - OPTIMAL is too small!
High
High
Increase OPTIMAL until the number of shrinks is lower.