Search Results undo_advisor

Overview

SYS.DBMS_UNDO_ADV is the Oracle-supplied Undo Advisor package that ships as part of the database kernel and is exposed to Oracle E-Business Suite environments running 12.1.1 and 12.2.2. Within the EBS technical stack, this package provides the programmatic foundation for the Automatic Undo Management (AUM) advisory framework. It enables DBAs and application administrators to evaluate whether the undo tablespace is correctly sized, to model the retention impact of long-running queries, and to obtain tuning recommendations that improve transaction concurrency and reduce the risk of "ORA-01555: snapshot too old" errors during EBS batch processing.

The package resides in the SYS schema and is classified in ETRM as an OTHER API. Its ETRM record confirms a VALID status and documents 25 procedures and functions, indicating full availability in the 12.2.2 environment. It depends on the internal array type DBMS_UADV_ARR and the STANDARD package, and it is referenced publicly and by PRVTEMX_ADMIN, the private package that supports Oracle Enterprise Manager's Undo Advisor front end.

Key Procedures and Functions

The documented entry points group into three functional areas:

• UNDO_INFO — Returns the current undo configuration and usage characteristics of the undo tablespace, forming the baseline data for advisory output.
• UNDO_ADVISOR / UNDO_HEALTH — Produce consolidated advisory and health-check output describing the state of undo management and highlighting potential problems.
• UNDO_AUTOTUNE — Supplies the automatic tuning recommendation for undo parameters.
• LONGEST_QUERY — Identifies the longest-running query, which drives the minimum undo retention requirement.
• REQUIRED_RETENTION and BEST_POSSIBLE_RETENTION — Compute the retention period the system must support versus the maximum retention achievable with current undo sizing.
• REQUIRED_UNDO_SIZE — Estimates the undo tablespace size needed to satisfy the required retention.
• RBU_MIGRATION — Supports migration or conversion activity relating to rollback segment to automatic undo management transitions.

These procedures are invoked internally by the database's undo management infrastructure rather than called directly in most EBS deployments.

Tables Accessed

The ETRM record does not list application tables accessed through APPS synonyms. DBMS_UNDO_ADV operates primarily against database-level dynamic performance views and internal fixed tables that describe undo segments, undo tablespace configuration, and active transaction behavior. No EBS application schema tables are documented as being read or written by this package, confirming its role as a kernel-level advisory utility rather than an application data processor.

Usage Notes

In EBS 12.1.1 and 12.2.2, DBMS_UNDO_ADV is typically invoked indirectly. The most common path is through Oracle Enterprise Manager's Undo Advisor, which consumes this package via PRVTEMX_ADMIN. DBAs also reach the same functionality through SQL*Plus by querying V$UNDOSTAT and related views, or by executing the advisor from the command line during capacity planning.

Because the package is owned by SYS and is classified as an internal API, direct calls from EBS concurrent programs or Forms-based customizations are not recommended. Undo tuning in EBS should instead be driven through the standard DBA workflow: capture advisor output during representative batch windows, compare REQUIRED_RETENTION against BEST_POSSIBLE_RETENTION, and adjust the undo tablespace size or UNDO_RETENTION parameter accordingly. One other package references DBMS_UNDO_ADV, so dependency changes should be reviewed before database upgrades or patches.