SEGMENTS II - ROLLBACK SEGMENTS

 This discussion is on managing the UNDO segments.

Undo is defined by oracle as:

Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo.

Undo records are used to:

01. To maintain the read consistency
02. To recover the database
03. Roll back transactions when a ROLLBACK statement is issued

UNDO management is done by Oracle in two ways:

01. By creating a Rollback Segment Tablespace
02. BY creating an UNDO tablespace

Historically, Oracle has used rollback segments to store undo. Space management for these rollback segments has proven to be quite complex. Oracle now offers another method of storing undo that eliminates the complexities of managing rollback segment space, and enables DBAs to exert control over how long undo is retained before being overwritten. This method uses an undo tablespace.

You cannot have these two methods in a single database. That means you cannot implement both these methods at a time. Option is to be made.

Historical method of Managing UNDO- Using Rollback Segments

Metalink Doc Id 62005.1

Introduction

A rollback segment consists of contiguous multi-block pieces called extents. The segment uses these extents in an ordered circular fashion, moving from one to the next after the current extent is full. A transaction writes a record to the current location in the rollback segment and advances the current pointer by the size of the record. (The current writing location for records is the "head" of the rollback segment). The term "tail" is used to refer to the location on the rollback segment that is the beginning of the oldest active transaction record. To determine a general rollback segment configuration, balance the number of segments against the size of each segment, such that the total size is small enough to fit into the available disk space. There should be enough segments to avoid contention as processes access them. It's also important to make sure that individual rollback segments are large enough to handle their transaction load. Oracle allocates rollback segments in a round-robin fashion among all online rollback segments (with the exception of SYSTEM) to try to spread transactions evenly. The next section discusses balancing requirements of size and concurrency.

Size and Number of Rollback Segments

1. A transaction can only use one rollback segment to store all of its rollback (undo) records.
2. Multiple transactions can write to the same extent.
3. The head of the rollback segment never moves into a rollback extent currently occupied by the tail.
4. Extents in the ring are never skipped over and used out of order as the head tries to advance.
5. If the head can't use the next extent, it allocates another extent and inserts it into the ring. From these principles it is evident that transaction time as well as transaction size is important. For instance, a transaction that only modifies one byte but waits a long period of time before ending could cause a rollback segment to extend if the extent it occupied is needed again.

Size Considerations

There are two issues that need to be considered when deciding if your segment is large enough. First, you want to make sure that transactions will not cause the head to wrap around too fast and catch the tail. This causes the segment to extend in size, as discussed above. Second, if you have long-running queries that access data that changes frequently, you want to make sure that the rollback segment doesn't wrap around and prevent the construction of a read-consistent view. Read-consistency is discussed in more detail later in the section on avoiding `ORA-1555 - snapshot too old' errors. The size needed for a rollback segment depends directly on the transaction activity of the database. The primary concern should be the activity during normal processing of the database, not rare or semi-frequent large transactions. These special cases should be dealt with separately. The number of rollback segments needed to prevent contention between processes can be determined with the use of the v$waitstat table. Waits are a definite indication of contention. The following v$waitstat query will display number of waits since instance startup:

SELECT CLASS, COUNT FROM V$WAITSTAT WHERE CLASS = '%undo%';

Any non-zero value in the count column indicates rollback segment header contention. To find out the size and number of rollback segments needed to handle normal processing on the database you need to do some testing. A good test is to start with small rollback segments and allow your application to force them to extend.

Here are the steps to run such a test:

1. Create a rollback segment tablespace.
2. Select a number of rollback segments to test and create them in the tablespace
3. Create the rollback segments so that all extents are the same size. Choose an extent size that you suspect will need between 10 to 30 extents when the segments grow to full size
4. Each rollback segments should start with two extents before the test is run. This is the minimum number of extents any rollback segment can have
5. Only the rollback segments that you are testing and the SYSTEM rollback segment should be online. Run transactions and load typical of the application
6. Watch for rollback segment contention
7. Watch for the maximum size a rollback extends to.

The maximum size any one of the rollback segments reaches during the test is the size you want to use when configuring. This size we will call the "minimum coverage size". If you see contention, adjust the number of segments and rerun the test. Also, if the largest size requires fewer than 10 extents, or more than 30, it is a good idea to lower or raise the extent size, respectively, and rerun the test. Otherwise, space may be getting wasted during the test and this would throw the number off. At the end of this test, you will have some good base estimates for the size and number of rollback segments needed for normal processing. However, handling large transactions has not been discussed yet. Calculating the segment size needed for these types of transactions as well as how to meet the requests for the calculated space will be addressed.

For sizing rollback segment extents, it is strongly recommended that each extent be of the same size. In fact, for all strategies listed below it is assumed that all rollback segments have extents of the same size and that the size of the rollback tablespace is some multiple of the common extent size. The minimum number of extents for an individual segment should be around twenty for best performance.

