Subject: The New DBA FAQs, Part 3
From: Kevin Loney <"75663,327"@compuserve.com>
Date: Tue, 19 Nov 1996 13:52:18 -0500
The New DBA FAQs
(Frequently Asked Questions for/from Database Administrators)
by Kevin Loney
Third in a Series.
1992 Original post Kevin Loney
7-FEB-96 Modified Kevin Loney
19-NOV-96 Updated Kevin Loney
This article may not be published.
References: ORACLE DBA Handbook, 7.3 Edition. (Loney)
see www.osborne.com for scripts and sample chapters.
Part 3. Rollback Segment Management
Q: How can I best manage my rollback segments? How can I tell how much rollback space
a transaction takes?
Monitoring Rollback Segments
The following script allows you to quickly see the status of your rollback segments:
FROM DBA_SEGMENTS DS,DBA_ROLLBACK_SEGS DRS
WHERE DS.SEGMENT_NAME = DRS.SEGMENT_NAME
ORDER BY 1;
How do you use this information?
Well, for starters understand that fragmentation in rollback segments is not a bad thing. Each new
transaction in a rollback segment will start in a new extent of that segment. In V7, the default
PCTINCREASE is automatically set to 0 for rollback segments, so each extent of the rollback
segment will be the same size.
NOTE: The OPTIMAL parameter, which lets you set to size to which a rollback segment shrinks, can
be set for each rollback segment - rollback segments cannot inherit this value from their tablespace's
1. each transaction will fit in one extent of a rollback segment.
2. all non-system rollback segments will be located outside of the SYSTEM tablespace
3. all rollback segments will be of equal size
NOTE: if #1 is not true, then you will see a non-zero value for the Wraps column in V$ROLLSTAT.
If wraps are occurring, then individual rollback segment entries are spanning extents within the
rollback segments - in which case the extents may be too small. To increase the size of the extents,
you'll need to drop and recreate the rollback segments.
NOTE: #2 may seem hard to figure out at first. After all, you have to have a second rollback segment
in SYSTEM before you can write data to non-SYSTEM rollback segments. However, once you have
done that, you can create a tablespace called ROLLBACKS (eg) and create rollback segments there.
Once you have done this, you no longer need the non-SYSTEM rollback segment in the SYSTEM
tablespace. No other objects should be created in the ROLLBACKS tablespace.
NOTE: #3 is important because it means that any given transaction, when running, will have a good
chance of running without dynamically extending the rollback segment it resides in (can you say
"recursive calls"?), improving its performance and its chance of succeeding.
You can use the OPTIMAL parameter to specify the size to which a rollback segment should shrink
if it extends past a certain size. If your rollback segments are frequently extending past OPTIMAL,
then your database is doing extra work, and OPTIMAL is probably set too low. Check the AveShrink
and Extends columns of V$ROLLSTAT to see if a rollback segment is frequently extending.
NOTE: The famous ORA-1555 error message ("rollback segment too small") is misleading. The
usual cause of this error is that long-running transactions/queries are occurring within the database at
the same time as short online transactions. When the short transactions complete, the rollback
segment area they use is up for grabs and can be overwritten. As soon as that area is overwritten, the
long- running queries/transactions can no longer maintain a read consistent picture of the data, and
they fail with an ORA-1555. It's a scheduling problem. Run batch and long-running jobs at off-hours.
Monitoring Rollback Area used by Transactions
This topic has been covered in several Oracle technical bulletins and a few IOUG papers, so I'll
present it very briefly. Any transaction's rollback area can be measured by monitoring the
changes in the system statistics tables during its run. This means that a valid reading requires that
nothing else be running during the time the test is taking place.
All you have to do is store the result of the following query immediately before and after running your test:
SELECT SUM(WRITES) FROM V$ROLLSTAT;
When the transaction has completed, and you have the beginning and ending values, the size of the
rollback information generated can be calculated:
ENDING_WRITES - BEGINNING_WRITES - 54 = ROLLBACK INFO GENERATED.
NOTE: The "54" is the rollback overhead that corresponds to the amount of overhead required by a
table insert operation. This assumes that you're storing these values in tables. If you're just tracking
them on paper, then ignore this factor.
This will tell you how much rollback space you need to handle this transaction; knowing how many
transactions will be running at once will give you an idea of how much space will be needed in your
CHOOSE YOUR ROLLBACK SEGMENT
Starting in Version 6.0.33, you can specify which rollback segment your transaction is to use via the
"set transaction use rollback segment" command. Note, however, that this command does not
guarantee that yours will be the only transaction in that rollback segment.
Before you issue the SET TRANSACTION command, issue a COMMIT, to force the start of a
new transaction. You need to do that even if you've just logged in and have not issued any
MANUALLY SHRINKING ROLLBACK SEGMENTS
In Oracle 7.2, you can force a rollback segment to shrink to a given size:
alter rollback segment r0 shrink to 100M;
If you do not specify a size to which the rollback segment should shrink, then it will shrink to its
specified OPTIMAL size. The OPTIMAL value isn't in DBA_ROLLBACK_SEGS, you have to
query V$ROLLSTAT. You cannot shrink a rollback segment to fewer than two extents.
Kevin Loney is the author of the ORACLE DBA Handbook, 7.3 Edition, coauthor of ORACLE: The
Complete Reference, Electronic Edition, and coauthor of Advanced ORACLE Tuning and
Administration, all published under Osborne/McGraw-Hill's Oracle Press imprint. See
www.osborne.com for scripts and sample chapters.