DBMS_SQL_MONITOR DBMS_SQLTUNE

DBMS_SQL_MONITOR

The DBMS_SQL_MONITOR package provides information about Real-time SQL Monitoring and Real-time Database Operation Monitoring

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 expensive

BEGIN_OPERATION Function

This function starts a composite 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)

  RETURN NUMBER;

END_OPERATION Procedure

This function ends a composite database operation in the current session. If the specified composite database operation does not exist, this procedure has no effect.

Syntax

DBMS_SQL_MONITOR.END_OPERATION  (

   dbop_name       IN VARCHAR2,

   dbop_eid        IN NUMBER)

  RETURN NUMBER;

REPORT_SQL_MONITOR Function

This function builds a detailed report with monitoring information for a simple or a composite database operation.

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;

REPORT_SQL_MONITOR_LIST Function

This function builds a report for all or a subset of database operations that have been monitored by Oracle. For each database operation, it gives key information and associated global statistics.

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; 

DBMS_SQLTUNE

The DBMS_SQLTUNE package is the interface for tuning SQL on demand

https://docs.oracle.com/database/122/ARPLS/DBMS_SQLTUNE.htm#ARPLS68376