Oracle CREATE ROLLBACK SEGMENT Insights

A Rollback Segment records the old values of data that were changed by each transaction, whether committed or not. The information in a rollback segment is used to:

• Generate read-consistent database information.
• Rollback uncommitted transactions, for users, during normal database operation.
• Rollback uncommitted transactions at the end of a database recovery session.

Each rollback segment contains a transaction:

• BI – Before Image (data as it existed before an operation in a transaction)
• TID – Transaction Id (not to be confused with SCN)

Rollback Segment Rules:

• The minimum number of extents for a rollback segment is two.
• One transaction writes sequentially to a single rollback segment. If the rollback segment is too small, the transaction will not be able to write all of the undo information and it will abort. 
• Each transaction writes to only one extent of the rollback segment at any given time.
• Many active transactions can write concurrently to a single rollback segment – even the same extent of a rollback segment; however, each data block in a rollback segment's extent can contain information for only a single transaction.

When a transaction runs out of space in the current extent, and needs to continue writing, Oracle finds an available extent of the same rollback segment in one of two ways:

1. It can reuse an extent already allocated to the rollback segment.
2. It can acquire (and allocate) a new extent for the rollback segment.

Rollback segments store system undo data allowing non-committed transactions to be rolled back, they can be likened to before image logs or journals in other database systems. Rollback segments store the before images of changed data. Large transaction failures can usually be attributed to the following rollback related problems:

• Inadequate space in the rollback segment (usually named RBS) tablespace for rollback segment expansion.
• Improper storage specifications for the rollback segment being used resulting in that segment exceeding its MAXEXTENTS value
• Improper scheduling allowing other transactions to cause "snapshot too old errors" to occur in the transaction (can also be caused by improper INITRANS setting).

In addition to the SYSTEM rollback segment created when the database is built for use strictly by the SYSTEM tablespace, there must be at least one additional rollback segment created. Usually the number of private rollback segments is determined by determining how many concurrent users will access the database and deciding how many users should be assigned to each rollback segment (by specifying the MINEXTENTS value). For example if you have 100 concurrent users and you want (on the average) 20 users per rollback segment then the MINEXTENTS would be set to 20 for each of 5 rollback segments. For private rollback segments the calculated ratio of the initialization parameters TRANSACTIONS divided by TRANSACTIONS_PER_ROLLBACK_SEGMENT and rounded up to the nearest integer should be used to determine the number of rollback segments created. If you do not specify which rollback segments to place online (with the ROLLBACK_SEGMENTS parameter), Oracle will use this formula for determining how many rollback segments to place online at instance startup.

Sizing Recommendations

The sizing of rollback segments depends on many factors, such as the number of concurrent users, the size of the average transaction, the size of the largest expected transaction, and the available space. The STORAGE clause parameters can be derived using information from the V$ROLLSTAT view (which contains dynamic data about actual rollback segment statistics).

INITIAL
 

The INITIAL parameter sets the size of the initial extents for the rollback segment. The value of the INITIAL extent should be set to the size of the average transaction. If you need to change the INITIAL extent size, you must dropping and recreate the rollback segment.

For INITIAL, the critical value in the V$ROLLSTAT table is AVEACTIVE, averaged over the online rollback segments. Set INITIAL equal to the average AVEACTIVE value.
 

NEXT
 

NEXT should always be equal to the INITIAL value for rollback segments. Since INITIAL should equal NEXT, it is advisable to place rollback segments in a Locally Managed Tablespace with uniform extent sizes. This is the only way you can absolutely guarantee that INITIAL will always equal NEXT. If a DBA modifies NEXT so that it does not equal INITIAL, that modification will be ignored.
 

MINEXTENTS
 

The MINEXTENT value is determined on the basis of the number of expected concurrent DML transactions balanced against the number of desired rollback segments. For example, if you have a potential for 200 concurrent DML transactions and want to have 10 online rollback segments, set MINEXTENTS to 20. Each DML transaction will use one extent in a rollback segment, and multiple transactions can use a single rollback segment simultaneously.
 

MAXEXTENTS
 

The MAXEXTENTS value is set to the result of the calculation

MAXEXTENTS = CEILING(Largest HWMSIZE / INITIAL) + 1

which takes the largest high-water mark (largest recorded transaction for any of the online rollback segments), divides it by the size of your initial extent (be sure to convert the INITIAL size to bytes), and adds 1 to the result to allow for growth. The CEILING statement indicates to round up to the nearest integer. Be sure that the rollback segment tablespace is sized appropriately for expected transactions. Large transactions fail mainly because they run out of space in the rollback segment tablespace. They also fail when the MAXEXTENTS limit for the table being updated or inserted into is reached.
 

OPTIMAL
 

Sizing the OPTIMAL value can be problematic. If OPTIMAL is set, and a rollback segment extend past this optimal value, then when transactions free extents, the rollback segment is shrunk back to the OPTIMAL value. Ideally, you want to adjust OPTIMAL until wraps and shrinks are minimized. You should reach a setting at which wraps stays at or near zero and you get only a few shrinks that coincide to large transactions. Usage of OPTIMAL does introduce overhead to database operations. For this reason, it can be advisable to not set an optimal rollback segment size.

General Recommendations

• PUBLIC rollback segments are used for multiple instances to share the same rollback segments in an Oracle Parallel Server or Real Application Clusters environment. If you are not using OPS or RAC, then use private rollback segments. 
• Rollback segments can be placed in any tablespace, but it is suggested that they be placed in a tablespace that only contains other rollback segments. This makes administration easier. It is also important because rollback segments are high-activity areas and should be separated from data and index files.
• The extent size should be the average transaction size expected for the instance. The number of users per rollback segments should be set such that there aren't excessive extents per rollback segment but each expected concurrent user requires at least one extent. The sum of the number of extents in all non-system rollback segments should be equal to or slightly more than the average number of concurrent transactions.