21
Managing Rollback Segments

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

See Also: If you are using Oracle with the Parallel Server option, see Oracle8i Parallel Server Concepts and Administration.

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

See Also: For more 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 parameter TRANSACTIONS, and the number of transactions you expect each rollback segment to have to handle with the 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.

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 Concepts and Administration.

Add a Rollback Segment to the SYSTEM Tablespace

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.

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 Oracle8i Parallel Server Concepts and Administration.

For more information about public and private rollback segments, see Oracle8i Concepts.

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.

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

To obtain estimates and monitor the effectiveness of the OPTIMAL settings for rollback segments, use the MONITOR ROLLBACK statement. The following statistics are given for each rollback segment:

Size, High Water	the most space ever allocated for the rollback segment, in bytes
Size, 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 Size, Shrunk	the average size of the space Oracle truncated from the rollback segment, in bytes
Average Size, 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 21-1 provides additional information on how to interpret the statistics given in this monitor.

Table 21-1 Analyzing the Effectiveness of Current OPTIMAL Settings

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.

Set the Storage Location for Rollback

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. To create additional rollback segments for a database, use the SQL statement CREATE ROLLBACK SEGMENT. The tablespace to contain the new rollback segment must be online.

The following statement creates a public rollback segment named USERS_RS in the USERS tablespace, using the default storage parameters of the USERS tablespace:

CREATE PUBLIC ROLLBACK SEGMENT users_rs TABLESPACE users;

Bringing New Rollback Segments Online

If you create a private rollback segment, you should add the name of this new rollback segment to the ROLLBACK_SEGMENTS parameter in the 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 RS1 and RS2, the ROLLBACK_SEGMENTS parameter of the parameter file should be similar to the following:

ROLLBACK SEGMENTS= (RS1, RS2)

See Also: Once a rollback segment is created, it is not available for use by transactions of any instance until it is brought online. See "Taking Rollback Segments Online and Offline" for more information.

Specifying Storage Parameters for Rollback Segments

This section describes aspects of specifying rollback segment storage parameters, and includes the following topics:

Setting Storage Parameters When Creating a Rollback Segment

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

The rollback segment is allocated an initial extent of 50K.
The rollback segment is allocated the second extent of 50K.
The optimal size of the rollback segment is 750K.
The minimum number of extents and the number of extents initially allocated when the segment is created is 15.
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 data1_rs
                 TABLESPACE users
                 STORAGE (
      INITIAL 50K
      NEXT 50K
      OPTIMAL 750K
      MINEXTENTS 15
      MAXEXTENTS 100);

Changing Rollback Segment Storage Parameters

You can change a rollback segment's storage parameters after creating it. However, you cannot alter the size of any extent currently allocated to a rollback segment. You can only affect future extents.

Alter a rollback segment's storage parameters using the SQL statement ALTER ROLLBACK SEGMENT.

The following statement alters the maximum number of extents that the DATA1_RS rollback segment can allocate.

ALTER PUBLIC ROLLBACK SEGMENT data1_rs 
		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.

See Also: For guidance on setting sizes and storage parameters (including OPTIMAL) for rollback segments, see "Guidelines for Managing Rollback Segments".

Altering Rollback Segment Format

To alter rollback segments, you must have the ALTER ROLLBACK SEGMENT system privilege.

You can define limited or unlimited format for rollback segments. When converting to limited or unlimited format, you must take the rollback segments offline. If you identify unlimited format for rollback segments, extents for that segment must have a minimum of 4 data blocks. Thus, a limited format rollback segment cannot be converted to unlimited format if it has less than 4 data blocks in any extent. If you want to convert from limited to unlimited format and have less than 4 data blocks in an extent, your only choice is to drop and re-create the rollback segment.

Shrinking a Rollback Segment Manually

To shrink a rollback segment you must have the ALTER ROLLBACK SEGMENT system privilege.

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

The following statement shrinks rollback segment RBS1 to 100K:

ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

See Also: For a complete description of the ALTER ROLLBACK SEGMENT statement, see the Oracle8i SQL Reference.

Taking Rollback Segments Online and Offline

This section describes aspects of taking rollback segments online and 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.

See Also: For information about the ROLLBACK_SEGMENTS and DBA_ROLLBACK_SEGS parameters, see the Oracle8i Reference.

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 command 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 ONLINE; however, the rollback segment's status in the view V$ROLLSTAT is PENDING OFFLINE.

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.

Taking Public and Private Rollback Segments Offline

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.

See Also: For information on viewing rollback segment status, see "Displaying Rollback Segment Information".

For information about the views DBA_ROLLBACK_SEGS and V$ROLLSTAT, see the Oracle8i Reference.

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 currently ONLINE, PARTLY AVAILABLE, NEEDS RECOVERY, or INVALID, you cannot drop it. If the status is INVALID, the segment has already been dropped. Before you can drop it, you must take it offline.

To drop a rollback segment, you must have the DROP ROLLBACK SEGMENT system privilege.

If a rollback segment is offline, you can drop it using the SQL statement DROP ROLLBACK SEGMENT.

The following statement drops the DATA1_RS rollback segment:

DROP PUBLIC ROLLBACK SEGMENT data1_rs;

If you use the DROP ROLLBACK SEGMENT statement, indicate the correct type of rollback segment to drop, public or private, by including or omitting the PUBLIC keyword.

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.

See Also: For more information about the view DBA_ROLLBACK_SEGS, see the Oracle8i Reference.

Monitoring Rollback Segment Information

For a detailed description of how to use the MONITOR for the corresponding operation, see "Set an Optimal Number of Extents for Each Rollback Segment".

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

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
------------   ---------------    -------    ------     ---------
RS1            SYSTEM             20480        10         2
RS2            TS1                40960        20         3
SYSTEM         SYSTEM            184320        90         3

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 remains ONLINE, but 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.

Displaying Deferred Rollback Segments

The following query shows which rollback segments are private and which are public. Note that it only displays information about the rollback segments that are currently online for the current instance:

SELECT segment_name, tablespace_name, owner
       FROM sys.dba_rollback_segs;

SEGMENT_NAME   TABLESPACE_NAME    OWNER
-------------  ----------------   ------
SYSTEM         SYSTEM             SYS
PUBLIC_RS      SYSTEM             PUBLIC
USERS_RS       USERS              SYS

Displaying All Deferred Rollback Segments

The following query shows all deferred rollback segments (rollback segments that were created to hold rollback entries for tablespaces taken offline until the tablespaces are brought back online):

SELECT segment_name, segment_type, tablespace_name
     FROM sys.dba_segments
WHERE segment_type = 'DEFERRED ROLLBACK';

SEGMENT_NAME     SEGMENT_TYPE        TABLESPACE_NAME
------------     -----------------   ----------------
USERS_RS         DEFERRED ROLLBACK   USERS

21 Managing Rollback Segments