|Oracle9i Database Concepts
Release 2 (9.2)
Part Number A96524-01
This appendix contains information the following topics:
If you created your database with an earlier version of Oracle, then you could be using these features. Locally managed tablespaces are recommended over dictionary managed tablespaces, and automatic undo space management is recommended over manual undo space management, which uses rollback segment.
Prior to Oracle8i, all tablespaces were created as dictionary managed. Dictionary managed tablespaces rely on data dictionary tables to track space utilization. Beginning with Oracle8i, you could create locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. Because of the better performance and greater ease of management of locally managed tablespaces, the default for non-
SYSTEM permanent tablespaces is locally managed whenever the type of extent management is not explicitly specified.
If you created your database with an earlier version of Oracle, then you could be using dictionary managed tablespaces. With dictionary managed tablespaces, Oracle controls the allocation of incremental extents for a given segment as follows:
In this example, if Oracle does not find a set of exactly 20 contiguous data blocks, then Oracle searches for a set of contiguous data blocks greater than 20. If the first set it finds contains 25 or more blocks, then it breaks up the blocks and allocates 20 of them to the new extent and leaves the remaining five or more blocks as free space. Otherwise, it allocates all of the blocks (between 21 and 24) to the new extent.
The blocks of a newly allocated extent, although they were free, may not be empty of old data. Usually, Oracle formats the blocks of a newly allocated extent when it starts using the extent, but only as needed (starting with the blocks on the segment free list). In a few cases, however, such as when a database administrator forces allocation of an incremental extent with the
EXTENT clause of an
CLUSTER statement, Oracle formats the blocks in the extent when it allocates the extent.
In earlier releases, undo space management was performed using rollback segments. This method is now called manual undo management mode. Manual undo management mode is supported under any compatibility level. Use it when you need to run Oracle9i to take advantage of some new features, but are not yet not ready to convert to automatic undo management mode.
Although manual undo management mode is supported, Oracle Corporation strongly recommends that you run in automatic undo management mode.
Each database contains one or more rollback segments. A rollback segment records the old values of data that were changed by each transaction (whether or not committed). Rollback segments provide read consistency, roll back transactions, and recover the database.
Oracle Corporation strongly recommends using automatic undo management. This section is included only for backward compatibility with previous releases.
Information in a rollback segment consists of several rollback entries. Among other information, a rollback entry includes block information (the file number and block ID corresponding to the data that was changed) and the data as it existed before an operation in a transaction. Oracle links rollback entries for the same transaction, so the entries can be found easily if necessary for transaction rollback.
Neither database users nor administrators can access or read rollback segments. Only Oracle can write to or read them. (They are owned by the user
SYS, no matter which user creates them.)
Rollback entries change data blocks in the rollback segment, and Oracle records all changes to data blocks, including rollback entries, in the redo log. This second recording of the rollback information is very important for active transactions (not yet committed or rolled back) at the time of a system crash. If a system crash occurs, then Oracle automatically restores the rollback segment information, including the rollback entries for active transactions, as part of instance or media recovery. When recovery is complete, Oracle performs the actual rollbacks of transactions that had been neither committed nor rolled back at the time of the system crash.
For each rollback segment, Oracle maintains a transaction table--a list of all transactions that use the associated rollback segment and the rollback entries for each change performed by these transactions. Oracle uses the rollback entries in a rollback segment to perform a transaction rollback and to create read-consistent results for queries.
Rollback segments record the data prior to change for each transaction. For every transaction, Oracle links each new change to the previous change. If you must roll back the transaction, then Oracle applies the changes in a chain to the data blocks in an order that restores the data to its previous state.
Similarly, when Oracle needs to provide a read-consistent set of results for a query, it can use information in rollback segments to create a set of data consistent with respect to a single point in time.
Each time a user's transaction begins, the transaction is assigned to a rollback segment in one of two ways:
For the duration of a transaction, the associated user process writes rollback information only to the assigned rollback segment.
When you commit a transaction, Oracle releases the rollback information but does not immediately destroy it. The information remains in the rollback segment to create read-consistent views of pertinent data for queries that started before the transaction committed. To guarantee that rollback data is available for as long as possible for such views, Oracle writes the extents of rollback segments sequentially. When the last extent of the rollback segment becomes full, Oracle continues writing rollback data by wrapping around to the first extent in the segment. A long-running transaction (idle or active) can require a new extent to be allocated for the rollback segment.
Each rollback segment can handle a fixed number of transactions from one instance. Unless you explicitly assign transactions to particular rollback segments, Oracle distributes active transactions across available rollback segments so that all rollback segments are assigned approximately the same number of active transactions. Distribution does not depend on the size of the available rollback segments. Therefore, in environments where all transactions generate the same amount of rollback information, all rollback segments can be the same size.
See your Oracle operating system-specific documentation for more information.
When you create a rollback segment, you can specify storage parameters to control the allocation of extents for that segment. Each rollback segment must have at least two extents allocated.
One transaction writes sequentially to a single rollback segment. 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:
The first transaction that needs to acquire more rollback space checks the next extent of the rollback segment. If the next extent of the rollback segment does not contain information from an active transaction, Oracle makes it the current extent, and all transactions that need more space from then on can write rollback information to the new current extent.
Figure B-1 illustrates two transactions, T1 and T2, which begin writing in the third extent (E3) and continue writing to the fourth extent (E4) of a rollback segment.
As the transactions continue writing and fill the current extent, Oracle checks the next extent already allocated for the rollback segment to determine if it is available. In Figure B-2, when E4 is completely full, T1 and T2 continue any further writing to the next extent allocated for the rollback segment that is available. In this figure, E1 is the next extent. This figure shows the cyclical nature of extent use in rollback segments.
To continue writing rollback information for a transaction, Oracle always tries to reuse the next extent in the ring first. However, if the next extent contains data from an active transaction, then Oracle must allocate a new extent. Oracle can allocate new extents for a rollback segment until the number of extents reaches the value set for the rollback segment's storage parameter
Figure B-3 shows a new extent allocated for a rollback segment. The uncommitted transactions are long running (either idle, active, or persistent in-doubt distributed transactions). At this time, they are writing to the fourth extent, E4, in the rollback segment. However, when E4 is completely full, the transactions cannot continue further writing to the next extent in sequence, E1, because it contains active rollback entries. Therefore, Oracle allocates a new extent, E5, for this rollback segment, and the transactions continue writing to this new extent.
When you drop a rollback segment, Oracle returns all extents of the rollback segment to its tablespace. The returned extents are then available to other segments in the tablespace.
When you create or alter a rollback segment, you can use the storage parameter
OPTIMAL (which applies only to rollback segments) to specify the optimal size of the segment in bytes. If a transaction needs to continue writing rollback information from one extent to another extent in the rollback segment, Oracle compares the current size of the rollback segment to the segment's optimal size. If the rollback segment is larger than its optimal size, and if the extents immediately following the extent just filled are inactive, then Oracle deallocates consecutive nonactive extents from the rollback segment until the total size of the rollback segment is equal to or close to, but not less than, its optimal size. Oracle always frees the oldest inactive extents, as these are the least likely to be used by consistent reads.
A rollback segment's
OPTIMAL setting cannot be less than the combined space allocated for the minimum number of extents for the segment. For example:
Oracle creates an initial rollback segment called
SYSTEM whenever a database is created. This segment is in the
SYSTEM tablespace and uses that tablespace's default storage parameters. You cannot drop the
SYSTEM rollback segment. An instance always acquires the
SYSTEM rollback segment in addition to any other rollback segments it needs.
With 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, then Oracle uses the
SYSTEM segment as necessary. In general, after database creation, create at least one additional rollback segment in the
When an Oracle instance opens a database, it must acquire one or more rollback segments so that the instance can handle rollback information produced by subsequent transactions. An instance can acquire both private and public rollback segments. A private rollback segment is acquired explicitly by an instance when the instance opens a database. Public rollback segments form a pool of rollback segments that any instance requiring a rollback segment can use.
Any number of private and public rollback segments can exist in a database. As an instance opens a database, the instance attempts to acquire one or more rollback segments according to the following rules:
SYSTEMsegment. If the instance is one of several instances accessing the database in a Real Application Clusters environment, then it acquires the
SYSTEMrollback segment and at least one other rollback segment. If it cannot, Oracle returns an error, and the instance cannot open the database.
CEIL is a SQL function that returns the smallest integer greater than or equal to the numeric input. In the previous example, if
TRANSACTIONS equal 155 and
TRANSACTIONS_PER_ROLLBACK_SEGMENT equal 10, then the instance tries to acquire at least 16 rollback segments. (However, an instance can open the database even if the instance cannot acquire the number of rollback segments given by the division in the previous example.)
SYSTEMrollback segment, the instance next tries to acquire all private rollback segments specified by the instance's
ROLLBACK_SEGMENTSparameter. If one instance in Oracle Real Application Clusters opens a database and attempts to acquire a private rollback segment already claimed by another instance, the second instance trying to acquire the rollback segment receives an error during startup. An error is also returned if an instance attempts to acquire a private rollback segment that does not exist.
After an instance claims a public rollback segment, no other instance can use that segment until either the rollback segment is taken offline or the instance that claimed the rollback segment is shut down.
A database used by Oracle9i Real Application Clusters can have both public and private segments. Use of private segments is recommended.
for more information about rollback segment use with Real Application Clusters
A rollback segment is always in one of several states, depending on whether it is offline, acquired by an instance, involved in an unresolved transaction, in need of recovery, or dropped. The state of the rollback segment determines whether it can be used in transactions, as well as which administrative procedures a database administrator can perform on it.
The rollback segment states are:
Has not been acquired (brought online) by any instance.
Has been acquired (brought online) by an instance and can contain data from active transactions.
Contains data from uncommitted transactions that cannot be rolled back (because the data files involved are inaccessible) or is corrupted.
Contains data from an in-doubt transaction (that is, an unresolved distributed transaction).
Has been dropped. (The space once allocated to this rollback segment is later used when a new rollback segment is created.)
The data dictionary table
DBA_ROLLBACK_SEGS lists the state of each rollback segment, along with other rollback information.
Figure B-4 shows how a rollback segment moves from one state to another.
RECOVERY states are very similar. A rollback segment in either state usually contains data from an unresolved transaction.
REMOVE_AVAILABLErollback segment is being used by an in-doubt distributed transaction that cannot be resolved because of a network failure. A
RECOVERYrollback segment is being used by a transaction (local or distributed) that cannot be resolved because of a local media failure, such as a missing or corrupted datafile, or is itself corrupted.
REMOVE_AVAILABLErollback segment online. In contrast, you must take a
OFFLINEbefore it can be brought online. (If you recover the database and thereby resolve the transaction, then Oracle automatically changes the state of the
RECOVERYrollback segment to
RECOVERYrollback segment. (This lets the database administrator drop corrupted segments.) A
REMOVE_AVAILABLEsegment cannot be dropped. You must first resolve the in-doubt transaction, either automatically by the RECO process or manually.
If you bring a
REMOVE_AVAILABLE rollback segment online (by a statement or during instance startup), then Oracle can use it for new transactions. However, the in-doubt transaction still holds some of its transaction table entries, so the number of new transactions that can use the rollback segment is limited.
Until you resolve the in-doubt transaction, the transaction continues to hold the extents it acquired in the rollback segment, preventing other transactions from using them. Thus, the rollback segment may need to acquire new extents for the active transactions, and therefore grow. To prevent the rollback segment from growing, a database administrator can create a new rollback segment for transactions to use until the in-doubt transaction is resolved, rather than bring the
REMOVE_AVAILABLE segment online.
When a tablespace goes offline so that transactions cannot be rolled back immediately, Oracle writes to a deferred rollback segment. The deferred rollback segment contains the rollback entries that could not be applied to the tablespace, so that they can be applied when the tablespace comes back online. These segments disappear as soon as the tablespace is brought back online and recovered. Oracle automatically creates deferred rollback segments in the
The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.
Figure B-5 shows a segment consisting of three extents containing 10K, 20K, and 30K of space, respectively. The high water mark is in the middle of the second extent. Thus, the segment contains 20K of used space to the left of the high water mark, and 40K of unused space to the right of the high water mark.
For manually managed tablespaces, two space management parameters,
PCTUSED, enable you to control the use of free space for inserts and updates to the rows in all the data blocks of a particular segment. Specify these parameters when you create or alter a table or cluster (which has its own data segment). You can also specify the storage parameter
PCTFREE when creating or altering an index (which has its own index segment).
See "LOB Datatypes" for more information.
PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a
This states that 20% of each data block in this table's data segment be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area, until the row data and overhead total 80% of the total block size. Figure B-6 illustrates
PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by
PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls below the parameter
PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter in a
In this case, a data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached
PCTFREE). Figure B-7 illustrates this.
PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Figure B-8 illustrates the interaction of these two parameters.
In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and free space (
PCTFREE). Updates to existing data can use any available space in the block. Therefore, updates can reduce the available space of a block to less than
PCTFREE, the space reserved for updates but not accessible to inserts.
For each data and index segment, Oracle maintains one or more free lists--lists of data blocks that have been allocated for that segment's extents and have free space greater than
PCTFREE. These blocks are available for inserts. When you issue an
INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in that block is not large enough to accommodate the
INSERT statement, and the block is at least
PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.
After you issue a
UPDATE statement, Oracle processes the statement and checks to see if the space being used in the block is now less than
PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. When the transaction commits, free space in the block becomes available for other transactions.