Subject:	        How Many Rollback Segments to Have
Last Modified: 	        11 September 1994
Author:			Walter Lindsay
Document ID:	        10579.1


This bulletin presents general rules for use in selecting the number
of rollback segments to have.  Because the System rollback segment
is a system resource <b>and</b> not under user control, sizing it will not
be discussed, although the general information below does apply.
Thus, 'a database with 3 rollback segments' has 3 plus the System
rollback segment.  Total rollback segment space, the number of
rollback segments, <b>and</b> contention in accessing rollback segments is
discussed.
Background Information
The facts necessary for this discussion:
    o The rollback information for a transaction might go into any
      rollback segment currently in use by that instance.
    o All the rollback information for a transaction is placed into
      one rollback segment.
    o Multiple transactions may be writing into the same rollback
      segment simultaneously.
    o Once all the transactions making use of a rollback segment
      extent have committed or rolled back, that extent may be
      reused.
    o Once a rollback segment extent is reused, any previous
      information in that extent is no longer available.
    o If the next extent in a rollback segment has active
      transactions in it at the time when more space is needed,
      the instance will attempt to extend the rollback segment.
    o SELECT, INSERT, UPDATE <b>and</b> DELETE statements may cause the
      database to read data from multiple rollback segments.
   o Reading from or writing to a rollback segment requires
      momentary exclusive locking of data structures in the rollback
      segment header.
Lower Bound on Rollback Segment Size
A rollback segment should be large enough (or be able to grow large
enough) to
    o accomodate all the rollback information
    o written by all transactions in that rollback segment for the
      duration of any transaction that may occur in the instance
    o during periods of processing when that rollback segment will be
      in use.
This can be used to derive a lower bound on the size of the rollback
segments of an instance.
For most systems, the lower bound will be set by long running
transactions which can be divided into two classes:
    1. In some cases, those transactions will themselves generate
       large amounts of rollback.
    2  In other cases, the transaction may be active for long enough
       for other transactions to generate large amounts of rollback.
Increasing the number of rollback segments in use in a system will
cause fewer transactions to simultaneously write to the same
rollback segment.  Thus, once rollback segments are large enough
to service type (1) transactions above, the issue becomes total
rollback segment space.  In this case, allocating more rollback
segments can reduce rollback segment contention <b>and</b> reduce the
minimum rollback segment size that will work.
The minimum size of rollback segment that will work in a system can
be easily estimated.
    1. Allocate a large number of rollback segments.  The Oracle V6
       RDBMS Database Administrator's Guide (DBA Guide), April 88
       edition, suggests using N / 4 rollback segments, where N is
       the number of simultaneous users accessing the system.
       Allocating this many rollback segments virtually eliminates
       rollback segment contention for most applications, <b>and</b> is
       thus a good number to use in this test.
    2. Run through a heavy processing cycle.  For example, a
       database used to process financial data might have large
       numbers of users entering information during days, <b>and</b> might
       also have monthly, quarterly, <b>and</b> year end closing.
       Simulating these different stages of processing will give
       accurate estimates.
Minimizing Contention <b>and</b> Total Rollback Segment Space
Rollback segment contention can be reduced by allocating more
rollback segments.  The Oracle V6 RDBMS Performance Tuning Guide
contains information about measuring rollback segment contention.
However, contention only becomes a problem by slowing down
applications.  Because a system will have an absolute lower limit
on allowable rollback segment size (as discussed in the preceeding
section), total rollback segment space can be reduced by decreasing
the number of rollback segments until the lower limit of the total
amount of rollback segment space needed is reached.  This limit can
be estimated by:
    1. Create a single rollback segment that could grow very large.
    2. Run through a heavy processing cycle.
    3. The estimate can be found by finding the size of the rollback
       segment.
V$ROLLSTAT
The writes column in the v$rollstat table records the number
of bytes written to a rollback segment since the instance of the
database was started.  To determine the number of bytes written to
rollback segments during a transaction, check this value before <b>and</b>
after running the transaction.  The first row of v$rollstat is for
the System rollback segment.  Ignore the first row for databases with
multiple rollback segments.
Guidelines <b>and</b> Hints
Few applications will benefit from having more than
rollback segments, where N is the number of simultaneous users on
the system.  An educated guess based in part on customer feedback
for a reasonable lower limit is
    N / 20
 rollback segments, where N is the number of simultaneous users.  For
multi-user systems with an Oracle block size of 512 bytes,