11
Managing Rollback Segments

This chapter describes how to manage rollback segments, and includes the following topics:

• Guidelines for Managing Rollback Segments

• Creating Rollback Segments

• Altering Rollback Segments

• Explicitly Assigning a Transaction to a Rollback Segment

• Dropping Rollback Segments

• Monitoring Rollback Segment Information

  See Also: If you are using Oracle with the Parallel Server option, see Oracle8i Parallel Server Administration, Deployment, and Performance for information about creating rollback segments in that environment.

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:

• Use Multiple Rollback Segments

• Choose Between Public and Private Rollback Segments

• Specify Rollback Segments to Acquire Automatically

• Approximate Rollback Segment Sizes

• Create Rollback Segments with Many Equally Sized Extents

• Set an Optimal Number of Extents for Each Rollback Segment

• Place Rollback Segments in a Separate Tablespace

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, roll back transactions, and recover the database.

See Also: For additional information about rollback segments, see Oracle8i Concepts.

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:

• When a database is created, a single rollback segment named SYSTEM is created in the SYSTEM tablespace. You can create any objects in non-SYSTEM tablespaces, but you cannot write to them until you have created and brought online at least one additional rollback segment in a non-SYSTEM tablespace (for non-SYSTEM objects).

• When many transactions are concurrently proceeding, more rollback information is generated at the same time. You can indicate the number of concurrent transactions you expect for the instance with the initialization parameter TRANSACTIONS, and the number of transactions you expect each rollback segment to have to handle with the initialization parameter TRANSACTIONS_PER_ROLLBACK_SEGMENT. Then, when an instance opens a database, it attempts to acquire at least TRANSACTIONS/ TRANSACTIONS_PER_ROLLBACK_SEGMENT rollback segments to handle the maximum amount of transactions. Therefore, after setting the parameters, create TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_ SEGMENT rollback segments.

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.

See Also: In order to start instances in an Oracle Parallel Server environment, you must give each instance access to its own rollback segment, in addition to the SYSTEM rollback segment. For additional details, see Oracle8i Parallel Server Administration, Deployment, and Performance. For information about the TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT initialization parameters, see the Oracle8i Reference.

Choose Between Public and Private Rollback Segments

A private rollback segment is one that is acquired explicitly by an instance when the instance opens the database if it is named in the ROLLBACK_SEGMENTS parameter in the initialization parameter file. A private rollback segment can also be acquired by specifically bringing it online by manually issuing a statement to do so. Public rollback segments form a pool of rollback segments that any instance requiring a rollback segment can use.

A database with the Parallel Server option can 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.

If a database does not have the Parallel Server option, public and private rollback segments are identical.

Specify Rollback Segments to Acquire Automatically

When an instance starts, it acquires by default 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_ROLLBACK_SEGMENT segments are specified. The rollback segments can be either private or public.

Approximate Rollback Segment Sizes

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 preallocated extents of a large rollback segment.

When database systems 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:

T = total initial rollback segment size, in bytes

n = number of extents initially allocate

s = calculated size, in bytes, of each extent initially allocated

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.

The V$ROLLNAME and V$ROLLSTAT dynamic performance views can be monitored to collect statistics useful in determining appropriate settings for OPTIMAL. See "Rollback Segment Statistics".

Place Rollback Segments in a Separate Tablespace

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

• A tablespace holding rollback segments can always be kept online, thus maximizing the combined storage capacity of rollback segments at all times. Note that if some rollback segments are not available, the overall database operation can be affected.

• Because tablespaces with active rollback segments cannot be taken offline, designating a tablespace to hold all rollback segments of a database ensures that the data stored in other tablespaces can be taken offline without concern for the database's rollback segments.

• A tablespace's free extents are likely to be more fragmented if the tablespace contains rollback segments that frequently allocate and deallocate extents.

Creating Rollback Segments

