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 |
Rollback Segments Stats |
Trans Table Gets | Number of times that Oracle accessed the
header of this rollback segment number |
Waits | Indicates contention for RBS extents - if this value
is "large" then there my be a requirement for more RBS extents; see
recommendations below |
Shrinks | Number of growth beyond the OPTIMAL value
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. |
Extends | Number of times that this RBS had transactions that
outgrew a single extent into another extent(s). Incremented multiple
times for extensions into multiple extents. |
Rollback Segments Storage |
Segment Size | The actual size of this segment |
Avg Active | Average transaction size for this RBS |
Optimal Size | The optimal size of this segment as defined at
creation of the segment or with the ALTER command at a later
time |
Maximum Size | Size reached by the largest transaction we've had |
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. |
|