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

Last updated on JULY 05, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database 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.

Purpose

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.