My Oracle Support Banner

What is the Undo Advisor and how to Use it through the DBMS_UNDO_ADV package (Doc ID 1580225.1)

Last updated on DECEMBER 30, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 21-Jul-2017***


Explain Undo Advisor and the options available

Automatic tuning of undo retention typically achieves better results with a fixed-size undo tablespace. If you decide to use a fixed-size undo tablespace, the Undo Advisor can help you estimate needed capacity.

You can access the Undo Advisor through Oracle Enterprise Manager (EM) or through the DBMS_ADVISOR PL/SQL package or through the DBMS_UNDO_ADV PL/SQL package.

In This Document we will explain how to use the DBMS_UNDO_ADV PL/SQL package.

The package DBMS_UNDO_ADV is undocumented , and it is used internally by the Undo Advisor .

The Undo Advisor assists in correctly sizing the undo tablespace and to set the low threshold value of the undo retention period for any Oracle Flashback requirements.

The Undo Advisor can also be used to estimate the Undo Tablespace needed for migration from Manual To Automatic Undo management, before actually creating the new undo tablespace which will use automatic undo management.

The Undo Advisor relies for its analysis on data collected in the Automatic Workload Repository (AWR). It is therefore important that the AWR have adequate workload statistics available so that the Undo Advisor can make accurate recommendations. For newly created databases, adequate statistics may not be available immediately. In such cases, continue to use the default auto-extending undo tablespace until at least one workload cycle completes.

Note: To make the undo tablespace fixed-size, Oracle suggests that you first allow enough time after database creation to run a full workload, thus allowing the undo tablespace to grow to its minimum required size to handle the workload. Then, you can use the Undo Advisor to determine, if desired, how much larger to set the size of the undo tablespace to allow for long-running queries and Oracle Flashback operations.

An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations that the advisor produces. See Oracle Database Performance Tuning Guide for more information.

Please refer the following document on how to modify the collection interval and retention period for the AWR :

How to Modify the statistics collection by MMON for AWR repository (Doc ID 308450.1)

Running the Undo Advisor does not alter the size of the undo tablespace. The advisor just returns a recommendation. You must use ALTER DATABASE statements to change the tablespace data files to fixed sizes.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Explain Undo Advisor and the options available
 Estimate the Undo Tablespace Size needed for migration from Manual To Automatic Undo management .
Provide information about undo tablespace of the current instance .
Determine if auto tuning of undo retention is enabled or not.
Check the length of the longest query for a given period .
 The required undo_retention to satisfy longest query .
Check best possible undo_retention the current undo tablespace can satisfy .
The required undo tablespace size to satisfy certain undo retention value .
Verify current undo_retention and undo tablespace size (check whether its optimal).
 Check if there is any problem with the current instance and provide recommendations.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.