Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Guidelines for Managing Rollback Segments

This section describes guidelines to consider before creating or managing the rollback segments of your databases, and includes the following topics:

Every database contains one or more rollback segments, which are portions of the database that record the actions of transactions in the event that a transaction is rolled back. You use rollback segments to provide read consistency, rollback transactions, and recover the database.

See Also: For more information about rollback segments, see the Oracle7 Server Concepts manual.

Use Multiple Rollback Segments

Using multiple rollback segments distributes rollback segment contention across many segments and improves system performance. Multiple rollback segments are required in the following situations:

See Also: With the Oracle Parallel Server, in order to start, each instance requires access to its own rollback segment, in addition to the SYSTEM rollback segment. For additional details, see the Oracle7 Parallel Server Concepts & Administration guide.

Add a Rollback Segment to the SYSTEM Tablespace

An initial rollback segment called SYSTEM is created when a database is created. The SYSTEM rollback segment is created in the SYSTEM tablespace using the default storage parameters associated with that tablespace. You cannot drop this rollback segment.

An instance always acquires the SYSTEM rollback segment in addition to any other rollback segments it needs. However, if there are multiple rollback segments, Oracle tries to use the SYSTEM rollback segment only for special system transactions and distributes user transactions among other rollback segments; if there are too many transactions for the non-SYSTEM rollback segments, Oracle uses the SYSTEM segment. Therefore, after database creation, create at least one additional rollback segment in the SYSTEM tablespace.

Choose Between Public and Private Rollback Segments

A private rollback segment is acquired explicitly by an instance when the instance opens the database. Public rollback segments form a pool of rollback segments that any instance requiring a rollback segment can use.

If a database does not have the Parallel Server option, public and private rollback segments are identical. Therefore, you can create all public rollback segments. A database with the Parallel Server option can also have only public segments, as long as the number of segments is high enough that each instance opening the database can acquire at least one rollback segment in addition to its SYSTEM rollback segment. You may also use private rollback segments when using the Oracle Parallel Server.

See Also: For more information about the Parallel Server option and rollback segments, see the Oracle7 Parallel Server Concepts & Administration guide.

For more information about public and private rollback segments, see the Oracle7 Server Concepts guide.

Specify Rollback Segments to Acquire Automatically

By default, when an instance starts, it acquires TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT rollback segments. If you want to ensure that the instance acquires particular rollback segments that have particular sizes or particular tablespaces, specify the rollback segments by name in the ROLLBACK_SEGMENTS parameter in the instance's parameter file.

The instance acquires all the rollback segments listed in this parameter, even if more than TRANSACTIONS/TRANSACTIONS_PER_ROLL- BACK_SEGMENT segments are specified. The rollback segments can be either private or public.

Set Rollback Segment Sizes Appropriately

Total rollback segment size should be set based on the size of the most common transactions issued against a database. In general, short transactions experience better performance when the database has many smaller rollback segments, while long running transactions, like batch jobs, perform better with larger rollback segments. Generally, rollback segments can handle transactions of any size easily; however, in extreme cases when a transaction is either very short or very long, a user might want to use an appropriately sized rollback segment.

If a system is running only short transactions, rollback segments should be small so that they are always cached in main memory. If the rollback segments are small enough, they are more likely to be cached in the SGA according to the LRU algorithm, and database performance is improved because less disk I/O is necessary. The main disadvantage of small rollback segments is the increased likelihood of the error "snapshot too old" when running a long query involving records that are frequently updated by other transactions. This error occurs because the rollback entries needed for read consistency are overwritten as other update entries wrap around the rollback segment. Consider this issue when designing an application's transactions, and make them short atomic units of work so that you can avoid this problem.

In contrast, long running transactions work better with larger rollback segments, because the rollback entries for a long running transaction can fit in pre-allocated extents of a large rollback segment.

When a database system's applications concurrently issue a mix of very short and very long transactions, performance can be optimized if transactions are explicitly assigned to a rollback segment based on the transaction/rollback segment size. You can minimize dynamic extent allocation and truncation for rollback segments. This is not required for most systems and is intended for extremely large or small transactions.

