Search Results prvt_advisor




Overview
Explore in AI Chat

Overview

SYS.PRVT_ADVISOR is a private PL/SQL package owned by the SYS schema and shipped as part of the Oracle Database kernel, with its status recorded as VALID within the EBS 12.1.1 and 12.2.2 environments. Although it resides in the database layer rather than in the E-Business Suite APPS schema, it appears in ETRM dependency reporting because it underpins several advisory and tuning facilities that administrators reach through EBS tooling. The package functions as the internal implementation layer for the Advisor framework, providing the shared logic that the public DBMS_ADVISOR API and its siblings delegate to. It therefore supports business-facing capabilities such as SQL tuning, SQL Access Advisor recommendations, Automatic Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, SQL Plan Management, and automatic SQL tuning. In practical terms, the package enables the database to generate, store, and report on performance recommendations that DBAs rely on to keep an EBS application instance healthy.

Key Procedures and Functions

The ETRM documentation records zero documented procedures or functions for SYS.PRVT_ADVISOR. This is consistent with its status as an internal, private package: Oracle does not publish a supported callable interface for it. All functional entry points are exposed through the documented public packages that reference it, including DBMS_ADVISOR, DBMS_ADDM, DBMS_AUTO_SQLTUNE, DBMS_SPM, DBMS_SQLPA, DBMS_SQLTUNE, DBMS_UNDO_ADV, and DBMS_MANAGEMENT_PACKS. Because no parameter lists are documented, no specific procedure signatures should be assumed. Any direct invocation of this package is unsupported and should not be attempted from EBS custom code.

Tables Accessed

The dependency metadata identifies the advisor repository objects that this package interacts with. The base structure is WRI$_ADV_ABSTRACT_T, the abstract task table shared across Advisor implementations. Specialized tables extend this base for particular advisor types, including WRI$_ADV_SQLTUNE and WRI$_ADV_SQLACCESS_ADV for SQL tuning and SQL Access Advisor tasks, WRI$_ADV_HDM_T for health/ADDM data, WRI$_ADV_TUNEMVIEW_ADV for materialized view tuning, and WRI$_ADV_SPM_EVOLVE for SQL plan evolution. Reporting tables such as WRI$_REPT_SQLT, WRI$_REPT_SQLPI, WRI$_REPT_PLAN_DIFF, and WRI$_REPT_SPMEVOLVE hold the rendered output of advisor runs. These WRI$_ tables constitute the persistent Advisor repository, and all reads and writes occur under SYS ownership.

Usage Notes

PRVT_ADVISOR is invoked internally whenever a public Advisor API executes; it is not called directly from EBS Forms, concurrent programs, or standard application code. Users and DBAs trigger the underlying functionality indirectly, for example through the Automatic SQL Tuning job, ADDM runs after a snapshot interval, or SQL Tuning Advisor tasks. The package is referenced by approximately thirty-one other packages, several of which are themselves private, forming a deep internal hierarchy beneath the public DBMS_* interfaces. Because it is a private SYS package with no supported interface, it must never be referenced from custom EBS extensions. Administrators interacting with Advisor functionality should always use the documented public APIs, and should treat PRVT_ADVISOR as an implementation detail subject to change across database patch levels.