Why size a rollback segment with a 'minimum' of twenty extents? Rollback segments dynamically allocate space when required and de-allocate space when no longer needed (if the OPTIMAL parameter is used). The fewer extents that a rollback segment consists of, the larger the less granular these space allocations and de-allocations are. For example, consider a 200-megabyte rollback segment, which consists of only two 100-megabyte extents. If this segment were to require additional space, it would allocate another 100M extent. This immediately increases the size of the rollback segment by 50% and potentially acquires more space than is really needed. By contrast, if the rollback segment consisted of twenty 10-megabyte extents, any additional space required would be allocated in 10-megabyte pieces. When a rollback segment consists of twenty or more extents, any single change in the number of extents will not move the total size of the rollback segment by more than 5%, resulting in a much smoother allocation and de-allocation of space. Given this, increasing the number of extents beyond the suggested twenty will make space allocation and de-allocation even *more* smooth. However, in-house testing has showed rapidly diminishing returns when increasing the number of extents past twenty. In addition, allocating and de-allocating extents is not a cost-free operation. The database will have performance degradation when performing extent operations. The cost for individual extents is minor, but a rollback segment that is constantly allocating and de-allocating tiny extents can cause even a minor cost to add up. When is the SYSTEM rollback segment used? When a database is first created using the CREATE DATABASE command, only a single rollback segment is created. This is the system rollback segment and it is created in the system tablespace. The system rollback segment has one basic difference from any other rollback segment, including any other rollback segments that are created in the system tablespace. This difference is that the system rollback segment can only be used for transactions that occur on objects inside the system tablespace. This is done because the main purpose of the system rollback segment is to handle rollback for DDL transactions – that is transactions against the data dictionary tables themselves. Making the system rollback usable only for the system tablespace was simply an easy way to enforce that. It is possible for the system rollback segment to be used for non-data dictionary tables, but only if those tables are created inside the system tablespace (which is very bad development practice). Any other rollback segments inside the system tablespace do not have this restriction. Prior to Oracle 7.1.5, during database creation, at least one rollback segment must be created in system. If this is not done, additional tablespaces can never be created (because the CREATE TABLESPACE transaction by definition affects objects outside the system tablespace.) Attempting to perform such an operation will generate an `ORA-1552, cannot use the system rollback segment for non-system tablespace x' ([BUG:232566]). You will get this error any time rollback is generated for a tablespace outside of system and the only rollback segment online is system. If any other rollback segment is online, this error will not occur. Normally one of the first operations of creating a new database is to create a rollback tablespace and place all non-system rollback segments there. The additional system rollback segment is either off-lined or dropped. Leaving it online can lead to fragmentation in the system tablespace.

Read Consistency and ORA-1555 errors

A common error to receive when issuing long-running transactions is `ORA-1555, `Snapshot too old'. In the Oracle Server messages manual for this error, the cause given is `Rollback Segment too small.' That is misleading, as it is possible to get this error message with any size rollback segment and increasing the size of rollback segments may not necessarily help. When a transaction is started, Oracle keeps track of the time (actually the SCN) that it was first issued. While gathering row information to fulfill the statement, Oracle checks each row to make sure that none of the rows was modified after the begin date of the current transaction. If a row is located which was modified, Oracle goes out to the rollback segment for the value of that row which existed when the current transaction started. For uncommitted changes, the information will always exist in the rollback segment, and there are no snapshot issues. However, if there is a change that was committed after the current transaction started, then the rollback space where that transaction information is stored may get overwritten by subsequent transactions (or eliminated entirely by an OPTIMAL shrink). If Oracle tries to get information for that row and the rollback transaction no longer exists, a read-consistent result set cannot be returned and an ORA-1555 error is generated. No matter what size rollback segment(s) exists on the database, it is possible for committed transactions to be overwritten. The larger (and more) rollback segments that exist in the system, the less often transactions will be overwritten. This is the basis for the Server Messages error explanation of `rollback segment too small'. Note: A common (and incorrect) assumption is that the ORA-1555 message indicates that the rollback segment being used by the current transaction is too small. Most commonly it is SELECT statements, which generate ORA-1555 errors.

SELECT statements do not generate rollback information.

