Subject: Rollback Segment Extents
Last Revision Date: 11 September 1994
Author:Walter Lindsay
This bulletin discusses the advantages of multiple equally-sized extents in a
rollback segment. This bulletin complements chapters 4 and 16 of the Database
Administrator's Guide Version 6.0, as well as other sections of the Oracle
documentation. A list of the variables used in this bulletin are listed at the
bottom. 10 to 20 equivalently sized extents per rollback segment is optimal
for nearly all Oracle V6.0 databases.
Background
Although there is no direct link between the number of extents in a rollback
segment and the number of transactions that can make use of that rollback
segment, multiple equally-sized extents will maximize the number and size of
transactions that may make use of that rollback segment. The SYSTEM rollback
segment and deferred rollback segments are special system resources not under
the control of the DBA, so they are ignored in the following discussion.
Assume
o Rollback segment s with N bytes total space.
o Transaction t such that between the first statement in t
and the ROLLBACK or COMMIT that terminates t, Nt bytes are
written to s by all transactions using s, including
transaction t. Whether or not t itself writes any bytes
to s is irrelevant; the total number of bytes written
to s during the internal between the start and end of t is
the value of Nt to assume here. Measuring or calculating
Nt for a transaction is not discussed in this bulletin;
Nt is merely used to derive a useful result.
When the database fills up one extent of s with rollback information for one or
more transactions, it attempts to enter the next extent e of s. When the
database enters extent e after the previous extent has been filled, the
previous contents of extent e will no longer be accessible. Thus, extent e
will never be entered if any active update transaction is making use of e.
This is different than transaction t requesting that the database allocate
it some space: a transaction can be allocated space in an extent that is also
used by other transactions. The prohibition against entering an extent that
has active update transactions in it applies only when extent boundaries are
crossed. Transactions involving only queries (SET TRANSACTION READ ONLY, or
SELECT statements) are not considered update transactions, while transactions
having any UPDATE, INSERT, DELETE, CREATE TABLE ... AS SELECT, etc. statements
are.
If e can not be entered, the database attempts to allocate another extent for
s. If another extent can not be allocated, errors such as ORA-1555 "snapshot
too old", ORA-1562 "failed to extend rollback segment", or ORA-1547 "failed to
allocate extent of size num in tablesapace 'name'" may result.
Example
Let s have 2 equally sized extents, e1 and e2, and let
Nt = .75 * N (1)
If t begins in the first half of e1, then t will complete before the database
will need to re-enter e1 (see Figure 1).
e1 e2 e1
entered entered re-entered
| | |
|-|-------|+-------|--------+------||-------+--------->
| |
transaction t t
begins ends
Figure 1
If t begins in the latter half of e1, then the database will need to re-enter
e1 before t completes (see Figure 2).
e1 e2 e1
entered entered re-entered
| | |
|-|--------+|------|--------+-------||------+--------->
| |
transaction t t
begins ends
Figure 2
Although rollback segment s has N bytes available, t might not successfully run
even though Nt < N.
Multiple Equally-Sized Extents
Clearly, if Nt > N, and t is using rollback segment s, t will not complete
successfully unless more extents are added to s as t runs.
In order to discuss cases where Nt < N, the statistic Ne, the effective number
of bytes in s, is useful.
Ne = N - Nm (2)
where Nm is the number of bytes in the largest extent of s. If
Ne < Nt < N (3)
t may finish without needing another extent to be added to s. If
Nt < Ne (3.1)
t will finish without needing another extent to be added to s unless another
transaction interferes. Assume transaction T such that during the duration
of T, the number of bytes NT written to s is greater than the Nt for any other
transaction t. Confining the discussion to a single rollback segment s,
clearly then the condition
NT < Ne (4)
is sufficient to ensure that T have enough space available in s. If T has
enough space to run, then any other transaction t also has sufficient space.
Maximizing Ne allows the database to more effectively use the space allocated
to a rollback segment.
Ne is maximized for a particular E when each extent (including the initial
extent) of s is the same size. In that case,
N = E * Nm (5)
and thus
N (E - 1)
Ne = N - Nm = N - --- = N * --------- (6)
E E
and, more simply, the useful relationship is
(E - 1)
Ne = N * --------- (7)
E
where E is the number of extents in s. As E grows, Ne will asymptotically
approach N, as illustrated in Figure 3.
E (E - 1)/E Ne
--- ----------- --------
2 0.5 0.5 * N
5 0.8 0.8 * N
10 0.9 0.9 * N
20 0.95 0.95 * N
50 0.98 0.98 * N
Figure 3
Interactions Between Rollback Segments
Inequality (4) was derived by limiting our attention to a single rollback
segment s. When more than one rollback segment is used at once, the
interactions between rollback segments must be considered. When the database
needs to enter the next extent e in s, in addition to checking the status of
e, the statuses of all other rollback segments are also checked. Extent e will
not be entered if there is an active update transaction t in another rollback
segment that began before the oldest (committed) transaction in e. Extent e
will not be entered because entering e will remove information that could
potentially be required for giving t a read-consistent view of data. Read-only
transactions will never deter the database from entering extent e.
Because transaction t may use any (non-SYSTEM) rollback segment in the
database, for multiple runs of t, the Ne available to t will be more consistent
if the rollback segments in the database have equivalent Ne. For more
practical information about cross-rollback segment interactions, see the
bulletin "How Many Rollback Segments to Have."
The Moral
Create each rollback segment with several extents of equal size.
Increasing the number of extents in a rollback segments beyond 20 will have
very little impact on the size of transactions able to use a rollback segment.
Page 20-22 of the ORACLE RDBMS Database Administrator's Guide Version 6.0
discusses reasons for allocating to each rollback segment in the database the
same amount of space. In light of the above discussion, the rollback segments
of a database should have nearly equivalent Ne. (Many sites benefit from an
exception to this general rule. If desired, a rollback segment that has Ne
several times larger than the Ne of the other rollback segments may be created.
For special periods of processing where transactions with large Nt must be run,
the database may be brought up with only the large rollback segment in use.
During normal processing, the other rollback segments are brought online as
well.)
Variables
The variables used in this bulletin are listed in alpahbetical order. Note
that two-letter variable names are used.
e The extent in rollback segment s that the database will
attempt to enter when another extent is needed in s.
e1 The first extent of rollback segment s having two extents.
e2 The second extent of rollback segment s having two extents.
E The number of extents in rollback segment s.
N Size in bytes of rollback segment s.
Ne The 'effective number of bytes' in rollback segment s.
Maximizing this statistic for a rollback segment
is the point of this bulletin.
Nm The number of bytes in the largest extent of rollback
segment s.
Nt Number of bytes written to rollback segment s by all
transactions during the running of transaction t.
NT The maximum Nt for rollback segment s.
s Rollback segment.
t A Transaction.
T The transaction that requires NT bytes in s to complete.
References
ORACLE Database Administrator's Guide, Version 6.0.
ORACLE RDBMS Performance Tuning Guide, Version 6.0.