认真就输

Subject: Creating, Optimizing, and Understanding Rollback Segments
Creation Date: 30-JUN-1998

Creating, Optimizing, and Understanding Rollback Segments
=========================================================

Purpose
——-

This paper is a discussion on configuring a rollback segment
tablespace that is designed to meet the needs of your
database applications.

Scope & Application
——————-

For DBA’s requiring information about configuring a rollback
segment tablespace

Introduction
————

A rollback segment consists of contiguous multi-block pieces
called extents. The segment uses these extents in an ordered
circular fashion, moving from one to the next after the current
extent is full. A transaction writes a record to the current
location in the rollback segment and advances the current
pointer by the size of the record. (The current writing
location for records is the "head" of the rollback segment).
The term "tail" is used to refer to the location on the
rollback segment that is the beginning of the oldest active
transaction record.

To determine a general rollback segment configuration,
balance the number of segments against the size of each
segment, such that the total size is small enough to fit
into the available disk space. There should be enough
segments to avoid contention as processes access them. It’s
also important to make sure that individual rollback
segments are large enough to handle their transaction load.
Oracle allocates rollback segments in a round-robin fashion
among all online rollback segments (with the exception of
SYSTEM) to try to spread transactions evenly. The next
section discusses balancing requirements of size and
concurrency.

Size and Number of Rollback Segments
————————————

1. A transaction can only use one rollback segment to store
all of its rollback (undo) records.

2. Multiple transactions can write to the same extent.
3. The head of the rollback segment never moves into a
rollback extent currently occupied by the tail.

4. Extents in the ring are never skipped over and used out
of order as the head tries to advance.

5. If the head can’t use the next extent, it allocates
another extent and inserts it into the ring.
From these principles it is evident that transaction time as
well as transaction size is important. For instance, a
transaction that only modifies one byte but waits a long
period of time before ending could cause a rollback segment
to extend if the extent it occupied is needed again.

Size Considerations
——————-

