| Oracle Server Administrator's Guide
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 Oracle8 Parallel Server Concepts & Administration.
This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager User's Guide.
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 Oracle8 Server Concepts.
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: 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 Oracle8 Parallel Server Concepts & Administration.
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.
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 Oracle8 Parallel Server Concepts & Administration.
For more information about public and private rollback segments, see Oracle8 Server Concepts.
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_ROLL-
BACK_SEGMENT segments are specified. The rollback segments can be either private or public.
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 per cent of the size of the database's largest table because most SQL statements affect 10 per cent 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.
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
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.
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 Enterprise Manager/GUI. In this monitor, the following statistics are given for each rollback segment:
Size, High Water
the most space ever allocated for the rollback segment, in bytes
the OPTIMAL size of the rollback segment, in bytes
the cumulative number of times a transaction continues writing from one extent in a rollback segment to another existing extent
the cumulative number of times a new extent is allocated for a rollback segment
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 18-1
provides additional information on how to interpret the statistics given in this monitor.
|Shrinks||Average Sizes, Shrunk||Analysis and Recommendation|
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.)
Excellent: a good setting for OPTIMAL.
OPTIMAL is too small: too many shrinks are being performed.
Periodic long transactions are probably causing these statistics. Set the OPTIMAL parameter higher until Shrinks is low.
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:
To create rollback segments, you must have the CREATE ROLLBACK SEGMENT system privilege. To create additional rollback segments for a database, use either the Create Rollback Segment property sheet of Enterprise Manager, or the SQL command 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;
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" on page 18-11 for more information.
This section describes aspects of specifying rollback segment storage parameters, and includes the following topics:
Suppose you wanted to create a public rollback segment DATA1_RS with storage parameters and optimal size set as follows:
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);
You can also use the Create Rollback Segment property sheet of Enterprise Manager to set the rollback segment's 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 either the Alter Rollback Segment property sheet of Enterprise Manager, or the SQL command 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.
Note: If you are altering a public rollback segment, you must include the keyword PUBLIC in the ALTER ROLLBACK SEGMENT command.
See Also: For guidance on setting sizes and storage parameters (including OPTIMAL) for rollback segments, see "Guidelines for Managing Rollback Segments" on page 18-2.
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.
To shrink a rollback segment using you must have the ALTER ROLLBACK SEGMENT system privilege.
You can manually decrease the size of a rollback segment using the SQL command 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 command, see the Oracle8 Server SQL Reference.
This section describes aspects of taking rollback segments online and offline, and includes the following topics:
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:
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.
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 either the Place Online menu item of Enterprise Manager or the SQL command ALTER ROLLBACK SEGMENT with the ONLINE option.
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.
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.
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 Oracle8 Server Reference Manual.
To see a query for checking rollback segment state, see "Displaying Rollback Segment Information" on page 18-15.
To take an online rollback segment offline, use either the Take Offline menu item of Enterprise Manager, or 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.
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" on page 18-15.
For information about the views DBA_ROLLBACK_SEGS and V$ROLLSTAT, see the Oracle8 Server Reference Manual.
A transaction can be explicitly assigned to a specific rollback segment using the SET TRANSACTION command with the USE ROLLBACK SEGMENT parameter. Transactions are explicitly assigned to rollback segments for the following reasons:
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 statement 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.
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 either the Drop menu item of Enterprise Manager, or the SQL command 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 command, 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 Oracle8 Server Reference Manual.
Use the MONITOR ROLLBACK feature of Enterprise Manager/GUI to monitor a rollback segment's size, number of extents, optimal number of extents, activity concerning dynamic deallocation of extents, and current usage by active transaction.
See Also: 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" on page 18-6.
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:
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
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.
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
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