The New DBA FAQs
(Frequently Asked Questions for/from Database Administrators)
by Kevin Loney

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:

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 default setting.

Ideally:

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:

When the transaction has completed, and you have the beginning and ending values, the size of the rollback information generated can be calculated:

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 ROLLBACKS tablespace.

CHOOSE YOUR ROLLBACK SEGMENT

Starting in Version 6.0.33, you can specify which rollback segment your transaction is to use via the 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 commands yet.

MANUALLY SHRINKING ROLLBACK SEGMENTS

In Oracle 7.2, you can force a rollback segment to shrink to a given size:

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.