There are two issues that need to be considered when
deciding if your segment is large enough. First, you want
to make sure that transactions will not cause the head to
wrap around too fast and catch the tail. This causes the
segment to extend in size, as discussed above. Second, if
you have long-running queries that access data that changes
frequently, you want to make sure that the rollback segment
doesn’t wrap around and prevent the construction of a read-
consistent view. Read-consistency is discussed in more
detail later in the section on avoiding `ORA-1555 – snapshot
too old’ errors.
The size needed for a rollback segment depends directly on
the transaction activity of the database. The primary
concern should be the activity during normal processing of
the database, not rare or semi-frequent large transactions.
These special cases should be dealt with separately.
The number of rollback segments needed to prevent contention
between processes can be determined with the use of the
v$waitstat table. Waits are a definite indication of
contention. The following v$waitstat query will display
number of waits since instance startup:
SELECT CLASS, COUNT FROM V$WAITSTAT
WHERE CLASS = ‘%undo%’;

Any non-zero value in the count column indicates rollback
segment header contention.
To find out the size and number of rollback segments needed
to handle normal processing on the database you need to do
some testing. A good test is to start with small rollback
segments and allow your application to force them to extend.
Here are the steps to run such a test:
1) Create a rollback segment tablespace.

2) Select a number of rollback segments to test and create
them in the tablespace.
3) Create the rollback segments so that all extents are the
same size. Choose an extent size that you suspect will
need between 10 to 30 extents when the segments grow to
full size.
4) Each rollback segments should start with two extents
before the test is run. This is the minimum number of
extents any rollback segment can have.

6) Only the rollback segments that you are testing and the
SYSTEM rollback segment should be online.
Run transactions and load typical of the application.

7) Watch for rollback segment contention.

8) Watch for the maximum size a rollback extends to.
The maximum size any one of the rollback segments reaches
during the test is the size you want to use when
configuring. This size we will call the "minimum coverage
size". If you see contention, adjust the number of segments
and rerun the test. Also, if the largest size requires
fewer than 10 extents, or more than 30, it is a good idea to
lower or raise the extent size, respectively, and rerun the
test. Otherwise, space may be getting wasted during the
test and this would throw the number off.
At the end of this test, you will have some good base
estimates for the size and number of rollback segments
needed for normal processing. However, handling large
transactions has not been discussed yet. Calculating the
segment size needed for these types of transactions as well
as how to meet the requests for the calculated space will be
addressed.
For sizing rollback segment extents, it is strongly
recommended that each extent be of the same size. In fact,
for all strategies listed below it is assumed that all
rollback segments have extents of the same size and that the
size of the rollback tablespace is some multiple of the
common extent size. The minimum number of extents for an
individual segment should be around twenty for best
performance.

Why size a rollback segment with a ‘minimum’ of twenty extents?
Rollback segments dynamically allocate space when required and deallocate
space when no longer needed (if the OPTIMAL parameter is used). The fewer
extents that a rollback segment consists of, the larger the less granular
these space allocations and deallocations are. For example, consider a 200
megabyte rollback segment which consists of only two 100-megabyte extents. If
this segment were to require additional space, it would allocate another 100M
extent. This immediately increases the size of the rollback segment by 50%
and potentially acquires more space than is really needed. By contrast, if
the rollback segment consisted of twenty 10-megabyte extents, any additional
space required would be allocated in 10-megabyte pieces. When a rollback
segment consists of twenty or more extents, any single change in the number of
extents will not move the total size of the rollback segment by more than 5%,
resulting in a much smoother allocation and deallocation of space.
Given this, increasing the number of extents beyond the suggested twenty will
make space allocation and deallocation even *more* smooth. However, in-house
testing has showed rapidly diminishing returns when increasing the number of
extents past twenty. In addition, allocating and deallocating extents is not
a cost-free operation. The database will have a performance degradation when
performing extent operations. The cost for individual extents is minor, but a
rollback segment which is constantly allocating and deallocating tiny extents
can cause even a minor cost to add up.

When is the SYSTEM rollback segment used?

When a database is first created using the CREATE DATABASE
command, only a single rollback segment is created. This is
the system rollback segment and it is created in the system
tablespace. The system rollback segment has one basic
difference from any other rollback segment, including any
other rollback segments that are created in the system
tablespace. This difference is that the system rollback
segment can only be used for transactions that occur on
objects inside the system tablespace. This is done because
the main purpose of the system rollback segment is to handle
rollback for DDL transactions – that is transactions against
the data dictionary tables themselves. Making the system
rollback usable only for the system tablespace was simply an
easy way to enforce that. It is possible for the system
rollback segment to be used for non-data dictionary tables,
but only if those tables are created inside the system
tablespace (which is very bad development practice).

Any other rollback segments inside the system tablespace do
not have this restriction. Prior to Oracle 7.1.5, during database
creation, at least one rollback segment must be created in
system. If this is not done, additional tablespaces can never
be created (because the CREATE TABLESPACE transaction by
definition affects objects outside the system tablespace.)
Attempting to perform such an operation will generate an
`ORA-1552, cannot use the system rollback segment for non-
system tablespace x’ ([BUG:232566]). You will get this error
any time rollback is generated for a tablespace outside of
system and the only rollback segment online is system. If any
other rollback segment is online, this error will not occur.
Normally one of the first operations of creating a new
database is to create a rollback tablespace and place all
non-system rollback segments there. The additional system
rollback segment is either off-lined or dropped. Leaving it
online can lead to fragmentation in the system tablespace.

Read Consistency and ORA-1555 errors
————————————

A common error to receive when issuing long-running
transactions is `ORA-1555, `Snapshot too old’. In the
Oracle Server messages manual for this error, the cause
given is `Rollback Segment too small.’ That is misleading,
as it is possible to get this error message with any size
rollback segment and increasing the size of rollback
segments may not necessarily help.

When a transaction is started, Oracle keeps track of the
time (actually the SCN) that it was first issued. While
gathering row information to fulfill the statement, Oracle
checks each row to make sure that none of the rows was
modified after the begin date of the current transaction.
If a row is located which was modified, Oracle goes out to
the rollback segment for the value of that row which existed
when the current transaction started. For uncommitted
changes, the information will always exist in the rollback
segment, and there are no snapshot issues. However, if
there is a change that was committed after the current
transaction started, then the rollback space where that
transaction information is stored may get overwritten by
subsequent transactions (or eliminated entirely by an
OPTIMAL shrink). If Oracle tries to get information for
that row and the rollback transaction no longer exists, a
read-consistent result set cannot be returned and an ORA-
1555 error is generated.

No matter what size rollback segment(s) exists on the
database, it is possible for committed transactions to be
overwritten. The larger (and more) rollback segments that
exist in the system, the less often transactions will be
overwritten. This is the basis for the Server Messages
error explanation of `rollback segment too small’.
Note: A common (and incorrect) assumption is that the ORA-
1555 message indicates that the rollback segment being used
by the current transaction is too small. Most commonly it
is SELECT statements which generate ORA-1555 errors. SELECT
statements do not generate rollback information. Rollback
information is generated for a `CREATE TABLE AS SELECT’
statement, but it is the CREATE, rather than the SELECT
which does so.

The best way to handle ORA-1555 errors is simply to start
the long-running transaction when there are few (or no)
other transactions running against the database. So long as
there are updates occurring to the table(s) being accessed,
snapshot errors are possible. If possible, it also helps to
split the transaction into smaller pieces that take less
time to run. However, if neither of these is possible, there
are a couple of items to keep in mind when trying to resolve
ORA-1555 errors by modifying rollback configurations:

Make sure all rollback segments are online. The more
segments are online, the more transactions are spread out
and the less often any individual transaction will be
overwritten. Exceptions to this include cases where there
is a massive rollback segment that is reserved for other
uses and tiny rollback segments that `wrap’ head to tail
often. Having such tiny segments online can actually make a
1555 worse.