To create rollback segments, you must have the CREATE ROLLBACK SEGMENT system privilege. You use the CREATE ROLLBACK SEGMENT statement. The tablespace to contain the new rollback segments must be online. Rollback segments are usually created as part of the database creation script or process, but you may add more at a later time.

The following topics relating to creating rollback segments are included in this section:

• The CREATE ROLLBACK SEGMENT Statement

• Bringing New Rollback Segments Online

• Setting Storage Parameters When Creating a Rollback Segment

The CREATE ROLLBACK SEGMENT Statement

The following statement creates a rollback segment named RBS_02 in the RBSSPACE tablespace, using the default storage parameters of that tablespace. Since this is not a parallel server environment, it is not necessary to specify PRIVATE or PUBLIC. The default is PRIVATE.

CREATE ROLLBACK SEGMENT rbs_02 TABLESPACE rbsspace;

See Also: For exact syntax, restrictions, and authorization requirements for the SQL statements used in managing rollback segments, see Oracle8i SQL Reference.

Bringing New Rollback Segments Online

New rollback segments are initially offline. You must issue an ALTER ROLLBACK SEGMENT to bring them online and make it available for use by transactions of an instance. See "Changing the ONLINE/OFFLINE Status of Rollback Segments" for more information.

If you create a private rollback segment, you should add the name of this new rollback segment to the ROLLBACK_SEGMENTS initialization parameter in the initialization parameter file for the database. Doing so enables the private rollback segment to be captured by the instance at instance start up. For example, if two new private rollback segments are created and named RBS_01 and RBS_02, the ROLLBACK_SEGMENTS parameter of the parameter file should be similar to the following:

ROLLBACK_SEGMENTS = (RBS_01, RBS_02)

See Also: For information about the ROLLBACK_SEGMENTS initialization parameter, see the Oracle8i Reference.

Setting Storage Parameters When Creating a Rollback Segment

Suppose you wanted to create a rollback segment RBS_01 with storage parameters and optimal size set as follows:

• The rollback segment is allocated an initial extent of 100K.

• The rollback segment is allocated the second extent of 100K.

• The optimal size of the rollback segment is 4M.

• The minimum number of extents and the number of extents initially allocated when the segment is created is 20.

• The maximum number of extents that the rollback segment can allocate, including the initial extent, is 100.

The following statement creates a rollback segment with these characteristics:

CREATE PUBLIC ROLLBACK SEGMENT rbs_01
                 TABLESPACE rbsspace
                 STORAGE (
      INITIAL 100K
      NEXT 100K
      OPTIMAL 4M
      MINEXTENTS 20
      MAXEXTENTS 100 );

You cannot set a value for the storage parameter PCTINCREASE. It is always 0 for rollback segments. The OPTIMAL storage parameter is unique to rollback segments. For a discussion of storage parameters see "Setting Storage Parameters" and the Oracle8i SQL Reference.

Oracle makes the following recommendations:

• Set INITIAL and NEXT to the same value to ensure that all extents are the same size.

• Create a large number of initial extents to minimize the possibility of dynamic extension. MINEXTENTS = 20 is a good value.

• Avoid setting MAXEXTENTS = UNLIMITED as this could cause unnecessary extension of a rollback segment and possibly of data files due to a programming error. If you do specify UNLIMITED, be aware that extents for that segment must have a minimum of 4 data blocks. Also, if you later want to convert a rollback segment whose MAXEXTENTS are limited to UNLIMITED, that rollback segment cannot be converted if it has less than 4 data blocks in any extent. If you want to convert from limited to UNLIMITED, and have less than 4 data blocks in an extent, your only choice is to drop and re-create the rollback segment.

Altering Rollback Segments

This section discusses various actions you can take to maintain your rollback segments. All of these maintenance activities use the ALTER ROLLBACK SEGMENT statement. You must have the ALTER ROLLBACK SEGMENT system privilege to use this statement.

The following topics are presented:

• Changing Rollback Segment Storage Parameters

• Shrinking a Rollback Segment Manually

• Changing the ONLINE/OFFLINE Status of Rollback Segments