Rollback information is generated for a `CREATE TABLE AS SELECT' statement, but it is the CREATE, rather than the SELECT which does so. The best way to handle ORA-1555 errors is simply to start the long-running transaction when there are few (or no) other transactions running against the database. So long as there are updates occurring to the table(s) being accessed, snapshot errors are possible. If possible, it also helps to split the transaction into smaller pieces that take less time to run. However, if neither of these is possible, there are a couple of items to keep in mind when trying to resolve ORA-1555 errors by modifying rollback configurations: Make sure all rollback segments are online. The more segments are online, the more transactions are spread out and the less often any individual transaction will be overwritten. Exceptions to this include cases where there is a massive rollback segment that is reserved for other uses and tiny rollback segments that `wrap' head to tail often. Having such tiny segments online can actually make a 1555 worse. Make all rollback segments that are online (except SYSTEM) approximately the same size. Transactions are assigned rollback segments in a round-robin fashion (not exactly, but close enough). Since a transaction, which can cause an ORA-1555, can appear in any segment (other than SYSTEM), the likelihood of receiving and ORA-1555 will almost always be dictated by how fast the smallest rollback segment wraps (and rewrites old transactions)

Setting OPTIMAL on Rollback Segments

In the rollback segment storage clause, there is a parameter called OPTIMAL. This specifies the `optimal' size of a rollback segment in bytes. When set, Oracle will try to keep the segment at the specified size, rounded up to the extent boundary. The RDBMS tries to have the fewest number of extents such that the total size is greater than or equal to the size specified as OPTIMAL. If additional space is needed beyond the optimal size, the rollback segment will expand beyond optimal to accommodate the current transaction(s), but will eventually de-allocate extents to shrink back to this size. The process of shrinking back to OPTIMAL is not instantaneous. When a transaction that has expanded the segment beyond the set value has ended, the rollback segment does not `snap' back to the optimal size. This could have severe performance implications and cause considerable problems with read-consistency. The process of de-allocating extents is performed during transactions after the one, which caused the segment to extend. When the head moves from one extent to the next during a transaction, the segment size is checked. If the segment size is currently above the optimal, the RDBMS determines if an extent should be de-allocated. An extent will only be deallocated if there are no active transactions in the next two extents (Why the next two extents? If only the next extent is checked for availability, Oracle might de-allocate it then try to continue with the current transaction only to find that the following extent is used. At that point, Oracle must reallocate an extent.). If necessary, the RDBMS will de-allocate multiple extents in a single transaction (one each time the head crosses into a new extent) until the segment has shrunk back to its optimal size. Since extents are de-allocated by the current transaction(s), the circular nature of rollback segments guarantees that these are the oldest inactive extents (and the least likely to be used for read consistency). The OPTIMAL clause is a very handy tool, however, be aware of a couple of points when using it. Point one is that extent allocation and de-allocation is expensive in regards to performance. This means that an OPTIMAL setting may decrease performance if it is too low. (Actually, it will always decrease performance. It may noticeably decrease performance if set too low.) The best solution for performance is to set all of your rollback segments to a size where every single transaction will always fit. In practical terms, this may well be impossible - if your largest transaction is 2 gigabytes and you require ten rollback segments for concurrency. The segments should have an optimal size large enough that 90% or better of transactions will fit without having to extend the segment. In addition, the rollback tablespace should be large enough that when all rollback segments are at the optimal value, there is plenty of space for them to extend when it becomes necessary. For example, if your segments are set with an optimal value of 500 megs and you know that there is a particular transaction that runs infrequently, but requires one gigabyte when it does run, your rollback tablespace must have 500 megs free (absolute minimum) when all segments in that tablespace are at optimal. Since you cannot normally count on all segments being at optimal or on that big transaction being the only one using space in the rollback segment, prudence suggests having a bit more space available than the absolute minimum. Point two is that you are never guaranteed when a rollback segment will shrink down to its optimal size. The rollback segment only shrinks when a transaction attempts to move into another extent and sees that the extent meets the requirements for de-allocation. If a rollback is a candidate for shrinks, but no transactions are allocated to it, it will not shrink. The obvious corollary is that a segment must be online to shrink.

Bringing Rollback Segments Online and Offline

By default whenever a rollback segment is created it is offline and must be acquired by the instance or brought online. The SQL command ALTER ROLLBACK SEGMENT can be used to bring a rollback segment online or offline while the instance is running. If a segment is taken offline and the specified rollback segment does not have any active transactions, it is immediately taken offline. But if the segment contains active transactions then it is taken offline only after all the active transaction are either committed or rolled back. No new transactions will be written to a rollback segment that is waiting for other transactions to complete so that it can be brought offline. To become available again without shutting down the instance, a rollback segment that has been taken offline must be explicitly brought back online. Rollback segments are also brought online during instance startup. There are two basic ways that rollback segments can be brought online. The init.ora file can contain a parameter called rollback_segments. You can add this line to the init.ora and specify all rollback segments that are to be explicitly brought online during instance startup. Note that if any of these rollback segments do not exist, an ORA-1534 error is returned and the instance does not complete startup. The segments must already exist before you can add them to the list to be brought online. Even if rollback segments are not explicitly brought online by being added to the "rollback_segments" parameter, they may be brought online during startup. Oracle will do this based on the values of two other parameters: transactions and "transactions_per_rollback_segment". On startup, Oracle will divide transactions by "transactions_per_rollback_segment" and round up. If the resulting number is less than the number of rollback segments brought online by the "rollback_segments" parameter, nothing is done. However, if the resulting number is greater and more rollback segments exist which are not currently online, the segments will be brought online until the number is reached or until there are no other offline segments. If neither parameter is set in the init.ora, they will use default values.

