As Published In
Oracle Magazine
July/August 2005

TECHNOLOGY: Talking Tuning

Tuning Undo Tablespace

By Kimberly Floss

Let Oracle do the math and right-size the undo tablespace for you.

A company's quarter-end processing includes several long-running transactions. At the same time, several customer service representatives are updating information contained in the same underlying tables.

Here's a pop quiz for you: How many rollback segments should be online, and how big should they be, to ensure that the transaction completes and that no one gets an "ORA-1555: snapshot too old" error message while using the system? The short answer is, "I'd rather be doing something better with my time than fiddling around with the size of the database's rollback segments." It's not that the math is rocket science, but you have to take more into account than just the length of your longest-running transactions. And the work doesn't end once you've put away your spreadsheet—you must still create the optimal number of rollback segments, perhaps assign long-running transactions to a specific set of rollback segments, put additional segments online as processing demands change, and perform a host of other rollback-segment-related management tasks.

Why look back?
Review of undo capability and why it's needed

Whether contained in manually created rollback segments or in system-generated and managed undo segments, undo information is used by Oracle to accomplish three critical tasks: 

  • Rollback a given transaction when called upon to do so by a user or for recovery reasons

  • Provide read consistency to multiple users and applications, across the scope of different transactions

  • Ensure that Flashback Query-related features, if implemented in the system, can be supported

In all three cases, Oracle must have a 'before' image of data, which is provided by the undo (or rollback) segments, in order to work properly.

