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 ROLLBACK s 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?
Column | Explanation |
Segment | The segments name |
Status | Whether this segment is currently
available (ONLINE ) or not |
Size | The actual size of this segment |
OptSize | The optimal size of this segment as defined
at creation of the segment or with the ALTER command at a
later time |
HWMSize | Size reached by the largest transaction
we've had |
Waits | Indicates contention for RBS extents - if
this value is "large" then there my be a requirement for more RBS extents;
see recommendations below |
XActs | current transactions |
Shrinks | Number of growth beyond the
OPTIMAL value (see OptSize in this table) that have been shrunk
afterwards |
Wraps | Wraps 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. |
AveShrink | Average amount that this RBS has been
shrunk |
AveActive | Average 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. |
|