Changing Rollback Segment Storage Parameters

You can change some of a rollback segment's storage parameters after creating it. You may want to change the values of OPTIMAL or MAXEXTENTS. The following statement alters the maximum number of extents that the RBS_01 rollback segment can allocate.

ALTER ROLLBACK SEGMENT rbs_01 
    STORAGE (MAXEXTENTS 120);

You can alter the settings for the SYSTEM rollback segment, including the OPTIMAL parameter, just as you can alter those of any rollback segment.

Shrinking a Rollback Segment Manually

You can manually decrease the size of a rollback segment using the ALTER ROLLBACK SEGMENT statement. The rollback segment you are trying to shrink must be online.

The following statement shrinks rollback segment RBS1 to 100K:

ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

This statement attempts to reduce the size of the rollback segment to the specified size, but will stop short if an extent cannot be deallocated because it is active.

Changing the ONLINE/OFFLINE Status of Rollback Segments

This section describes aspects of bringing rollback segments online and taking them offline, and includes the following topics:

• Bringing Rollback Segments Online

• Taking Rollback Segments Offline

A rollback segment is either online and available to transactions, or offline and unavailable to transactions. Generally, rollback segments are online and available for use by transactions.

You may wish to take online rollback segments offline in the following situations:

• When you want to take a tablespace offline, and the tablespace contains rollback segments. You cannot take a tablespace offline if it contains rollback segments that transactions are currently using. To prevent associated rollback segments from being used, you can take them offline before taking the tablespace offline.

• You want to drop a rollback segment, but cannot because transactions are currently using it. To prevent the rollback segment from being used, you can take it offline before dropping it.

  Note: You cannot take the SYSTEM rollback segment offline.

You might later want to bring an offline rollback segment back online so that transactions can use it. When a rollback segment is created, it is initially offline, and you must explicitly bring a newly created rollback segment online before it can be used by an instance's transactions. You can bring an offline rollback segment online via any instance accessing the database that contains the rollback segment.

Bringing Rollback Segments Online

You can bring online only a rollback segment whose current status (as shown in the DBA_ROLLBACK_SEGS data dictionary view) is OFFLINE or PARTLY AVAILABLE. To bring an offline rollback segment online, use the SQL statement ALTER ROLLBACK SEGMENT with the ONLINE option.

Bringing a PARTLY AVAILABLE Rollback Segment Online

A rollback segment in the PARTLY AVAILABLE state contains data for an in-doubt or recovered distributed transaction, and yet to be recovered transactions. You can view its status in the data dictionary view DBA_ROLLBACK_SEGS as PARTLY AVAILABLE. The rollback segment usually remains in this state until the transaction is resolved either automatically by RECO, or manually by a DBA. However, you might find that all rollback segments are PARTLY AVAILABLE. In this case, you can bring a PARTLY AVAILABLE segment online, as described above.

Some resources used by the rollback segment for the in-doubt transaction remain inaccessible until the transaction is resolved. As a result, the rollback segment may have to grow if other transactions assigned to it need additional space.

As an alternative to bringing a PARTLY AVAILABLE segment online, you might find it easier to create a new rollback segment temporarily, until the in-doubt transaction is resolved.

Bringing Rollback Segment Online Automatically

If you would like a rollback segment to be automatically brought online whenever you start up the database, add the segment's name to the ROLLBACK_SEGMENTS parameter in the database's parameter file.

Bringing Rollback Segments Online: Example

The following statement brings the rollback segment USER_RS_2 online:

ALTER ROLLBACK SEGMENT user_rs_2 ONLINE;

After you bring a rollback segment online, its status in the data dictionary view DBA_ROLLBACK_SEGS is ONLINE. To see a query for checking rollback segment state, see "Displaying Rollback Segment Information".

Taking Rollback Segments Offline