To optimize performance when issuing a mix of extremely small and large transactions, make a number of rollback segments of appropriate size for each type of transaction (such as small, medium, and large). Most rollback segments should correspond to the typical transactions, with a fewer number of rollback segments for the atypical transactions. Then set OPTIMAL for each such rollback segment so that the rollback segment returns to its intended size if it has to grow.

You should tell users about the different sets of rollback segments that correspond to the different types of transactions. Often, it is not beneficial to assign a transaction explicitly to a specific rollback segment; however, you can assign an atypical transaction to an appropriate rollback segment created for such transactions. For example, you can assign a transaction that contains a large batch job to a large rollback segment.

When a mix of transactions is not prevalent, each rollback segment should be 10% of the size of the database's largest table because most SQL statements affect 10% or less of a table; therefore, a rollback segment of this size should be sufficient to store the actions performed by most SQL statements.

Generally speaking, you should set a high MAXEXTENTS for rollback segments; this allows a rollback segment to allocate subsequent extents as it needs them.

Create Rollback Segments with Many Equally-Sized Extents

Each rollback segment's total allocated space should be divided among many equally-sized extents. In general, optimal rollback I/O performance is observed if each rollback segment for an instance has 10 to 20 equally sized extents.

After determining the desired total initial size of a rollback segment and the number of initial extents for the segment, use the following formula to calculate the size of each extent of the rollback segment:

T / n = s

where:

After s is calculated, create the rollback segment and specify the storage parameters INITIAL and NEXT as s, and MINEXTENTS to n. PCTINCREASE cannot be specified for rollback segments and therefore defaults to 0. Also, if the size s of an extent is not an exact multiple of the data block size, it is rounded up to the next multiple.

Set an Optimal Number of Extents for Each Rollback Segment

You should carefully assess the kind of transactions the system runs when setting the OPTIMAL parameter for each rollback segment. For a system that executes long-running transactions frequently, OPTIMAL should be large so that Oracle does not have to shrink and allocate extents frequently. Also, for a system that executes long queries on active data, OPTIMAL should be large to avoid "snapshot too old" errors. OPTIMAL should be smaller for a system that mainly executes short transactions and queries so that the rollback segments remain small enough to be cached in memory, thus improving system performance.

To obtain estimates and monitor the effectiveness of the OPTIMAL settings for rollback segments, use the MONITOR ROLLBACK feature of Server Manager/GUI. In this monitor, the following statistics are given for each rollback segment:

Sizes, High Water the most space ever allocated for the rollback segment, in bytes
Sizes, Optimal the OPTIMAL size of the rollback segment, in bytes
Occurrences, Wraps the cumulative number of times a transaction continues writing from one extent in a rollback segment to another existing extent
Occurrences, Extends the cumulative number of times a new extent is allocated for a rollback segment
Shrinks the cumulative number of times Oracle has truncated extents from the rollback segment
Average Sizes, Shrunk the average size of the space Oracle truncated from the rollback segment, in bytes
Average Sizes, Active the average number of bytes in active extents in the rollback segment, measured over time
Assuming that an instance has equally sized rollback segments with comparably sized extents, the OPTIMAL parameter for a given rollback segment should be set slightly higher than Average Sizes, Active.

Table 17 - 1 provides additional information on how to interpret the statistics given in this monitor.

Shrinks Average Sizes, Shrunk Analysis and Recommendation
Low Low If Average Sizes, active is close to Sizes, Optimal, then the OPTIMAL setting is correct. Otherwise, OPTIMAL is too large (not many shrinks are being performed.)
Low High Excellent: a good setting for OPTIMAL.
High Low OPTIMAL is too small: too many shrinks are being performed.
High High Periodic long transactions are probably causing these statistics. Set the OPTIMAL parameter higher until Shrinks is low.
Table 17 - 1. Analyzing the Effectiveness of Current OPTIMAL Settings

Set the Storage Location for Rollback Segments

If possible, create one tablespace specifically to hold all rollback segments, in addition to the two required in the SYSTEM tablespace. This way, all rollback segment data is stored separately from other types of data. Creating this "rollback segment" tablespace can provide the following benefits:

Contents Index Home Previous Next