18
Configuring Undo and Temporary Segments

There are performance considerations when configuring undo and temporary segments.

This chapter contains the following topics:

• Configuring Undo Segments
• Configuring Temporary Tablespaces

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:

UNDO_MANAGEMENT=AUTO

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

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

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:

SET TRANSACTION USE ROLLBACK SEGMENT oltp_13

Also, monitor the shrinking, or dynamic deallocation, of rollback segments based on the OPTIMAL storage 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:

• Setting PCTINCREASE to zero
• Setting INITIAL and NEXT to the same size and a factor of SORT_AREA_SIZE

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
• Tablespaces of Type TEMPORARY
• Permanent Tablespaces

Temporary Tablespaces

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

• Space management (extent allocation and deallocation) is locally managed. Therefore, use of the ST-enqueue is avoided.
• The sort segment created for each instance is reused (it is only dropped if the tablespace is dropped). With Oracle Real Application Clusters, one sort segment is required for each instance.
• All processes performing sorts reuse existing sort extents of the sort segment, rather than allocating a segment (and potentially many extents) for each sort. If an insufficient number of extents exist for the number of sorts currently in operation, then the required extents are added once for each instance startup. They are recycled thereafter.
• Temporary tablespaces can be striped, as described in "Basic I/O Configuration". Only when the tablespaces is creating an I/O bottleneck should the tablespace be segregated from others.
• To create temporary tablespaces, use the CREATE TEMPORARY TABLESPACE statement.

  See Also: Oracle9i Database Concepts for more information on temporary tablespaces Oracle9i SQL Reference for more information on using the CREATE TEMPORARY TABLESPACE statement

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:

• Space management is dictionary managed so they use the ST-enqueue.
• The sort segment for an instance is dropped on instance startup and re-created when the first sort is performed. Extents are then allocated as needed.
• Although space management is dictionary-managed, the frequency of space allocation and deallocation is reduced. All processes performing sorts reuse the existing sort extents of a single sort segment, rather than allocating an deallocating extents and segments for each sort. With Oracle Real Application Clusters, one sort segment is required for each instance. If insufficient extents exist for the number of sorts, then the required extents are added once for each instance startup, and are recycled thereafter. This in turn reduces the load on the ST enqueue considerably.
• To create tablespaces of type TEMPORARY, use the CREATE TABLESPACE or ALTER TABLESPACE statements with the TEMPORARY clause.

  See Also: Oracle9i SQL Reference for more information on using the TEMPORARY clause

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:

• The ST-enqueue is used for allocation and de-allocation of each extent allocated to a sort segment.
• Sort-segments are not reused. Each process performing a disk sort creates then drops it's own sort segment. In addition, a single sort operation can require the allocation and deallocation of many extents, and each extent allocation requires the ST-enqueue.

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

CREATE 
TEMPORARY 
TABLESPACE

CREATE 
TABLESPACE 
... TYPE 
TEMPORARY

CREATE 
TABLESPACE

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