Make all rollback segments that are online (except SYSTEM)
approximately the same size. Transactions are assigned
rollback segments in a round-robin fashion (not exactly, but
close enough). Since a transaction which can cause an ORA-
1555 can appear in any segment (other than SYSTEM), the
likelihood of receiving and ORA-1555 will almost always be
dictated by how fast the smallest rollback segment wraps
(and rewrites old transactions)

Setting OPTIMAL on Rollback Segments
————————————

In the rollback segment storage clause, there is a parameter
called OPTIMAL. This specifies the `optimal’ size of a
rollback segment in bytes. When set, Oracle will try to
keep the segment at the specified size, rounded up to the
extent boundary. The RDBMS tries to have the fewest number
of extents such that the total size is greater than or equal
to the size specified as OPTIMAL. If additional space is
needed beyond the optimal size, the rollback segment will
expand beyond optimal to accommodate the current
transaction(s), but will eventually deallocate extents to
shrink back to this size.

The process of shrinking back to OPTIMAL is not
instantaneous. When a transaction that has expanded the
segment beyond the set value has ended, the rollback segment
does not `snap’ back to the optimal size. This could have
severe performance implications and cause considerable
problems with read-consistency. The process of deallocating
extents is performed during transactions after the one which
caused the segment to extend.When the head moves from one
extent to the next during a transaction, the segment size is
checked. If the segment size is currently above the
optimal, the RDBMS determines if an extent should be
deallocated. An extent will only be deallocated if there
are no active transactions in the next two extents (Why the
next two extents? If only the next extent is checked for
availability, Oracle might deallocate it then try to
continue with the current transaction only to find that the
following extent is used. At that point, Oracle must
reallocate an extent.). If necessary, the RDBMS will
deallocate multiple extents in a single transaction (one
each time the head crosses into a new extent) until the
segment has shrunk back to its optimal size. Since extents
are deallocated by the current transaction(s), the circular
nature of rollback segments guarantees that these are the
oldest inactive extents (and the least likely to be used for
read consistency).
The OPTIMAL clause is a very handy tool, however, be aware
of a couple of points when using it.

Point one is that extent allocation and deallocation is
expensive in regards to performance. This means that an
OPTIMAL setting may decrease performance if it is too low
(Actually, it will always decrease performance. It may
noticeably decrease performance if set too low). The best
solution for performance is to set all of your rollback
segments to a size where every single transaction will
always fit. In practical terms, this may well be impossible
– if your largest transaction is 2 gigabytes and you require
ten rollback segments for concurrency. The segments should
have an optimal size large enough that 90% or better of
transactions will fit without having to extend the segment.
In addition, the rollback tablespace should be large enough
that when all rollback segments are at the optimal value,
there is plenty of space for them to extend when it becomes
necessary. For example, if your segments are set with an
optimal value of 500 megs and you know that there is a
particular transaction that runs infrequently, but requires
one gigabyte when it does run, your rollback tablespace must
have 500 megs free (absolute minimum) when all segments in
that tablespace are at optimal. Since you cannot normally
count on all segments being at optimal or on that big
transaction being the only one using space in the rollback
segment, prudence suggests having a bit more space available
than the absolute minimum.

Point two is that you are never guaranteed when a rollback
segment will shrink down to its optimal size. The rollback
segment only shrinks when a transaction attempts to move
into another extent and sees that the extent meets the
requirements for deallocation. If a rollback is a candidate
for shrinks, but no transactions are allocated to it, it
will not shrink. The obvious corollary is that a segment
must be online to shrink.

Bringing Rollback Segments Online and Offline
———————————————

