As Published In 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.

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: 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."

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    /

187

PL/SQL procedure successfully completed.

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: 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.

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.

Conclusion

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.

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

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
UNDOTBS02                      UNDO
UNDOTBS03                      UNDO
UNDOTBS04                      UNDO

alter system set undo_tablespace = <
                                     new_undo_tablespace_name>;
                                  

SQL> select owner, segment_name, tablespace_name from dba_rollback_segs;

OWNER  SEGMENT_NAME                   TABLESPACE_NAME
------ ------------------------------ ------------------------------
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
...

SQL>select tuned_undoretention from v$undostat;

TUNED_UNDORETENTION
-------------------
                900
               2700
               2700