Topic: MANAGING ROLLBACK SEGMENTS
Last Revision Date: 11 September 1994
Author:Mark Ramacher and Harmeet Bharara
Introduction
This paper is a discussion on configuring a rollback segment
tablespace that is designed to meet the needs of your database
applications. We present strategies for version 6 plus some
strategies based on the initial observations of ORACLE 7
functionality. It is a good idea to familiarize yourself with the
documents listed at the end of the paper. They provide the
background information that leads to many of the
recommendations made here.
Getting Started
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 <b>and</b> advances the current
pointer by the size of the record. This paper refers to the
current writing location for records as the "head" of the rollback
segment. Transaction records are guaranteed not to be
overwritten until the transaction generating them is either
rolled back or committed. For the purposes of this discussion,
we will use the term "tail" 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 space needed will fit into the available disk space.
Make sure that there is enough segments to avoid contention as
processes access them. But also make sure that individual
rollback segments are large enough to h<b>and</b>le their transaction
load. The next section discusses balancing these two
requirements.
Size <b>and</b> Number of Rollback Segments
There are some basic principles that apply to rollback segment
space management in both version 6 <b>and</b> ORACLE 7. Some of the
most important are listed:
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 <b>and</b> used out of
order as the head tries to advance.
5. If the head can't use the next extent, it allocates another
extent <b>and</b> 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.
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 <b>and</b>
catch the tail. This causes the segment to extend in size, per the
principles mentioned above. Second, if you have long running
queries that access data that frequently changes, you want to
make sure that the rollback segment doesn't wrap around <b>and</b>
prevent the construction of a read consistent view. Refer to
the ORACLE Database Administrator's Guide for discussions on
"read consistency" <b>and</b> on "avoiding the snapshot too old error".
The size needed for a rollback segment depends directly on the
transaction activity of your database. Be concerned about the
activity during normal processing of the database, not with rare
or semi-frequent large transactions. These special cases are to
be dealt with separately.
The number of rollback segments needed to prevent contention
between processes can be determined with the help of the
monitor rollback display <b>and</b> with the use of the v$waitstat
table. The rollback monitor column "header waits/sec" gives an
indication of the current rollback segment contention. Waits are
a definite indication of contention. The following v$waitstat
query will display number of waits since instance startup:
SELECT * FROM FROM V*WAITSTAT WHERE
OPERATION = 'buffer busy waits' <b>AND</b>
CLASS = 'undo segment header';
RTSS <A HREF="/cgi-bin/iv/do.pl/Lookup?WwwID=bul:99267.458&WwwCid=011504:31:44.25871">bulletin 99267.458</A> has a good description of the v$waitstat
view.
To find out the size <b>and</b> number of rollback segments needed to
h<b>and</b>le normal processing on the database you need to do some
testing. A good test is to start with small rollback segments <b>and</b>
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 <b>and</b> 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.
5) Activate only the rollback segments that you are testing by
making the status "in use". The only other segment that should
be "in use" is the system rollback segment.
6) Run transactions <b>and</b> 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 <b>and</b> 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, <b>and</b> rerun the test. Otherwise you may be wasting
space.
You now have some good base estimates for the size <b>and</b>
number of your rollback segments needed during normal
processing. Remember that h<b>and</b>ling large transactions has not
been discussed yet. This topic is presented in the later section
on configuring a version 6 rollback tablespace. 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.
Number <b>and</b> Size of Extents
For sizing rollback segment extents, we strongly recommend
that each extent be of the same size. In fact, for all strategies
listed below we assume that all rollback segments have extents
of the same size <b>and</b> that the size of the rollback tablespace is
some multiple of the common extent size. The number of
extents for an individual segment should be around 20. For
background on these recommendations see RTSS bulletin
99267.6.
New Functionality in ORACLE 7
ORACLE 7 provides new features to simplify the management of
rollback segments. These additions assist in three areas;
managing segment size, managing segment availability, <b>and</b>
monitoring rollback activity. Note that these features are in
addition to the functionality of version 6. However, one thing
that has been removed in ORACLE 7 is the ability to set
PCTINCREASE on rollback segments. It is now always set to 0.
Recall that rollback segment extents should all be the same size,
so there really isn't a need for this option.
In the rollback segment storage clause, the PCTINCREASE
parameter has been replaced by a parameter called OPTIMAL.
This specifies the optimal size of a rollback segment in bytes. It
can also be specified in kilobytes or megabytes. The RDBMS tries
to keep the segment at its specified optimal size. The size is
rounded up to the extent boundary, which means that 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, it will
eventually deallocate extents to shrink back to this size. The
process of deallocating extents is performed when the head
moves from one extent to the next. At this time, the segment
size is checked <b>and</b> the RDBMS determines if the next extent
should be deallocated. The extent can only be deallocated if
there are no active transaction in it. If necessary, the RDBMS
will deallocate multiple extents at one time until the segment
has shrunk back to its optimal size. The RDBMS always
deallocates the oldest inactive extents as they are the least
likely to be used for read consistency.
The rollback segment monitor screen has been enhanced to help
in determining how successfully you have chosen your OPTIMAL
size. Among the new statistics are the most extents that were
allocated to the rollback segment, the OPTIMAL size, <b>and</b> the
number of shrinks performed. Based on these statistics one can
analyze the OPTIMAL setting for a rollback segment. Refer to the
Database Administrators Guide for a more detailed discussion on
this topic.
The other new functionality in ORACLE 7 is in the dynamic
onlining <b>and</b> offlining of rollback segments. By default whenever
a rollback segment is created it is offline <b>and</b> must be acquired
by the instance or brought online. If a rollback segment had to
be brought online in version 6, the database had to be shutdown
<b>and</b> restarted. But in ORACLE 7 this is no longer necessary. The
SQL comm<b>and</b> ALTER ROLLBACK SEGMENT with the ONLINE option
can be used. To take a rollback segment offline you can use the
OFFLINE option. If a segment is taken offline <b>and</b> the specified
rollback segment does not have any active transactions, it is
immediately taken offline. But if the specified rollback segment
contains rollback data for active transactions then it is taken
offline once all the active transaction are either committed or
rolled back. No new transactions are written to a rollback
segment that is either marked offline or is waiting for other
transactions to complete so that it can be brought offline. To
become available again, a rollback segment that is taken offline
has to be explicitly brought back online or it has to be specified
in the init.ora when the instance is started. This means that
when a public rollback segment is taken offline it remains
offline, even if the database is shutdown <b>and</b> restarted.
Setting Up Rollback Segments in Version 6
After calculating the size <b>and</b> the number of rollback segments
required, it is time to plan out 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
Estimating the size of the rollback information (undo) generated
by a transaction can be done with the help of the following
script.
UNDO.SQL
set feedback off
set termout off
column name format A40
define undo_overhead=54
DROP TABLE undo$begin;
DROP TABLE undo$end;
CREATE TABLE undo$begin ( writes number );
CREATE TABLE undo$end ( writes number );
INSERT INTO undo$begin
SELECT sum(writes) FROM v$rollstat;
set termout on
set feedback on
UPDATE test1 SET col1=99 WHERE col3 ='Y';
REM Note : The test transaction can be executed from a SQL
script file.
set termout off
set feedback off
INSERT INTO undo$send
SELECT sum(writes) FROM v$rollstat;
set termout on
set feedback on
SELECT ( ( e.writes - b.writes) - &undo_overhead)
" number of bytes generates"
FROM undo$begin b, undo$end e;
set termout off
set feedback off
DROP TABLE undo$begin;
DROP TABLE undo$end;
The value reported by this script is the undo generated during
the transaction. So the result of the test is accurate if it is
performed while nothing else is occurring on the database. The
UNDO_OVERHEAD defined in the script is a constant which
compensates for the unavoidable overhead of the "INSERT INTO
undo$begin ..." statement.
The TXRBS.SQL script shown below identifies all users with active
transactions <b>and</b> the rollback segment each transaction is using.
TXRBS.SQL
SELECT r.name "ROLLBACK SEGMENT NAME ",
l.pid "ORACLE PID",
s.pid "SYSTEM PID ",
NVL ( p.username , 'NO TRANSACTION'),
p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE l.pid = p.pid(+)
<b>AND</b> TRUNC (l.idl(+)/65536) = r.usn
<b>AND</b> l.type(+) = 'TX'
<b>AND</b> l.lmode(+) = 6
ORDER BY r.name
The recommended configurations for the three different types of
scenarios follow.
Steady Average Transaction Rate
For databases with no abnormally large transactions, there is a
straight forward way of configuring the tablespace. Create a
rollback tablespace with exactly enough space to hold your
determined number of rollback segments at the minimum
coverage size. Create the rollback segments so that they
initially allocate all the space they will need. There should be no
free space in the tablespace when you are done creating the
rollback segments. Make all of the extents the same size <b>and</b>
remember to allocate 20 extents per segment.
Frequent Large Transactions
For systems with frequent large transactions, you need to be
concerned with the rollback segment that these large
transactions are going to use. A scheme to use is to create your
rollback segments such that one is large enough to h<b>and</b>le the
largest of your transactions, in addition to the normal
transaction load placed upon it. So if your largest transaction is
45M, <b>and</b> the minimum coverage size is 10M, the large rollback
segment should be 55M. All other rollback segments should be
created at minimum coverage size. With this configuration, you
need to make sure that the large transactions are placed upon
the large rollback segment by using the SET TRANSACTION USE
ROLLBACK SEGMENT command. This command is available <b>and</b>
documented in the README file for version 6.0.33 <b>and</b> higher of
ORACLE. If large transactions often occur at the same time, you
should enlarge more of the rollback segments <b>and</b> disperse the
transactions on the different segments.
Infrequent Large Transactions
For systems with rare large transactions, it is not necessary to
have a large rollback segment available at all times. Knowing
you have time to work with, you can rearrange things <b>and</b>
prepare for the occurrence of these large transactions. One
technique is to recreate the rollback tablespace with one large
segment <b>and</b> enable it for the duration of the large transaction.
Restore the rollback tablespace to its normal configuration when
the transaction is done. You also may elect to create another
rollback tablespace with a large rollback segment in it <b>and</b> use
the SET TRANSACTION USE ROLLBACK SEGMENT comm<b>and</b> to run the
large transaction.
Your database may have some combination of the three
scenarios above, so you may want to consider different rollback
configurations during different phases of production. For
example, a financials system may require adjustment of the
rollback segment configuration during the close of a financial
quarter, when large transactions <b>and</b> reports are frequent.
Knowing your application's changing requirements is a key to
successfully h<b>and</b>ling rollback segments.
ORACLE 7 Configuration
The new features of rollback segments in ORACLE 7 add a couple
of new options when choosing configuration strategies. The use
of the OPTIMAL clause is a very h<b>and</b>y tool. But beware of a
couple of points when basing your strategy around this
functionality. Point one is that extent allocation <b>and</b>
deallocation are expensive in regards to performance. This
means that an OPTIMAL setting may decrease performance if it
is too low. Point two is that you are never guaranteed when a
rollback segment will shrink down to its optimal size.
Remember from the previous discussion that a rollback segment
only shrinks when it attempts to move into another extent <b>and</b>
sees that the extent meets the requirements for deallocation.
Now lets revisit the three scenarios discussed for version 6, but
explain what the new options for each case, if any, are in
ORACLE 7.
Steady Average Transaction Rate
For databases where the transaction rate has no fluctuation, ie.
there are no abnormally large transactions, the same basic
strategy used in version 6 is recommended for ORACLE 7. 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 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. You do not want to to make OPTIMAL smaller than
the minimum coverage size. Otherwise 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 h<b>and</b>le 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
will introduce contention <b>and</b> will 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 <b>and</b> make sure that large transactions
use these segments. The SET TRANSACTION USE ROLLBACK SEGMENT
comm<b>and</b> 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 <b>and</b> there is no
systematic control of large transactions. This option is
recommended in an environment where the large transactions
are issued from a controlled environment ie. an application
which will set the transaction to the appropriate rollback
segment.
Infrequent Large Transactions
For cases were large transactions are rare, there is an additional
option given with ORACLE 7. You can use the 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 <b>and</b> 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 r<b>and</b>omly make one of the segments
grow <b>and</b> 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.
As you can see, you must carefully consider what the
requirements are for your database before trying to take
advantage of new functionality. The new OPTIMAL feature for
rollback segments is a great tool for some environments, but not
all. Note that as of the completion date of this paper, ORACLE 7
was still pre-production. We strongly recommend that you keep
a look out for support bulletins <b>and</b> documentation that may
present still further suggestions <b>and</b>/or functionality changes.
The information presented here should be enough to get you
started with Oracle's newest technology.
References
RTSS <A HREF="/cgi-bin/iv/do.pl/Lookup?WwwID=bul:99267.6&WwwCid=011504:31:44.25871">Bulletin 99267.6</A> "Rollback Segment Extents" by Walter
Lindsay.
RTSS <A HREF="/cgi-bin/iv/do.pl/Lookup?WwwID=bul:99360.82&WwwCid=011504:31:44.25871">Bulletin 99360.82</A> "How Many Rollback Segments to Have"
by Walter Lindsay.
RTSS Bulletin <.........> "Rollback Segment Sizing for Large
Operations", by Linda Fong <b>and</b> Vijay Oddiraju.
RTSS <A HREF="/cgi-bin/iv/do.pl/Lookup?WwwID=bul:99267.458&WwwCid=011504:31:44.25871">Bulletin 99267.458</A> "V$ View Description" by Saleem Haque.
RTSS <A HREF="/cgi-bin/iv/do.pl/Lookup?WwwID=bul:99979.315&WwwCid=011504:31:44.25871">Bulletin 99979.315</A> "ORA-1562 <b>And</b> Open Transactions in
Rollback Segments" by Tuomas Pystynen.
"An Internal Look at Rollback Segments", Lan Nguyen <b>and</b> Walter
Lindsay, 1991 International Oracle User Week Proceedings,
Volume 2, paper #537
</PRE>