Typical Failure Causes

Typically, rollback failures are associated with larger transactions. This is because they generate a much larger amount of undo or they require a larger amount of undo to generate a read consistent image. Large transaction failures can usually be attributed to the following rollback related problems:

  • Inadequate space in the rollback segment tablespace for rollback segment expansion.

  • Improper storage specifications for the rollback segment being used resulting in that segment exceeding its MAXEXTENTS value.

  • Improper scheduling allowing other transactions to cause "snapshot too old errors" to occur in the transaction (can also be caused by improper INITRANS setting).

In addition to the SYSTEM rollback segment created when the database is built for use strictly by the SYSTEM tablespace, there must be at least one additional rollback segment created for normal operations. So, how many rollback segments should you configure?

Usually the number of private rollback segments is determined by determining how many concurrent users will access the database and deciding how many users should be assigned to each rollback segment (by specifying the MINEXTENTS value). For example if you have 100 concurrent users and you want (on the average) 20 users per rollback segment, then the MINEXTENTS would be set to 20 for each of 5 rollback segments. For private rollback segments the calculated ratio of the initialization parameters transactions/transactions_per_rollback_segment rounded up to the nearest integer should be used to determine the number of rollback segments created.

For absolute best performance, we recommend you limit each rollback segment to 5 concurrent users. Certainly, rollback segments can handle more concurrency, but 5 is a magic number. It is the maximum number of concurrent users that can share a given block within an Oracle rollback segment. Once you have more than 5 concurrent users in a given rollback segment, you will very slowly see performance degrade. In reality, however, we have seen that 10 to 20 users per rollback segment is sufficient.

Having too many users per rollback segment can result in wait contention for the header or undo blocks of that rollback segment. If you are experiencing waits on rollback segments, then you need to increase the number of rollback segments. To determine if you are experiencing waits, look in the V$WAITSTAT view. The CLASS column will contain a list of the different segment  types that are experiencing waits. Look for the undo block wait events. If these are high, and particularly if the TIME column is high, then you need to probably consider looking at the creation of additional rollback segments. If you are seeing high undo header waits, you may need to increase the FREELIST setting on the undo segment as this indicates contention for header information by concurrent sessions.

This is all a moot point with automated undo since there is no configuration required.

Sizing Rollback Segments

How you size rollback segments can impact performance. If the size of the rollback segment extents are to small, or you have not allocated enough of them, then Oracle will have to constantly be extending the rollback segment to accommodate larger transactions. This can have significant performance implications and can also lead to failures of long running transactions due to an inability to generate read consistent images.

With manual rollback segments, the sizing of rollback segments depends on many factors, such as the number of concurrent users, the size of the average transaction, the size of the largest expected transaction, and the available space. The STORAGE clause parameters can be derived using information from the V$ROLLSTAT view (which contains dynamic data about actual rollback segment statistics).

With automated undo in Oracle 9i or 10g, you do not have to worry about the sizing of the undo segments. The Oracle database will determine the optimal settings for the storage parameters of these segments. It will also determine the optimal number of undo segments.

INITIAL

The INITIAL parameter sets the size of the initial extents for the rollback segment. The value of INITIAL should be set to the size of the average transaction. If you find you need to reset a rollback segment's INITIAL value you will need to drop and re-create the rollback segment. Note that you can not drop the SYSTEM rollback segment, so unless you are prepared to edit the sql.bsq file (where this rollback segment gets created) and recreate the database, there isn’t much you can do to resize the SYSTEM rollback segment.

Once you have created your initial set of rollback segments, you can monitor the database to determine if the sizing is acceptable. For INITIAL, the critical value in the V$ROLLSTAT table is AVEACTIVE, averaged over the online rollback segments. If you find that your AVEACTIVE values are larger than the INITIAL setting for your rollback segment, consider recreating your rollback segments with INITIAL set to the size of AVEACTIVE.

NEXT

This one is easy! NEXT should always be equal to the value of the INITIAL storage setting for all rollback segments. If you find that INITIAL is too small, and you don’t want to drop your rollback segments at that time, then you can still reset INITIAL to a value that is a multiple of INITIAL. This will help to eliminate many of the performance problems associated with rollback segment sizing.

MINEXTENTS

When MINEXTENTS is set, this will cause the database to allocate, and maintain, a minimum number of extents in a given rollback segment. Setting this parameter is determined on the basis of the number of expected concurrent DML transactions balanced against the number of desired rollback segments and must be set carefully. To determine MINEXTENTS, calculate out the average transaction size times the number of concurrent transactions that you are going to allow per rollback segment. For example, if you are allowing 10 concurrent transactions per rollback segment and you have found that the average transaction size appears to be 100k, then you will want to set initial to 100k, next to 100k and minextents to 1000k (1 Meg). You can use V$ROLLSTAT to determine the average transaction size (AVEACTIVE).

MAXEXTENTS

Typically the MAXEXTENTS parameter is set to the result of the calculation

MAXEXTENTS = CEILING(Largest HWMSIZE / INITIAL) + 1

which takes the largest high-water mark (largest recorded transaction for any of the online rollback segments), divides it by the size of your initial extent (be sure to convert the INITIAL size to bytes), and adds 1 to the result to allow for growth. The CEILING statement indicates to round up to the nearest integer. Be sure that the rollback segment tablespace is sized appropriately for expected transactions. Large transactions fail mainly because they run out of space in the rollback segment tablespace. They also fail when the MAXEXTENTS setpoint for the table being updated or inserted into is reached.

