157 DBMS_SQL_MONITOR
The DBMS_SQL_MONITOR
package provides information about Real-Time SQL Monitoring and Real-Time Database Operation Monitoring.
This chapter contains the following topics:
See Also:
157.1 DBMS_SQL_MONITOR Overview
The DBMS_SQL_MONITOR
package provides information about Real-Time SQL Monitoring and Real-Time Database Operation Monitoring.
These features provide automatic monitoring of SQL statements, PL/SQL blocks, or composite database operations that are considered high-cost. A simple database operation is a single SQL statement or PL/SQL procedure or function. A composite database operation is activity between two defined points in time in a database session. The monitored data is collected in the V$SQL_MONITOR
and V$SQL_PLAN_MONITOR
views.
The following subprograms begin and end monitoring of a composite database operation:
The following subprograms report on monitoring data collected in V$SQL_MONITOR
and V$SQL_PLAN_MONITOR
:
157.2 DBMS_SQL_MONITOR Security Model
This package is available to PUBLIC
and executes with invoker's rights privileges. The reporting functions require privileges to select data from the catalog as provided by the role SELECT_CATALOG_ROLE
.
157.3 DBMS_SQL_MONITOR Constants
The DBMS_SQL_MONITOR
package uses the constants shown in the following table.
Table 157-1 DBMS_SQL_MONITOR Constants
Constant | Type | Value | Description |
---|---|---|---|
|
|
|
Force track the composite database operation when the operation starts |
|
|
|
Do not force track the composite database operation when the operation starts. It is only tracked when it has consumed 5 seconds of CPU or I/O time. |
157.4 Summary of DBMS_SQL_MONITOR Subprograms
This table lists and describes the DBMS_SQL_MONITOR
package subprograms.
Table 157-2 DBMS_SQL_MONITOR Package Subprograms
Subprogram | Description |
---|---|
This function starts a database operation in the current session. |
|
This function ends a database operation in the current session. If the specified database operation does not exist, then this function has no effect. |
|
This function builds a detailed report with monitoring information for a SQL statement, PL/SQL block, or database operation. |
|
This function is identical to the |
|
This function builds a report for all or a subset of database operations that have been monitored by Oracle Database. |
|
This function is identical to the |
157.4.1 BEGIN_OPERATION Function
This function starts a database operation in the current session.
Syntax
DBMS_SQL_MONITOR.BEGIN_OPERATION (
dbop_name IN VARCHAR2,
dbop_eid IN NUMBER := NULL,
forced_tracking IN VARCHAR2 := NO_FORCE_TRACKING,
attribute_list IN VARCHAR2 := NULL,
session_id IN NUMBER := NULL,
session_serial IN NUMBER := NULL)
iRETURN NUMBER;
Parameters
Table 157-3 BEGIN_OPERATION Procedure Parameters
Parameter | Description |
---|---|
|
Name for the composite database operation. |
|
Unique identifier for the current execution of the composite database operation. |
|
Whether tracking is forced. Possible values are:
See "DBMS_SQL_MONITOR Constants". |
|
List of user-created attributes. It is a comma-separated list of name-value pairs (for example, |
session_id |
Session ID of the session to be monitored. If omitted (or null), then the database monitors the current session. |
session_serial |
Serial number of the session to be monitored. If omitted (or null), then the database uses only the session ID to determine the session. |
Return Values
This function returns the database operation execution ID. If the value is null for dbop_eid
, then the database generates a unique value.
157.4.2 END_OPERATION Procedure
This function ends a database operation in the current session. If the specified database operation does not exist, then this function has no effect.
Syntax
DBMS_SQL_MONITOR.END_OPERATION(
dbop_name IN VARCHAR2,
dbop_eid IN NUMBER)
RETURN NUMBER;
Parameters
Table 157-4 END_OPERATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of a composite database operation |
|
Unique identifier for the current execution of the composite database operation |
157.4.3 REPORT_SQL_MONITOR Function
This function builds a detailed report with monitoring information for a SQL statement, PL/SQL block, or database operation.
For each operation, it gives key information and associated global statistics. Use this function to get detailed monitoring information for a database operation.
The target database operation for this report can be:
-
The last database operation monitored by Oracle Database (default, no parameter).
-
The last database operation executed in the specified session and monitored by Oracle Database. The session is identified by its session ID and optionally its serial number (
-1
is current session). -
The last execution of a specific database operation identified by its
sql_id
. -
A specific execution of a database operation identified by the combination
sql_id
,sql_exec_start
, andsql_exec_id
. -
The last execution of a specific database operation identified by
dbop_name
. -
The specific execution of a database operation identified by the combination
dbop_name
,dbop_exec_id
.
Syntax
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR ( sql_id IN VARCHAR2 DEFAULT NULL, dbop_name IN VARCHAR2 DEFAULT NULL, dbop_exec_id IN NUMBER DEFAULT NULL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, sql_exec_start IN DATE DEFAULT NULL, sql_exec_id IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, start_time_filter IN DATE DEFAULT NULL, end_time_filter IN DATE DEFAULT NULL, instance_id_filter IN NUMBER DEFAULT NULL, parallel_filter IN VARCHAR2 DEFAULT NULL, plan_line_filter IN NUMBER DEFAULT NULL, event_detail IN VARCHAR2 DEFAULT 'YES', bucket_max_count IN NUMBER DEFAULT 128, bucket_interval IN NUMBER DEFAULT NULL, base_path IN VARCHAR2 DEFAULT NULL, last_refresh_time IN DATE DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', type IN VARCHAR2 DEFAULT 'TEXT', sql_plan_hash_value IN NUMBER DEFAULT NULL, con_name IN VARCHAR2 DEFAULT NULL) RETURN CLOB;
Parameters
Table 157-5 REPORT_SQL_MONITOR Procedure Parameters
Parameter | Description |
---|---|
|
|
|
|
|
Execution ID for the composite database operation for which monitoring information is displayed |
|
Targets only the subset of statements executed and monitored on behalf of the specified session. Default is |
|
In addition to |
|
Time at which execution of the monitored SQL was started. Only applicable when |
|
A numeric ID generated internally by SQL monitor to identify different executions of the same SQL statement. Thus each execution will have the same |
|
Looks only at queries started on the specified instance. Use |
|
If not |
|
If not |
|
Only looks at activity for the specified instance. Use |
|
Parallel filter applies only to parallel execution and allows you to select only a subset of the processes involved in the parallel execution. The string
|
|
Selects activity and execution statistics for the specified line number in the plan of a SQL. |
|
When set to |
|
Specifies the maximum number of buckets to create in the report |
|
Represents the exact time interval, in seconds, of all histogram buckets. If specified, |
|
URL path for flex HTML resources since flex HTML format requires access to external files (Java scripts and the flash |
|
If not
|
|
Level of detail for the report. Of the following, only one can be specified:
In addition, individual report sections can also be enabled or disabled by using a
In addition, SQL text can be specified at different levels:
|
|
Report type:
|
|
Targets only those with the specified plan hash value. Default is |
|
Container name in a multitenant database. |
Return Values
SQL monitor report, an XML document.
Usage Notes
The user invoking this function must have privilege to access the following fixed views:
-
GV$SQL_MONITOR
-
GV$SQL_PLAN_MONITOR
-
GV$ACTIVE_SESSION_HISTORY
-
GV$SESSION_LONGOPS
-
GV$SQL
if SQL full text is requested and its length is greater than 2 KB
157.4.4 REPORT_SQL_MONITOR_XML Function
This function is identical to the REPORT_SQL_MONITOR
function, except that the return type is XMLType
.
Related Topics
157.4.5 REPORT_SQL_MONITOR_LIST Function
This function builds a report for all or a subset of database operations that have been monitored by Oracle Database.
For each database operation, it gives key information and associated global statistics.
Syntax
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST (
sql_id IN VARCHAR2 DEFAULT NULL,
dbop_name IN VARCHAR2 DEFAULT NULL,
monitor_type IN NUMBER DEFAULT MONITOR_TYPE_ALL,
session_id IN NUMBER DEFAULT NULL,
session_serial IN NUMBER DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
active_since_date IN DATE DEFAULT NULL,
active_since_sec IN NUMBER DEFAULT NULL,
last_refresh_time IN DATE DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT 'TYPICAL',
auto_refresh IN NUMBER DEFAULT NULL,
base_path IN VARCHAR2 DEFAULT NULL,
type IN VARCHAR2 DEFAULT 'TEXT',
con_name IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
Parameters
Table 157-6 REPORT_SQL_MONITOR_LIST Procedure Parameters
Parameter | Description |
---|---|
|
|
|
|
|
Monitor type:
|
|
Targets only the subset of database operations executed and monitored on behalf of the specified session. Default is |
|
In addition to |
|
Looks only at monitored database operations originating from the specified instance. Use |
|
If not |
|
If not |
|
If not |
|
Level of detail for the report. The level can be |
|
Specifies the duration in seconds after which report data will be automatically refreshed while the monitored SQL or database operation is still executing. This applies to active report types. |
|
URL path for flex HTML resources since flex HTML format requires access to external files (java scripts and the flash |
|
Report type:
|
|
Container name in a multitenant database. |
Return Values
A report in text, XML, or HTML format that contains the list of the database operations monitored.
Usage Notes
-
Use the REPORT_SQL_MONITOR Function to get detailed monitoring information for a single database operation.
-
The user invoking this function needs to have the privilege to access the fixed views
GV$SQL_MONITOR
andGV$SQL
.