Search Results report_sql_monitor_list

Overview

SYS.DBMS_SQL_MONITOR is an Oracle Database-supplied PL/SQL package that provides the programmatic interface to the SQL Monitoring infrastructure introduced in Oracle 11g. In the context of Oracle E-Business Suite 12.1.1 and 12.2.2, this package serves as the underlying engine that allows applications, concurrent programs, and administrative utilities to capture, register, and report on the execution statistics of long-running SQL statements. The package holds a VALID status in the ETRM repository, confirming that it is installed and compiled in the SYS schema on the database tier that supports the EBS application. Because SQL Monitoring is a database kernel feature rather than an EBS-specific construct, DBMS_SQL_MONITOR does not reside in the APPS schema; it is owned by SYS and is exposed to callers through a PUBLIC synonym, DBMS_SQL_MONITOR, which the EBS metadata confirms as a dependent object.

Key Procedures and Functions

The ETRM metadata documents six callable units within the package, organized as three functional pairs for lifecycle control and report retrieval:

• BEGIN_OPERATION — Marks the beginning of a monitored SQL operation, registering the statement with the SQL Monitoring framework so that runtime statistics can be associated with it.
• END_OPERATION — Signals the completion of a monitored operation, allowing the framework to finalize collection and release the monitoring context.
• REPORT_SQL_MONITOR — Produces a human-readable report of a monitored SQL execution, suitable for review in a text environment such as SQL*Plus.
• REPORT_SQL_MONITOR_XML — Returns the same monitoring data in XML form (XMLType), enabling downstream parsing or display by XML-aware clients.
• REPORT_SQL_MONITOR_LIST — Generates a formatted list of multiple monitored SQL executions, useful for scanning activity across a workload.
• REPORT_SQL_MONITOR_LIST_XML — Provides the list-oriented report in XML format for programmatic consumption.

No parameter signatures are asserted here; callers should consult the Oracle Database PL/SQL Packages and Types Reference for the exact argument lists applicable to their database release.

Tables Accessed

The ETRM excerpt does not enumerate fixed tables or APPS synonyms accessed directly by this package. DBMS_SQL_MONITOR operates against dynamic performance views in the Oracle data dictionary — principally V$SQL_MONITOR and its XML-rendering counterparts such as V$SQL_MONITOR_REPORT. These views are populated by the SQL Monitoring background infrastructure. Because EBS 12.1.1 and 12.2.2 run on the same database kernel, no EBS application tables are read or written; the package's data surface is confined to SYS-owned performance views.

Usage Notes

Within an Oracle EBS environment, DBMS_SQL_MONITOR is typically invoked in three ways. First, database administrators call the REPORT_SQL_MONITOR family interactively to diagnose resource-intensive concurrent programs or report queries identified through the EBS Concurrent Manager. Second, monitoring can be enabled through the database initialization parameter CONTROL_MANAGEMENT_PACK_ACCESS and related SQL Monitoring hints, with this package supplying the reporting layer. Third, custom diagnostic code and OEM/Grid Control integrations may invoke BEGIN_OPERATION, END_OPERATION, and the XML report functions to embed SQL monitoring into bespoke utilities. The ETRM metadata records that the package is referenced by one other package (KUPM$MCP, a SYS-owned internal component), indicating that EBS patches and internal kernel utilities depend on it. Because it is invoked rather than invoked by APPS forms directly, no EBS Forms or concurrent program registration references it as a driver; it remains a database-tier diagnostic facility available to any session with EXECUTE privilege granted on the PUBLIC synonym.