To take an online rollback segment offline, use the ALTER ROLLBACK SEGMENT statement with the OFFLINE option. The rollback segment's status in the DBA_ROLLBACK_SEGS data dictionary view must be ONLINE, and the rollback segment must be acquired by the current instance.

The following example takes the rollback segment USER_RS_2 offline:

ALTER ROLLBACK SEGMENT user_rs_2 OFFLINE;

If you try to take a rollback segment that does not contain active rollback entries offline, Oracle immediately takes the segment offline and changes its status to "OFFLINE".

In contrast, if you try to take a rollback segment that contains rollback data for active transactions (local, remote, or distributed) offline, Oracle makes the rollback segment unavailable to future transactions and takes it offline after all the active transactions using the rollback segment complete. Until the transactions complete, the rollback segment cannot be brought online by any instance other than the one that was trying to take it offline. During this period, the rollback segment's status in the view DBA_ROLLBACK_SEGS remains PENDING OFFLINE; however, the rollback segment's status in the view V$ROLLSTAT is PENDING OFFLINE. For information on viewing rollback segment status, see "Displaying Rollback Segment Information".

The instance that tried to take a rollback segment offline and caused it to change to PENDING OFFLINE can bring it back online at any time; if the rollback segment is brought back online, it will function normally.

After you take a public or private rollback segment offline, it remains offline until you explicitly bring it back online or you restart the instance.

Explicitly Assigning a Transaction to a Rollback Segment

A transaction can be explicitly assigned to a specific rollback segment using the SET TRANSACTION statement with the USE ROLLBACK SEGMENT clause. Transactions are explicitly assigned to rollback segments for the following reasons:

• The anticipated amount of rollback information generated by a transaction can fit in the current extents of the assigned rollback segment.

• Additional extents do not have to be dynamically allocated (and subsequently truncated) for rollback segments, which reduces overall system performance.

To assign a transaction to a rollback segment explicitly, the rollback segment must be online for the current instance, and the SET TRANSACTION USE ROLLBACK SEGMENT statement must be the first statement of the transaction. If a specified rollback segment is not online or a SET TRANSACTION USE ROLLBACK SEGMENT clause is not the first statement in a transaction, an error is returned.

For example, if you are about to begin a transaction that contains a significant amount of work (more than most transactions), you can assign the transaction to a large rollback segment, as follows:

SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;

After the transaction is committed, Oracle will automatically assign the next transaction to any available rollback segment unless the new transaction is explicitly assigned to a specific rollback segment by the user.

Dropping Rollback Segments

You can drop rollback segments when the extents of a segment become too fragmented on disk, or the segment needs to be relocated in a different tablespace.

Before dropping a rollback segment, make sure that status of the rollback segment is OFFLINE. If the rollback segment that you want to drop is any other status, you cannot drop it. If the status is INVALID, the segment has already been dropped.

To drop a rollback segment, use the DROP ROLLBACK SEGMENT statement. You must have the DROP ROLLBACK SEGMENT system privilege. The following statement drops the RBS1 rollback segment:

DROP ROLLBACK SEGMENT rbs1;

Note: If a rollback segment specified in ROLLBACK_SEGMENTS is dropped, make sure to edit the parameter files of the database to remove the name of the dropped rollback segment from the list in the ROLLBACK_SEGMENTS parameter. If this step is not performed before the next instance startup, startup fails because it cannot acquire the dropped rollback segment.

After a rollback segment is dropped, its status changes to INVALID. The next time a rollback segment is created, it takes the row vacated by a dropped rollback segment, if one is available, and the dropped rollback segment's row no longer appears in the DBA_ROLLBACK_SEGS view.

Monitoring Rollback Segment Information

This section presents views that can be used to obtain and monitor rollback segment information, and provides information and examples relating to their use.

The following topics are presented:

• Displaying Rollback Segment Information

• Rollback Segment Statistics

• Displaying All Rollback Segments

• Displaying Whether a Rollback Segment Has Gone Offline

  See Also: For information about the dictionary and dynamic views discussed in this chapter, see the Oracle8i Reference.

