Search Results dbms_sqlpa

Overview

SYS.DBMS_SQLPA is the SQL Performance Analyzer (SPA) PL/SQL package shipped with the Oracle database kernel and exposed in Oracle E-Business Suite 12.1.1 and 12.2.2. It provides a programmatic interface for building, executing, and reporting on SQL performance analysis tasks, which allow an administrator to capture a workload of SQL statements, run it against a baseline environment, replay the identical workload after a change (a database upgrade, patch application, parameter change, or optimizer statistics refresh), and compare the two executions to quantify the performance impact before the change is promoted to production.

Within EBS the package is owned by SYS and classified as an OTHER API in the ETRM repository. It is not an application-tier business API; no EBS form or concurrent program calls it directly as part of standard processing. It is leveraged by DBAs and by Oracle's own tools—most notably the SQL Tuning Advisor infrastructure—to support pre- and post-upgrade validation such as the recommended workflow executed during an EBS 12.1.1 to 12.2.2 upgrade or a database release upgrade.

Key Procedures and Functions

The documented interface exposes eighteen procedures and functions that follow the standard Advisor task lifecycle:

CREATE_ANALYSIS_TASK — Creates a new SQL Performance Analyzer task, optionally seeded from a SQL tuning set or an existing workload, and returns the task name.
SET_ANALYSIS_TASK_PARAMETER and SET_ANALYSIS_DEFAULT_PARAMETER — Configure execution options for a specific task or for all future tasks, respectively (for example scope, execution type, and time limits).
EXECUTE_ANALYSIS_TASK — Runs the analysis task, executing or comparing the SQL workload as configured.
INTERRUPT_ANALYSIS_TASK, CANCEL_ANALYSIS_TASK, and RESET_ANALYSIS_TASK — Suspend a running task, cancel it permanently, or reset it to its initial state.
RESUME_ANALYSIS_TASK — Restarts a previously interrupted task from its last checkpoint.
DROP_ANALYSIS_TASK — Removes the task and its stored results from the Advisor repository.
REPORT_ANALYSIS_TASK — Generates the comparison report (text, HTML, or XML) summarizing per-statement performance changes.
GET_SESS_OPTIMIZER_ENV — Retrieves optimizer environment settings associated with the session or task.
REMOTE_PROCESS_SQL — Supports execution of SQL against a remote database as part of a distributed SPA comparison.

These procedures wrap the underlying task infrastructure in SYS.DBMS_ADVISOR.

Tables Accessed

SPA does not read or write EBS application tables. All state is persisted in the Advisor repository—principally the task, parameter, and result tables maintained by DBMS_ADVISOR (for example the DBA_ADVISOR_* and DBA_SQLPA_* views). The package additionally references SQL_BINDS when reconstructing bind information for captured statements, and depends on the STANDARD package. Results are queried through dictionary views rather than EBS synonyms.

Usage Notes

Because DBMS_SQLPA is a SYS-owned kernel package, it is invoked from SQL*Plus, SQL Developer, or custom PL/SQL by a DBA holding the ADVISOR privilege—not from EBS forms or concurrent managers. The canonical EBS pattern is to create a SQL tuning set on the pre-change system, create and execute an SPA task to establish the baseline, repeat the capture after the change, execute the comparison task, and then call REPORT_ANALYSIS_TASK to review regressions and improvements. It should be used when validating database upgrades, optimizer changes, initialization parameter modifications, or large patch rollouts. ETRM records it as referenced by four other packages, including DBMS_SQLTUNE_UTIL1, DBMS_WRR_INTERNAL, and PRVT_SQLADV_INFRA, confirming its role as shared infrastructure rather than a standalone end-user API.