For example, let's say while a quarterly batch transaction is updating hundreds of records across dozens of tables, including the accounts receivable table, a customer service rep has begun updating a customer's balance due. If the batch transaction (which began before the customer service rep began his session) is trying to update the same record that has just been modified in the context of a different transaction (the customer service rep's transaction), then Oracle Database turns to the undo segment to recall the state of that record at the time the batch transaction began, to ensure read consistency for that transaction. If that section of the undo data has already been overwritten, the "ORA-1555: snapshot too old" error occurs, and the transaction cannot complete.

Since the advent of Oracle9i, however, there's been another, less time-consuming way—using Automatic Undo Management—in which Oracle Database creates and manages rollback segments (now called "undo segments") in a special-purpose undo tablespace. Unlike with rollback segments, you don't create or manage individual undo segments—Oracle Database does that for you when you create the undo tablespace. All transactions in an instance share a single undo tablespace. Any executing transaction can consume free space in the undo tablespace, and when the transaction completes, its undo space is freed (depending on how it's been sized and a few other factors, as we'll see in a minute). Thus, space for undo segments is dynamically allocated, consumed, freed, and reused—all under the control of Oracle Database, rather than by you.

So, the original question has changed and a simpler question remains—how do you properly size the undo tablespace to begin with, so that Oracle Database has sufficient space to record undo information for all of your transactions?

The Oracle Database 10g Undo Advisor was designed for that specific purpose. Using statistical data from the Automatic Workload Repository (AWR) over a specified time period, the Undo Advisor lets you model different scenarios for your system and obtain recommendations for the size of the undo tablespace.

Undo Management

The easiest way to get a quick look at undo tablespace utilization is to access the Undo Management page (using Oracle Enterprise Manager). From the Database Control (or Grid Control) home page, click on the Administration tab and then click on the Undo Management link (under the Instance heading) to open the Undo Management page (see Figure 1).


figure 1
Figure 1: Undo Management page

As shown in Figure 1, the Undo Management page shows key statistics for the active undo tablespace for the instance—the longest-running query, average kilobytes per minute of undo generated by the system, and the maximum number of kilobytes per minute of undo generated by the system during the selected time period. These values change as you change the time frame for analysis by choosing among the presets in the drop-down list (Last Seven Days, Last One Day, Last One Hour, or Customize Time Period). If you try to select a time period for which the AWR doesn't have viable data, you'll get the error message: "Data required for analysis are not available in the database server during the selected time period."

Migrating from Rollback Segments to UNDO Tablespace

To move an existing Oracle9i or Oracle Database 10g system from manual undo management (using rollback segments) to automatic undo management requires a shut down and restart—you must shut-down the database, set the undo_management parameter to AUTO , and then restart the database—assuming you've created the UNDO tablespace first, however. To get some guidance about how large to make that UNDO tablespace, you can use a function from the Undo Advisor PL/SQL package created just for that purpose, as in this example:


SQL> set serveroutput on;
SQL> declare utbsize_in_MB number;
  2    begin
  3    utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
  4    dbms_output.put_line(TO_CHAR(utbsize_in_MB));
  5    end;
  6    /


PL/SQL procedure successfully completed.

In this case, the return value of 187 is the suggested starting size in megabytes for an UNDO tablespace based on current rollback segment utilization in an existing system.

In addition, as shown in Figure 1, the Undo Management page displays any potential problems and provides recommendations, if appropriate; in this case, the Undo Management page warns that the undo tablespace is too small—"Undo tablespace cannot support required undo retention"—and recommends sizing the undo tablespace to 1,147MB. Also note that the time frame in the Analysis Time Period drop-down list covers the last seven days, as shown in Figure 1.

The analysis on the Undo Management page also takes into account the configuration of the undo tablespace—its size and whether its associated datafile is auto-extensible. You'll also see values for "auto-tuned undo retention" and the "low threshold undo retention" at the top of the page.

"Undo retention" in general is how long Oracle Database will keep undo information before reusing the underlying extents in the undo segments. As the system runs, Oracle Database automatically tunes the undo retention, based on actual usage, increasing and decreasing the length as required for undo retention to keep ahead of the longest-running query and thereby avoiding query and transaction failures with errors such as "snapshot too old." This is the "Auto-tuned Undo Retention" value (shown in Figure 1).

Although the Undo Management page shows you at a glance many of the variables of the existing undo tablespace; its configuration; and how effective that configuration is, given the runtime statistics collected in the AWR, you'll turn to the Undo Advisor when you want to extrapolate how future requirements might affect the undo tablespace size. Click on the Undo Advisor button on the Undo Management page, and the Undo Advisor page will appear (see Figure 2).


figure 2
Figure 2: Undo Advisor page

Using the Undo Advisor

Like the Undo Management page, the Undo Advisor page also lets you adjust the time frame for analysis, so you can extrapolate future undo tablespace needs, based on the most representative time period. As you adjust the two variables on the Undo Advisor page—New Undo Retention and Analysis Time Period—click on the Update Analysis and Graph button to dynamically update the Analysis and Recommendations sections on the page as well as the line graph. Each of these two depictions (text and graphical line data) provides suggestions about how to size the undo tablespace to meet both undo-retention and long-running transaction requirements.

Next Steps

 READ other articles in this series

 LEARN more about undo management

 GET information about Kimberly Floss' book

 DOWNLOAD Oracle Database 10g

So, for example, as shown in Figure 2, the Undo Advisor shows that supporting two hours of retention requires 22MB of undo tablespace, based on an analysis of data accumulated (in the AWR) for the past seven days ("Last Seven Days" in the Analysis Time Period drop-down list). If you change the time frame for the analysis, the Undo Advisor recommendation might change if the workload characteristics of the new time frame are significantly different.

If you want the system to be able to support the requirements modeled in the Undo Advisor, you simply return to the Undo Management page and make the changes. For example, clicking on the Edit Undo Tablespace button on the Undo Management page displays the information about the tablespace and provides access (via a subsequent button) to editing the specifics of the underlying datafile associated with the tablespace. From here, you can change the size of the datafile, change the auto-extensible parameter, and make other changes in real time as the system is running.


The Undo Advisor lets you adjust the variables related to proper sizing of undo tablespaces. The Undo Management page lets you change the undo tablespace in use or edit the configuration of that tablespace, based on a problem exposed on this page or based on recommendations from the Undo Advisor.


A View of Undo

The DBA_TABLESPACES view maintains data about UNDO tablespaces just as it does with any other tablespace in your system:


SQL> select tablespace_name, contents from dba_tablespaces where contents = 'UNDO';

------------------------------ ---------
UNDOTBS02                      UNDO
UNDOTBS03                      UNDO
UNDOTBS04                      UNDO

Although you can create multiple UNDO tablespaces (as shown here, there are three), only one can be online at any given time, as set in the undo_tablespace initialization parameter. If you have only one UNDO tablespace, this parameter isn't used, but if you have more than one, be sure to set it to the UNDO tablespace you want your system the 'alter system' command changes initialization parameters for the database system (this has nothing to do with 'system' user) to use, as in:


alter system set undo_tablespace = <

The database must be in AUTO undo management mode (with the undo_management initialization parameter set to AUTO ) in order for this parameter to be valid.

After the database comes up in auto undo management mode, Oracle Database selects the first available undo tablespace (or the one specified by the undo_tablespace parameter). Oracle Database dynamically adjusts the number of undo segments on and offline in response to system activity. You can query the DBA_ROLLBACK_SEGS view to see the system-generated undo segments, as follows:


SQL> select owner, segment_name, tablespace_name from dba_rollback_segs;

------ ------------------------------ ------------------------------
PUBLIC _SYSSMU15$                     UNDOTBS02
PUBLIC _SYSSMU16$                     UNDOTBS02
PUBLIC _SYSSMU17$                     UNDOTBS02
PUBLIC _SYSSMU18$                     UNDOTBS02
PUBLIC _SYSSMU19$                     UNDOTBS02
PUBLIC _SYSSMU20$                     UNDOTBS02
PUBLIC _SYSSMU21$                     UNDOTBS02
PUBLIC _SYSSMU22$                     UNDOTBS02
PUBLIC _SYSSMU23$                     UNDOTBS02
PUBLIC _SYSSMU24$                     UNDOTBS02

However, other than looking at the names, there's nothing you can do with these segments—Oracle Database manages them completely. The V$UNDOSTATS view lets you look at all statistics associated with the UNDO tablespace, including the tuned_undoretention value over time, as in:


SQL>select tuned_undoretention from v$undostat;


Kimberly Floss ( is president of the International Oracle Users Group . She specializes in Oracle performance tuning and SQL tuning techniques and is also the author of Oracle SQL Tuning & CBO Internals, from Rampant TechPress.

Send us your comments