Displaying Rollback Segment Information

The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. For example, the following query lists the name, associated tablespace, and status of each rollback segment in a database:

SELECT segment_name, tablespace_name, status
        FROM sys.dba_rollback_segs;

SEGMENT_NAME  TABLESPACE_NAME     STATUS
------------- ----------------    ------
SYSTEM        SYSTEM              ONLINE
PUBLIC_RS     SYSTEM              ONLINE
USERS_RS      USERS               ONLINE

In addition, the following data dictionary views contain information about the segments of a database, including rollback segments:

• USER_SEGMENTS

• DBA_SEGMENTS

Rollback Segment Statistics

The V$ROLLSTAT dynamic performance view can be queried to monitor rollback segment statistics. Refer to the Oracle8i Reference for a complete description of the columns and statistics contained in this view. It must be joined with the V$ROLLNAME view to map its segment number to its name.

Some specific columns of interest include:

Name	Description
USN	Rollback segment number. If this view is joined with the V$ROLLNAME view, the rollback segment name can be determined.
WRITES	The number of bytes of entries written to the rollback segment.
XACTS	The number of active transactions.
GETS	The number of rollback segment header requests.
WAITS	The number of rollback segment header requests that resulted in waits.
OPTSIZE	The value of the optimal parameter for the rollback segment.
HWMSIZE	The highest value (high water mark), in bytes, of the rollback segment size reached during usage.
SHRINKS	The number of shrinks that the rollback segment has had to perform in order to stay at the optimal size.
WRAPS	The number of times a rollback segment entry has wrapped from one extent to another.
EXTENDS	The number of times that the rollback segment had to acquire a new extent.
AVESHRINK	The average number of bytes freed during a shrink.
AVEACTIVE	The average number of bytes in active extents in the rollback segment, measured over time.

These statistics are reset at system startup.

Ad hoc querying of this view can help in determining the most advantageous setting for the OPTIMAL parameter. Assuming that an instance has equally sized rollback segments with comparably sized extents, OPTIMAL for a given rollback segment should be set slightly higher than AVEACTIVE. The following chart provides additional information on how to interpret the statistics given in this view.

SHRINKS	AVESHRINK	Analysis and Recommendation
Low	Low	If AVEACTIVE is close to OPTSIZE, 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 SHRINK is low.

Displaying All Rollback Segments

The following query returns the name of each rollback segment, the tablespace that contains it, and its size:

SELECT segment_name, tablespace_name, bytes, blocks, extents
   FROM sys.dba_segments
   WHERE segment_type = 'ROLLBACK';

SEGMENT_NAME   TABLESPACE_NAME    BYTES      BLOCKS     EXTENTS
------------   ---------------    -------    ------     -------
SYSTEM         SYSTEM              409600       200           8
RB_TEMP        SYSTEM             1126400       550          11
RB1            RBS                 614400       300           3
RB2            RBS                 614400       300           3
RB3            RBS                 614400       300           3
RB4            RBS                 614400       300           3
RB5            RBS                 614400       300           3
RB6            RBS                 614400       300           3
RB7            RBS                 614400       300           3
RB8            RBS                 614400       300           3
10 rows selected.

Displaying Whether a Rollback Segment Has Gone Offline

When you take a rollback segment offline, it does not actually go offline until all active transactions in it have completed. Between the time when you attempt to take it offline and when it actually is offline, its status in DBA_ROLLBACK_SEGS is PENDING OFFLINE and it is not used for new transactions. To determine whether any rollback segments for an instance are in this state, use the following query:

SELECT name, xacts "ACTIVE TRANSACTIONS"
      FROM v$rollname, v$rollstat
      WHERE status = 'PENDING OFFLINE'
        AND v$rollname.usn = v$rollstat.usn;

NAME         ACTIVE TRANSACTIONS
----------   --------------------
RS2                             3

If your instance is part of a Parallel Server configuration, this query displays information for rollback segments of the current instance only, not those of other instances.