Skip Headers

Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)

Part Number A96533-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Configuring Undo and Temporary Segments

There are performance considerations when configuring undo and temporary segments.

This chapter contains the following topics:

Configuring Undo Segments

Oracle provides automatic undo management, which completely automates the management of undo data. A database running in automatic undo management mode transparently creates and manages undo segments. Oracle Corporation strongly recommends using automatic undo management, because it significantly simplifies database management and removes the need for any manual tuning of undo (rollback) segments. Manual undo management using rollback segments is supported for backward compatibility reasons.

Configuring Automatic Undo Management

To configure automatic undo, you can simply include the following initialization parameter:


You can also create an undo tablespace, and determine the maximum retention time for undo data kept in that tablespace.

See Also:

Oracle9i Database Administrator's Guide for detailed information on how to configure automatic undo

Configuring Rollback Segments

Automatic undo management is the preferred way of handling rollback space. Automatic undo management lets you allocate undo space in a single undo tablespace, instead of distributing undo space in a set of statically allocated rollback segments. The creation and allocation of space among the undo segments is handled automatically by the Oracle server.

With rollback segments, one or more tablespaces are created; rollback segments are manually created in those tablespaces. The number and size of the rollback segments must be determined by the DBA.

Determining the Number and Size of Rollback Segments

The size of rollback segments can affect performance. Rollback segment size is determined by the rollback segment's storage parameter values. Your rollback segments must be large enough to hold the rollback entries for your transactions. As with other objects, avoid dynamic space management in rollback segments.

Table 18-1 shows some general guidelines for choosing how many rollback segments to allocate based on the number of concurrent transactions on your database. These guidelines are appropriate for most application mixes.

Table 18-1 Choosing the Number of Rollback Segments
Number of Current Transactions (n) Number of Rollback Segments Recommended

n < 16


16 <= n < 32


32 <= n


Use the SET TRANSACTION statement to assign transactions to rollback segments of the appropriate size based on the recommendations in the following sections. If you do not explicitly assign a transaction to a rollback segment, then Oracle automatically assigns it to a rollback segment.

For example, the following statement assigns the current transaction to the rollback segment oltp_13:



If you are running multiple concurrent copies of the same application, then be careful not to assign the transactions for all copies to the same rollback segment. This leads to contention for that rollback segment.

Also, monitor the shrinking, or dynamic deallocation, of rollback segments based on the OPTIMAL storage parameter.

See Also:

Oracle9i Database Administrator's Guide for information on choosing values for this parameter, monitoring rollback segment shrinking, and adjusting the OPTIMAL parameter

For Long Queries

Assign large rollback segments to transactions that modify data that is concurrently selected by long queries. Such queries might require access to rollback segments to reconstruct a read-consistent version of the modified data. The rollback segments must be large enough to hold all the rollback entries for the data while the query is running.

For Long Transactions

Assign large rollback segments to transactions that modify large amounts of data. A large rollback segment can improve the performance of such a transaction, because the transaction generates large rollback entries. If a rollback entry does not fit into a rollback segment, then Oracle extends the segment. Dynamic extension reduces performance; avoid it whenever possible.

For OLTP Transactions

OLTP applications are characterized by frequent concurrent transactions, each of which modifies a small amount of data. Assign OLTP transactions to small rollback segments, as long as their data is not concurrently queried. Small rollback segments are more likely to remain stored in the buffer cache where they can be accessed quickly. A typical OLTP rollback segment might have two extents, each approximately 10 kilobytes in size. To best avoid contention, create many rollback segments and assign each transaction to its own rollback segment.

Configuring Temporary Tablespaces

Configuring the temporary tablespace helps optimize disk sort performance. This involves choosing good storage clauses and the correct type of tablespace to use for sorting.

Choosing the default storage clause for the sort tablespace includes the following:

Choosing the correct type of tablespace makes disk sorting more efficient. The various tablespaces that could be used for disk sorting include the following:

Temporary Tablespaces

These are the most efficient tablespaces for disk sorts. Characteristics of a temporary tablespace include the following:

Tablespaces of Type TEMPORARY

After temporary tablespaces, these are next best tablespaces to use for sort operations. Characteristics of tablespaces of type TEMPORARY include the following:

Permanent Tablespaces

Permanent tablespaces (which are not of type TEMPORARY) are least efficient for performance of disk sorts. This is because of the following reasons:

For optimal performance, the best choice is to use temporary tablespaces. They bypass the need of using the ST enqueue for space management and have the additional benefit of reuse of sort extents.

Table 18-2 Sort Tablespaces
Tablespace Type Space Management Sort Segment Reuse Sort Extent Reuse Creation Statement


(uses tempfiles)

Locally Managed

Yes. Sort segment is not dropped




(uses datafiles)

Dictionary Managed

Sort segment is re-created on instance startup

Yes. Extents are reused until sort segment is dropped on instance startup

... TYPE 


Dictionary Managed

No. Sort segments are not reused

No. Sort segments are not reused


Temporary tablespaces, and tablespaces of type TEMPORARY, cannot contain permanent objects, such as tables or rollback segments.

See Also:

Oracle9i SQL Reference for more information about the syntax of the CREATE TABLESPACE and ALTER TABLESPACE statements