PACKAGE: SYS.DBMS_SQLPA 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLPA, status:VALID,
PACKAGE: SYS.DBMS_SQLPA 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLPA, status:VALID,
SYNONYM: PUBLIC.DBMS_SQLPA 12.1.1
owner:PUBLIC, object_type:SYNONYM, object_name:DBMS_SQLPA, status:VALID,
SYNONYM: PUBLIC.DBMS_SQLPA 12.2.2
owner:PUBLIC, object_type:SYNONYM, object_name:DBMS_SQLPA, status:VALID,
TYPE: SYS.SQL_BINDS 12.1.1
owner:SYS, object_type:TYPE, object_name:SQL_BINDS, status:VALID,
TYPE: SYS.SQL_BINDS 12.2.2
owner:SYS, object_type:TYPE, object_name:SQL_BINDS, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLPA 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLPA, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLPA 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLPA, status:VALID,
PACKAGE: SYS.DBMS_ADVISOR 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_ADVISOR, status:VALID,
PACKAGE: SYS.DBMS_ADVISOR 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_ADVISOR, status:VALID,
PACKAGE: SYS.PRVT_SQLADV_INFRA 12.2.2
owner:SYS, object_type:PACKAGE, object_name:PRVT_SQLADV_INFRA, status:VALID,
LIBRARY: SYS.DBMS_SQLTUNE_LIB 12.1.1
owner:SYS, object_type:LIBRARY, object_name:DBMS_SQLTUNE_LIB, status:VALID,
PACKAGE: SYS.PRVT_SQLADV_INFRA 12.1.1
owner:SYS, object_type:PACKAGE, object_name:PRVT_SQLADV_INFRA, status:VALID,
LIBRARY: SYS.DBMS_SQLTUNE_LIB 12.2.2
owner:SYS, object_type:LIBRARY, object_name:DBMS_SQLTUNE_LIB, status:VALID,
PACKAGE: SYS.PRVT_SMGUTIL 12.1.1
owner:SYS, object_type:PACKAGE, object_name:PRVT_SMGUTIL, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL2 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL2, status:VALID,
PACKAGE: SYS.PRVT_SMGUTIL 12.2.2
owner:SYS, object_type:PACKAGE, object_name:PRVT_SMGUTIL, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLADV_INFRA 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLADV_INFRA, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL1 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL2 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL2, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL1 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL0 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL0, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLADV_INFRA 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLADV_INFRA, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL2 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL2, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL2 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL2, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL1 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL0 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL0, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL1 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_INTERNAL 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_INTERNAL, status:VALID,
PACKAGE: SYS.PRVT_ADVISOR 12.2.2
owner:SYS, object_type:PACKAGE, object_name:PRVT_ADVISOR, status:VALID,
PACKAGE: SYS.PRVT_ADVISOR 12.1.1
owner:SYS, object_type:PACKAGE, object_name:PRVT_ADVISOR, status:VALID,
VIEW: SYS.USER_ADVISOR_TASKS 12.1.1
owner:SYS, object_type:VIEW, object_name:USER_ADVISOR_TASKS, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_INTERNAL 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_INTERNAL, status:VALID,
OPERATOR: SYS.XMLSEQUENCE 12.2.2
owner:SYS, object_type:OPERATOR, object_name:XMLSEQUENCE, status:VALID,
OPERATOR: SYS.XMLSEQUENCE 12.1.1
owner:SYS, object_type:OPERATOR, object_name:XMLSEQUENCE, status:VALID,
PACKAGE BODY: SYS.DBMS_WRR_INTERNAL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_WRR_INTERNAL, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE, status:VALID,
VIEW: SYS.USER_ADVISOR_TASKS 12.2.2
owner:SYS, object_type:VIEW, object_name:USER_ADVISOR_TASKS, status:VALID,
PACKAGE BODY: SYS.DBMS_WRR_INTERNAL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_WRR_INTERNAL, status:VALID,
TYPE: SYS.SQLSET_ROW 12.2.2
owner:SYS, object_type:TYPE, object_name:SQLSET_ROW, status:VALID,
TYPE: SYS.SQLSET_ROW 12.1.1
owner:SYS, object_type:TYPE, object_name:SQLSET_ROW, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLPA 12.2.2
PACKAGE BODY: SYS.DBMS_SQLPA 12.1.1
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
12.1.1 DBA Data 12.1.1
12.1.1 DBA Data 12.1.1
PACKAGE: SYS.DBMS_SYS_ERROR 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SYS_ERROR, status:VALID,
SYS.PRVT_SQLADV_INFRA dependencies on DBMS_SQLPA 12.1.1