|Oracle7 Server Administrator's Guide||
If you are using Oracle with the Parallel Server option, see the Oracle7 Parallel Server Concepts & Administration guide.
This chapter contains several references to Oracle Server Manager. For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.
See Also: For more information about rollback segments, see the Oracle7 Server Concepts manual.
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.
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.
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.
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.
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
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
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
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.|
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;
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.
CREATE PUBLIC ROLLBACK SEGMENT data1_rs
You can also use the Create Rollback Segment property sheet of Server Manager to set the rollback segment's storage parameters.
Alter a rollback segment's storage parameters using either the Alter Rollback Segment property sheet of Server 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" .
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.
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 Oracle7 Server SQL Reference.
To take a rollback segment online or offline, you must have the ALTER ROLLBACK SEGMENT system privilege.
You may wish to take online rollback segments offline in the following situations:
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.
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.
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 Oracle7 Server Reference.
To see a query for checking rollback segment state, see "Displaying Rollback Segment Information" .
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.
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 Oracle7 Server Reference.
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.
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 Server 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 Oracle7 Server Reference.
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" .
SELECT segment_name, tablespace_name, status
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:
SELECT segment_name, tablespace_name, bytes, blocks, extents
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
SELECT name, xacts 'ACTIVE TRANSACTIONS'
FROM v$rollname, v$rollstat
WHERE status = 'PENDING OFFLINE'
AND v$rollname.usn = v$rollstat.usn;
NAME ACTIVE TRANSACTIONS
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.
SELECT segment_name, tablespace_name, owner
SEGMENT_NAME TABLESPACE_NAME OWNER
------------- ---------------- ------
SYSTEM SYSTEM SYS
PUBLIC_RS SYSTEM PUBLIC
USERS_RS USERS SYS
SELECT segment_name, segment_type, tablespace_name
WHERE segment_type = 'DEFERRED ROLLBACK';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------ ------------ ---------------
USERS_RS DEFERRED ROLLBACK USERS
Copyright © 1996 Oracle Corporation.
All Rights Reserved.