Formula aside, we like to give ourselves a lot of room for growth and new transactions. So we typically set MAXEXTENTS to a value much higher than the formula above will predict. Sometimes it’s just a “gut feel” exercise, since you know your database, know the workload it’s got now, and more importantly, the workload that is coming down the pike that is new. That’s the problem with statistics, and using them to guide you in sizing, they can’t predict the future, that’s the DBA’s job.

OPTIMAL

Sizing the OPTIMAL parameter can be problematic. We suggest that OPTIMAL and the number of bytes for MINEXTENTS number of extents, be set to the same value. Thus, you are configuring your rollback segments to be sized to a value of the average maximum current transaction size. Setting this value too low can result in errors (snapshot too old), and setting it to high can actually result in failed transactions. This is because if your rollback segment grows to an inordinate length, it will be taking up tablespace space. If it takes up so much tablespace space that other rollback segments can not grow, then the transactions in those tablespaces that need the rollback segment to grow, will fail.

FREELISTS

If you will be having high velocity concurrent access to your rollback segments, you might consider setting freelists (defaults to 1) to a higher value. Typically the default is sufficient.

Managing Rollback Segments

Managing normal rollback segments is done via the create rollback segment, alter rollback segment and drop rollback segment commands. Here are examples of each of these commands:

-- Creation of a rollback segment
CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (INITIAL 100K NEXT 100K MINEXTENTS 10 OPTIMAL 1000K)

-- Bring it online
ALTER ROLLBACK SEGMENT rbs01 ONLINE;

-- Take it offline
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

-- Drop it
DROP ROLLBACK SEGMENT rbs01;

Note that in order to actually use a rollback segment after it’s been created, you must bring it online. Also, in order to drop an active rollback segment, you must first take it offline. There are no corresponding commands for automated undo segments as the Oracle database will create, and online/offline rollback segments as needed.

When managing automated undo, all you need do is create an undo tablespace with the CREATE UNDO TABLESPACE command and ensure the following parameters are set correctly:

undo_management=auto
undo_retention= {set as requied}
undo_suppress_errors = {only available in 9iR1}
undo_tablesapce = {set to the name of the undo tablespace}

You can have more than one undo tablespace created in your database. However, only one undo tablespace can be active at any one time. You can change the active undo tablespace with the alter system command as follows:

alter system set undo_tablespace=new_undo_ts;

This change will cause new transactions to begin using undo segments in the new tablespace. Existing transactions will continue in the old undo tablespace. Once all active transactions have completed using the old undo tablespace, that undo tablespace can be dropped if desired.

Monitoring Rollback Segments

There a number of views to use to monitor rollback segment (RBS) use and size. They are:

View Name

Description

DBA_ROLLBACK_SEGS Describes the rollback segments
DBA_SEGMENTS Contains additional segment information
V$ROLLNAME Lists the names of all online rollback segments
V$ROLLSTAT Contains rollback segment statistics
V$TRANSACTION Contains undo segment information

The data dictionary view DBA_ROLLBACK_SEGS gives you the basic information on all rollback segments including whether they are online or offline. Its description is:

?

?

?

SEGMENT_NAME NOT NULL VARCHAR2(30) Name of RBS
OWNER VARCHAR2(6) Owner of the RBS
TABLESPACE_NAME NOT NULL VARCHAR2(30) Tablespace where RBS exists
SEGMENT_ID NOT NULL NUMBER ID number for the RBS
FILE_ID NOT NULL NUMBER File Id containing the RBS header
BLOCK_ID NOT NULL NUMBER Id of the block containing the RBS header
INITIAL_EXTENT NUMBER Initial extent size in bytes
NEXT_EXTENT NUMBER Next extent size in bytes
MIN_EXTENTS NOT NULL NUMBER Minimum number of extents
MAX_EXTENTS NOT NULL NUMBER Maximum number of extents
PCT_INCREASE NUMBER Percent increase for extent sizes
STATUS VARCHAR2(16) RBS status
INSTANCE_NUM VARCHAR2(40) Owning Instance number used with OPS
RELATIVE_FNO NOT NULL NUMBER Relative number of the file containing RBS header

Dynamic view V$ROLLSTAT gives RBS statistics. This view is reset on instance startup. 

?

?

?

USN NUMBER RBS number
EXTENTS NUMBER Number of extents of the RBS
RSSIZE NUMBER Size in bytes of the RBS
WRITES NUMBER Bytes written to the RBS
XACTS NUMBER The number of active transactions in the RBS
GETS NUMBER The number of RBS header requests
WAITS NUMBER The number of RBS header requests that had to wait
OPTSIZE NUMBER The value of the optimal parameter of the RBS
HWMSIZE NUMBER The largest size or high water mark of the RBS
SHRINKS  NUMBER The number of shrinks down to its optimal size
WRAPS NUMBER Count of times a transaction has wrapped to an additional extent of the RBS
EXTENDS NUMBER Number of times the RBS has acquired a new extent
AVESHRINK NUMBER The average shrink in bytes to optimal
AVEACTIVE NUMBER The average number of bytes in active extents in the rollback segment measured over time
STATUS VARCHAR2(15) RBS status
CUREXT NUMBER Current extent
CURBLK NUMBER Current block