The database assigns transactions to rollback segments in a round-robin fashion. This should help to evenly distribute transactions across all the rollback segments. Therefore it is not necessarily an advantage to have rollback segments of differing sizes because only a single transaction is assigned to each rollback segment. There is no distinction made between size of rollback segment and size of transaction. It is possible to assign specific transactions to specific rollback segments such as assigning large transactions to large rollback segments. Since rollback segments are utilised in a round-robin fashion without taking into account sizes of rollback segments and transactions large rollback segments are best not created as publicly available (PUBLIC) and assigned by specific users to specific transactions. These large rollback segments must not be made generally available to Oracle.
Never utilise the SYSTEM rollback segment for general transactions. The SYSTEM rollback segment is reserved for the use of database-level, meta-data transactions, ie. changes to data dictionary entries such as database objects. Note that a second system rollback segment, called SYSROL, is created on database creation. This second system-level rollback segment should either be deleted of atleast taken offline after database creation is complete.
Note that rollback segments must not be created too small such that they are forced to dynamically extend in order to complete transactions. Creating multiple rollback segments can help to place each transaction into it's own distinct rollback segment. Once again, remember to only utilise specialised rollback segments for specific transactions. Keep the generally available publicly declared rollback segments to the same size and incremental parameters. Specialised rollback segments should not be declared publicly and thus be available for general transactional use.
When rollback segments are extended to their maximum sizes the Oracle will attempt to reuse the first extent. If the first extent cannot be used then the rollback segment is forced to obtain a new extent.
On instance startup Oracle will always attempt to aquire the number of rollback segments equal to the formula shown below. The TRANSACTIONS sets the maximum number of concurrent transactions allowed at any point in time and the TRANSACTIONS_PER_ROLLBACK_SEGMENT attempts to allocate that number of transactions to a rollback segment simultaneously. Setting the ROLLBACK_SEGMENTS parameter to a list of rollback segments will ensure that the specified list of rollback segments are placed ONLINE on instance startup.
Rollback segments provide what is referred to as read consistency. Read consistency allows Oracle to provide an earlier version of the data for a query initiated before a change.
There is a SYSTEM rollback segment created automatically in the SYSTEM tablespace when a database is created.
Add new rollback segments to accomodate the expected number of users. It is advantageous to make rollback segments of different sizes only if each user aquires the appropriately sized rollback segment before starting each transaction. In general, make all rollback segments the same size, except for one special rollback segment which is very large, very small or one of each, for transactions that you can assign specifically to this special rollback segment.
Atleast two rollback segments are required when a database has multiple tablespaces. A database is created with a SYSTEM rollback segment in the SYSTEM tablespace. The SYSTEM rollback segment is dedicated to transactions accessing data in the SYSTEM tablespace. Creation of other tablespaces to keep user data out of the SYSTEM tablespace is a recommendation. Therefore additional rollback segments are also a recommendation to allow for servicing of transactions accessing non-SYSTEM tablespace data.
Each rollback segment can only accomodate a limited number of concurrent transactions (about 30 transactions per rollback segment in a database using 2K blocks). This limit determines how many rollback segments are required.
Using multiple rollback segments helps to distribute rollback segment contention across multiple segments. Rollback segments are used in a round-robin fashion. Thus in the case of public rollback segments it does not matter what size the rollback segemtn is, there is no distinction between the determination of the next rollback segment to use, regardless of the size of the rollback segment. Two parameters, TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT can be set for each instance. On startup the instance will attempt to aquite TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT segments. Aquire specific rollback segments by setting the ROLLBACK_SEGMENTS parameter.
Before additional tablespaces can be used, an additional rollback segment must be created in the SYSTEM tablespace. Only after this has been done can additional rollback segments be created and brought on-line in a non-SYSTEM tablespace. Note that this additional rollback segment should be dropped or taken off-line as soon as possible after the other rollback segments have been created.
Base rollback segment size on the most common transactions. For an OLTP database with short transactions use many small rollback segments and with batch transactional database with large transactions use a few large rollback segments. Small rollback segments may always be cached in memory and thus never involve any I/O. The disadvantage of small rollback segments is increased possibility of snapshot too old errors. These errors occur when rollback entries required for read consistency are overwritten as other update entries wrap around the rollback segment. Design an applications transactions as being short granular units of work thus avoidinf rollback size limitation problems. Long running transactions need the preallocated extents of large rollback segments. When a mix of differing sizes of rollback segments are created then ALL transactions should be explicitly assigned to a rollback segments. Rollback segments are cycled through by transactions, regardless of their size. This could result in small tranasactions being placed into large rollback segments, under-utilisation of space, and large transactions being assigned to small rollback segments, risking snapshot too old errors, ie. overwriting of require information or even reaching the maximimum number of extents for a rollback segments if specified. Also if a large transaction is automatically placed into a small rollback segment much I/O results since many extra extents are begin allocated.
SET TRANSACTION USE ROLLBACK SEGMENT large_rbs1;
In general it is best to keep rollback segment sizes consitent otherwise all database transactions require specific rollback segment allocation to be most efficient. Make each rollback segment about 5-10% the size of the database's largest table since most SQL statements generally access at 5-10% of a table's data. The MAXEXTENTS should be set high for rollback segments. Even set rollback segment extents to unlimited. However, unlimited, automatically extending rollback segments could become very large with run-away transactions for instance in heavy ad-hoc environments.
Determine a rollback segment size based on transactional requirements. Base the size of rollback segments on the size of the most common transactions issued against the database. Shorter transactions perform better with smaller rollback segments and longer transactions, for instance, batch jobs, perform better with larger rollback segments.
To achieve optimal rollback I/O performance, divide each rollback segment's total allocated space among many equally sized extents. The recommended numbers are 10 to 20 equally sized extents for each rollback segment. Once you have an idea of the required size set the storage parameters for the rollback segments.
The optimal size is the size the rollback segment will shrink back to after a lengthy transaction. The optimal size for a rollback segment is the size the segment should be to contain typical transaction information. For example, if a large optimal size is not set for large transactions Oracle will allocate new extents because all the existing extents are filled. Oracle can not write over extents which contain active transactions. Each added extent slows performance.
Set optimal high if the database runs lengthy transactions. What if most transactions are short ? If transactions are short set the optimal parameter to the size determined required for the rollback segment. Using a smaller optimal setting increases database performance because smaller rollback segments can remain in memory.
Rollback segments can also be shrunk to reclaim unused extents.
ALTER ROLLBACK SEGMENT rbs0 SHRINK TO 100K;
Private rollback segments apply to a single instance and public rollback segments are shared between instances in the case of a parallel server where multiple instances use a single set of datafiles.
Store rollback segments in a separate tablespace. Thus rollback data is stored on separate datafiles and possibly on separate disks to that of other data. Note that tablespaces with active rollback data cannot be taken offline. If rollback segments are stored with other segment types then there is the potential that data segment tablespaces would not be able to be taken offline for instance for backups.
In short, don't do it. Rather use separate tablespaces for separate datafile requirements for differeing types of rollback segments. Never create rollback segments using multiple datafiles within a single tablespace. Always use a single datafile or multiple tablespaces with a single datafile each. Rollback segments will not autoextend for a single transaction across multiple datafiles with the same tablespace. An attempt to extend in this fashion will simply cause Oracle to send the calling process a break (Ctl-C) and the transaction will be aborted. There should be some evidence in the alert log showing a rollback segment running out of space. This alert log evidence will not be in the form of ORA-00??? errors.
Large rollback segments are useful for separating large and small transactions and obviously assigning the large transaction exclusively to the larger rollback segments. However, there appears to be a slight problem in this case. Even when large rollback segments are not utilised Oracle appears to switch them online and use them. A common cause for this could simply be not enough general transactional (smaller) rollback segments. However, I have not found any evidence of this. It is probably expedient to periodically ensure that large rollback segments remain offline when not in use either manually or automatically. Small transactions placed into large rollback segments is not neccessarily inefficient since rollback segments can be forced to maintain optimal sizes and innefficiencies creep into rollback segments when allocating many extents for large transactions or one extent for small transactions. The problem is that if a large transaction is switched to a large rollback segment when in use by a small transaction then the large transaction will fail on setting of the rollback segment and thus negate the reason for the larger rollback segment.