Public vs. Private Rollback Segments

A common misconception about `Private' rollback segments is that they are segments reserved for a particular use or a particular transaction. The only difference between Public and Private rollback segments are in relation to the Parallel Server Option. A public rollback segment can be acquired implicitly by any instance in a parallel server environment. A private rollback segment must be explicitly acquired by a particular instance using the rollback segments parameter. If not using OPS, the difference between the two is insignificant.

Configuring Rollback Segments

After calculating the size and the number of rollback segments required, you should plan the configuration of the rollback tablespace. This section discusses three different transaction environments:

1. A steady average transaction rate
2. Frequent large transactions
3. Infrequent large transactions

The recommended configurations for the three different types of scenarios follow.

Steady Average Transaction Rate

For databases where the transaction rate has no fluctuation, there is a straightforward way to configure the tablespace. Create a tablespace that will fit your calculated number of rollback segments with the minimum coverage size you have determined. Make all extents the same size. For a safety net, you may allocate some additional space in the tablespace to allow segments to grow if they need to. If you elect to do this, use the OPTIMAL feature to force all rollback segments to free up any additional space they allocate beyond their determined size requirement. Do not make OPTIMAL smaller than the minimum coverage size. If this is done, performance will suffer due to excessive segment resizing.

Frequent Large Transactions

Databases with frequent large transactions are the hardest case to deal with. We will define frequent as the time between large transactions being less than the time needed to allow all rollback segments to shrink back to optimal size. A large transaction is one in which we don't have enough space to create all rollback segments of the size necessary to handle its rollback information. Since we can't depend on the segment shrinking in time to allow repeated large transactions, OPTIMAL is not really an option for this environment. There are basically two options that you can choose from for your rollback segment tablespace. One is to reduce the number of segments so that all are large enough to hold the largest transactions. This option may well introduce contention and cause some degradation in performance. It is a reasonable choice if performance is not extremely critical. The second option is to build one or more large rollback segments and make sure that large transactions use these segments. The SET TRANSACTION USE ROLLBACK SEGMENT command is necessary to control the placement of these large transactions. This option is difficult to implement if large transactions are being run with adhoc queries and there is no systematic control of large transactions. It is recommended in an environment where the large transactions are issued from a controlled environment (i.e. an application) that can set the transaction to the appropriate rollback segment.

Infrequent Large Transactions

For cases where large transactions are rare, you can use OPTIMAL feature to set up a flexible rollback segment scheme, one in which you are not concerned about which rollback segment the large transaction falls upon. The key is to leave enough free space in the rollback tablespace that the largest transaction’s rollback information can fit entirely into it. To do this, create the rollback tablespace with the space needed for your calculated number of segments and their minimum coverage size plus this additional space. Then set the OPTIMAL for each segment equal to the minimum coverage size. What you will see is that the large transactions will randomly make one of the segments grow and eat up the free space, but the segment will release the space before the next large transaction comes along. Note that you are sacrificing some performance for this flexibility.

SET TRANSACTION USE ROLLBACK SEGMENT will not affect DDL’s.

Example:

create table as select, alter table..

Create table as select is a data definition (DDL) statement, and as such it does an implicit commit. A transaction ends once a commit is processed, so any statements following a DDL command will not be part of the previous transaction, and therefore commands following the DDL statement may use a different rollback segment.

Command Examples

The following statement creates a rollback segment with default storage values in an appropriately configured tablespace:

CREATE TABLESPACE rbs_ts
DATAFILE 'rbs01.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K;

/* This example and the next will fail if your database is in Automatic Undo Mode. */

CREATE ROLLBACK SEGMENT rbs_one
TABLESPACE rbs_ts;

The preceding statement is equivalent to the following:

CREATE ROLLBACK SEGMENT rbs_one
TABLESPACE rbs_ts
STORAGE
(INITIAL 10K
NEXT 10K
MAXEXTENTS UNLIMITED);

Bringing a Rollback Segment Online:

This statement brings the rollback segment rbs_one online:

ALTER ROLLBACK SEGMENT rbs_one ONLINE;

Changing Rollback Segment Storage:

This statement changes the STORAGE parameters for rbs_one:

ALTER ROLLBACK SEGMENT rbs_one
STORAGE (NEXT 1000 MAXEXTENTS 20);

Resizing a Rollback Segment:

This statement attempts to resize a rollback segment to 100 megabytes:

ALTER ROLLBACK SEGMENT rbs_one
SHRINK TO 100 M;