By default whenever a rollback segment is created it is
offline and must be acquired by the instance or brought
online. The SQL command ALTER ROLLBACK SEGMENT can be used
to bring a rollback segment online or offline while the
instance is running. If a segment is taken offline and the
specified rollback segment does not have any active
transactions, it is immediately taken offline. But if the
segment contains active transactions then it is taken
offline only after all the active transaction are either
committed or rolled back. No new transactions will be
written to a rollback segment that is waiting for other
transactions to complete so that it can be brought offline.
To become available again without shutting down the
instance, a rollback segment that has been taken offline
must be explicitly brought back online.

Rollback segments are also brought online during instance
startup. There are two basic ways that rollback segments
can be brought online. The init.ora file can contain a
parameter called rollback_segments. You can add this line
to the init.ora and specify all rollback segments that are
to be explicitly brought online during instance startup.
Note that if any of these rollback segments do not exist, an
ORA-1534 error is returned and the instance does not
complete startup. The segments must already exist before
you can add them to the list to be brought online.

Even if rollback segments are not explicitly brought online
by being added to the "rollback_segments" parameter, they may
be brought online during startup. Oracle will do this based
on the values of two other parameters: transactions and
"transactions_per_rollback_segment". On startup, Oracle will
divide transactions by "transactions_per_rollback_segment" and
round up. If the resulting number is less than the number
of rollback segments brought online by the "rollback_segments"
parameter, nothing is done. However, if the resulting
number is greater and more rollback segments exist which are
not currently online, the segments will be brought online
until the number is reached or until there are no other
offline segments. If neither parameter is set in the
init.ora, they will use default values.

Public vs. Private Rollback Segments
————————————

A common misconception about `Private’ rollback segments is
that they are segments reserved for a particular use or a
particular transaction. The only difference between Public
and Private rollback segments is in relation to the Parallel
Server Option. A public rollback segment can be acquired
implicitly by any instance in a parallel server environment.
A private rollback segment must be explicitly acquired by a
particular instance using the rollback_segments parameter.
If not using OPS, the difference between the two is
insignificant.

Configuring Rollback Segments
—————————–

After calculating the size and the number of rollback
segments required, you should plan the configuration of the
rollback tablespace. This section discusses three different
transaction environments:
1) a steady average transaction rate
2) frequent large transactions
3) infrequent large transactions
The recommended configurations for the three different types
of scenarios follow.
Steady Average Transaction Rate
——————————-

For databases where the transaction rate has no fluctuation,
there is a straightforward way to configure the tablespace.
Create a tablespace that will fit your calculated number of
rollback segments with the minimum coverage size you have
determined. Make all extents the same size. For a safety
net, you may allocate some additional space in the
tablespace to allow segments to grow if they need to. If
you elect to do this, use the OPTIMAL feature to force all
rollback segments to free up any additional space they
allocate beyond their determined size requirement. Do not
make OPTIMAL smaller than the minimum coverage size. If
this is done, performance will suffer due to excessive
segment resizing.

Frequent Large Transactions
—————————

Databases with frequent large transactions are the hardest
case to deal with. We will define frequent as the time
between large transactions being less than the time needed
to allow all rollback segments to shrink back to optimal
size. A large transaction is one in which we don’t have
enough space to create all rollback segments of the size
necessary to handle its rollback information. Since we
can’t depend on the segment shrinking in time to allow
repeated large transactions, OPTIMAL is not really an option
for this environment. There are basically two options that
you can choose from for your rollback segment tablespace.
One is to reduce the number of segments so that all are
large enough to hold the largest transactions. This option
may well introduce contention and cause some degradation in
performance. It is a reasonable choice if performance is
not extremely critical. The second option is to build one
or more large rollback segments and make sure that large
transactions use these segments. The SET TRANSACTION USE
ROLLBACK SEGMENT command is necessary to control the
placement of these large transactions. This option is
difficult to implement if large transactions are being run
with adhoc queries and there is no systematic control of
large transactions. It is recommended in an environment
where the large transactions are issued from a controlled
environment (i.e. an application) that can set the
transaction to the appropriate rollback segment.

Infrequent Large Transactions
—————————–

For cases where large transactions are rare, you can use
OPTIMAL feature to set up a flexible rollback segment
scheme, one in which you are not concerned about which
rollback segment the large transaction falls upon. The key
is to leave enough free space in the rollback tablespace
that the largest transaction’s rollback information can fit
entirely into it. To do this, create the rollback
tablespace with the space needed for your calculated number
of segments and their minimum coverage size plus this
additional space. Then set the OPTIMAL for each segment
equal to the minimum coverage size. What you will see is
that the large transactions will randomly make one of the
segments grow and eat up the free space, but the segment
will release the space before the next large transaction
comes along. Note that you are sacrificing some performance
for this flexibility.