Search Results dbms_monitor

Overview

SYS.DBMS_MONITOR is a database-resident PL/SQL package supplied by Oracle that exposes server-side instrumentation and diagnostic services to the Oracle E-Business Suite environment. It runs with SYS privileges and is licensed under the Oracle RDBMS rather than the EBS application schema, which means it is available to every EBS 12.1.1 and 12.2.2 environment that sits on a supported database release. The package provides two principal areas of functionality: statistics aggregation and SQL tracing. Statistics aggregation allows the database to collect performance metrics scoped to a specific client identifier or to a service/module/action combination, independent of the general instance-level statistics. Tracing functionality allows a DBA or developer to enable extended SQL trace output for all sessions associated with a given client identifier, service/module/action combination, a single session, or the entire database instance. In an EBS context, this package is the mechanism by which targeted end-user or concurrent-program performance investigation is performed without enabling instance-wide tracing, which would generate excessive trace volume and I/O.

Key Procedures and Functions

The ETRM metadata documents twelve procedures grouped into three families.

Statistics aggregation procedures: CLIENT_ID_STAT_ENABLE and CLIENT_ID_STAT_DISABLE enable and disable statistics aggregation for a specified client identifier. SERV_MOD_ACT_STAT_ENABLE and SERV_MOD_ACT_STAT_DISABLE perform the equivalent action for a service, module, and optional action combination, allowing statistics to be gathered for a precise workload signature rather than a whole instance.
Trace-by-identifier procedures: CLIENT_ID_TRACE_ENABLE and CLIENT_ID_TRACE_DISABLE toggle SQL trace for all sessions carrying a given client identifier. SERV_MOD_ACT_TRACE_ENABLE and SERV_MOD_ACT_TRACE_DISABLE toggle trace for sessions matching a service/module/action combination, which is the typical means of tracing a specific EBS form or concurrent program workload.
Trace-scope procedures: SESSION_TRACE_ENABLE and SESSION_TRACE_DISABLE control trace at the individual session level, addressing a single database session by identifier and serial number. DATABASE_TRACE_ENABLE and DATABASE_TRACE_DISABLE control trace for the entire database instance and are reserved for narrowly scoped diagnostic windows because of their impact.

Constants ALL_MODULES and ALL_ACTIONS are provided so callers may indicate that a given module or action dimension should match all values.

Tables Accessed

DBMS_MONITOR does not read or write application tables. It operates against RDBMS-internal instrumentation structures and data dictionary views, including the dynamic performance and trace configuration views that hold the persisted client identifier, service, module, and action tracing and aggregation settings. The ETRM metadata lists no referenced tables via APPS synonyms, and the package is not referenced by any other documented EBS package. This confirms that DBMS_MONITOR is a diagnostic facility rather than a component of the EBS application data model; its effect is to modify session-level instrumentation, not business data.

Usage Notes

DBMS_MONITOR is generally invoked from SQL*Plus or another direct database client by a DBA or an application support analyst, rather than from EBS forms or concurrent programs. The SESSION_TRACE_ENABLE procedure is the specific entry point most commonly sought when investigating a misbehaving EBS session, since it can be called using the SID and serial number obtained from a query against the session views. In EBS 12.1.1 and 12.2.2, the most frequent usage patterns are tracing a named concurrent program by its service/module/action attributes, tracing a specific user by client identifier where the application sets it, and tracing a single session identified through the EBS session administration forms. Administrators should note that enabling database-level trace has significant file-generation and I/O consequences and should be time-boxed. Disabling trace with the corresponding procedure is equally important to prevent continued trace file accumulation. Because the package executes with SYS privileges, custom code should invoke it under controlled administrative accounts and not from within application code paths.

PACKAGE: SYS.DBMS_MONITOR 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_MONITOR, status:VALID,
PACKAGE: SYS.DBMS_MONITOR 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_MONITOR, status:VALID,
SYNONYM: PUBLIC.DBMS_MONITOR 12.1.1
owner:PUBLIC, object_type:SYNONYM, object_name:DBMS_MONITOR, status:VALID,
PACKAGE BODY: SYS.DBMS_MONITOR 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_MONITOR, status:VALID,
SYNONYM: PUBLIC.DBMS_MONITOR 12.2.2
owner:PUBLIC, object_type:SYNONYM, object_name:DBMS_MONITOR, status:VALID,
PACKAGE BODY: SYS.DBMS_MONITOR 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_MONITOR, status:VALID,
LIBRARY: SYS.DBMS_MONITOR_LIB 12.1.1
owner:SYS, object_type:LIBRARY, object_name:DBMS_MONITOR_LIB, status:VALID,
LIBRARY: SYS.DBMS_MONITOR_LIB 12.2.2
owner:SYS, object_type:LIBRARY, object_name:DBMS_MONITOR_LIB, status:VALID,
PACKAGE BODY: SYS.DBMS_MONITOR 12.2.2
PACKAGE BODY: SYS.DBMS_MONITOR 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_MONITOR 12.2.2
PACKAGE: SYS.DBMS_MONITOR 12.1.1
SYS.DBMS_MONITOR dependencies on DBMS_MONITOR 12.2.2
PUBLIC.DBMS_MONITOR dependencies on DBMS_MONITOR 12.2.2
SYS.DBMS_MONITOR dependencies on DBMS_MONITOR 12.1.1
PUBLIC.DBMS_MONITOR dependencies on DBMS_MONITOR 12.1.1
PACKAGE: SYS.STANDARD 12.1.1
owner:SYS, object_type:PACKAGE, object_name:STANDARD, status:VALID,