Oracle DBMS_SQL_MONITOR
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Provides information about Real-time SQL and Database Operation Monitoring.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Date Format
DATE_FMT VARCHAR2(21) 'mm/dd/yyyy hh24:mi:ss'
Forced Tracking
FORCE_TRACKING VARCHAR2(30) 'Y'
NO_FORCE_TRACKING VARCHAR2(30) 'N'
Monitor Types
MONITOR_TYPE_SQL NUMBER 1
MONITOR_TYPE_DBOP NUMBER 2
MONITOR_TYPE_ALL NUMBER 3
Report Levels
LEVEL_ALL VARCHAR2(3) 'ALL'
LEVEL_BASIC VARCHAR2(5) 'BASIC'
LEVEL_TYPICAL VARCHAR2(7) 'TYPICAL'
Report Sections
SECTION_ALL VARCHAR2(3) 'ALL'
SECTION_ERRORS VARCHAR2(6) 'ERRORS'
SECTION_FINDINGS VARCHAR2(8) 'FINDINGS'
SECTION_INFORMATION VARCHAR2(11) 'INFORMATION'
SECTION_PLANS VARCHAR2(5) 'PLANS'
SECTION_SUMMARY VARCHAR2(7) 'SUMMARY'
Report Types
TYPE_HTML VARCHAR2(4) 'HTML'
TYPE_TEXT VARCHAR2(4) 'TEXT'
TYPE_XML VARCHAR2(3) 'xML'
Dependencies
CDB_SQL_MONITOR_USAGE DBMS_SQLMON_LIB GV$SQL_MONITOR
DBA_SQL_MONITOR_USAGE DBMS_SQLTUNE GV$SQL_MONITOR_STATNAME
DBMS_DEBUG_JDWP DBMS_SQLTUNE_UTIL1 KUPM$MCP
DBMS_HPROF DBMS_SYS_ERROR XMLTYPE
DBMS_REPORT DUAL  
Documented Yes
Exceptions
Error Code Reason
ORA-13812 Current session does not have database operation monitored
First Available 12.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC. The reporting functionality requires the SELECT_CATALOG_ROLE role.

If anyone can think of a reason why EXECUTE should be granted to PUBLIC rather than a privileged account I would appreciate an email with that explanation.
Source {ORACLE_HOME}/rdbms/admin/dbmssqlm.sql
Subprograms
 
BEGIN_OPERATION
Starts a composite database operation in the current session 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)
RETURN NUMBER;
DECLARE
 n NUMBER;
BEGIN
  n := dbms_sql_monitor.begin_operation('UWMonOps', attribute_list => 'table_name = sys.zzyzx, operation=load');
  dbms_output.put_line(TO_CHAR(n));
END;
/
1

PL/SQL procedure successfully completed.


SELECT UNIQUE dbop_name
FROM gv$sql_monitor;

DBOP_NAME
------------------------------
UWMonOps


CREATE TABLE zzyzx AS
SELECT * FROM dba_objects
WHERE 1=2;

Table created.

INSERT INTO sys.zzyzx SELECT * FROM dba_objects;

78005 rows created.

COMMIT;

Commit complete.

INSERT /*+APPEND*/ INTO sys.zzyzx SELECT * FROM dba_objects;

78005 rows created.


COMMIT;

Commit complete.

DECLARE
 x XMLTYPE;
BEGIN
  x := dbms_sql_monitor.report_sql_monitor_xml(dbop_name=>'UWMonOps');
  dbms_output.put_line(x.getCLOBVal());
END;
/

-- xml manually formatted
<report db_version="21.0.0.0.0" elapsed_time="1.93" cpu_time="1.86" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2"
service_type="0">
  <report_id><![CDATA[/orarep/sqlmonitor/main%3fdbop_name%3dUWMonOps]]></report_id>
  <sql_monitor_report version="4.0" sysdate="04/27/2021 00:47:14">
  <report_parameters>
    <dbop_name>UWMonOps</dbop_name>
    <bucket_count>69</bucket_count>
    <interval_start>04/27/2021 00:42:43</interval_start>
    <interval_end>04/27/2021 00:47:16</interval_end>
  </report_parameters>
  <target instance_id="1" session_id="222" session_serial="6946" dbop_name="UWMonOps"
    dbop_exec_id="1" dbop_exec_start="04/27/2021 00:42:43"
    db_unique_name="test21db_iad25g" db_platform_name="x86_64/Linux 2.4.xx"
    report_host_name="test21">
    <user_id>0</user_id>
    <user>SYS</user>
    <con_id>1</con_id>
    <con_name>CDB$ROOT</con_name>
    <program>sqlplus@test21 (TNSV1-V3)</program>
    <module>DBMS_SQL_TRANSLATOR_EXPORT</module>
    <action>GET_CONFIG_FILTER_NUM_AND_LEN</action>
    <service>SYS$USERS</service>
    <plsql_entry_object_id>15751</plsql_entry_object_id>
    <plsql_entry_subprogram_id>4</plsql_entry_subprogram_id>
    <plsql_entry_name>SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_XML</plsql_entry_name>
    <plsql_object_id>14789</plsql_object_id>
    <plsql_subprogram_id>158</plsql_subprogram_id>
    <plsql_name>Unavailable</plsql_name>
    <status>EXECUTING</status>
    <refresh_count>0</refresh_count>
    <first_refresh_time>04/27/2021 00:42:43</first_refresh_time>
    <last_refresh_time>04/27/2021 00:47:14</last_refresh_time>
    <duration>272</duration></target>
    <stats type="monitor">
      <statname="elapsed_time">4695272</stat>
      <stat name="cpu_time">2698187</stat>
      <stat name="user_io_wait_time">209779</stat>
      <stat name="application_wait_time">114685</stat>
      <stat name="concurrency_wait_time">51</stat>
      <stat name="other_wait_time">1672570</stat>
      <stat name="buffer_gets">3252</stat><stat name="disk_reads">84</stat>
      <stat name="direct_writes">58</stat><stat name="read_reqs">84</stat>
      <stat name="read_bytes">929792</stat><stat name="write_reqs">58</stat>
      <stat name="write_bytes">47333376</stat>
    </stats>
    <activity_sampled>
      <activity class="Other SQL Execution" event="sql_id: 38uq36jhk63hj">1</activity>
      <activity class="Other SQL Execution" event="sql_id: 60kg2qh1agn3p">1</activity>
      <activity class="Other SQL Execution" event="sql_id: ds3dfgk6y7b91">3</activity>
      <activity class="Other SQL Execution" event="sql_id: f7jdr4y8vfhhk">1</activity>
    </activity_sampled>
    <activity_detail start_time="04/27/2021 00:42:43" end_time="04/27/2021 00:47:16"
      first_sample_time="04/27/2021 00:45:09" last_sample_time="04/27/2021 00:47:15"
      duration="127" sample_interval="1" bucket_interval="4" bucket_count="69"
      bucket_duration="274" cpu_cores="1" total_cpu_cores="1" hyperthread="Y">
      <bucket number="37">
        <activity sql="ds3dfgk6y7b91" other_sql_class="Other" dbop_name="UWMonOps"
        rt="2">1</activity><activity sql="ds3dfgk6y7b91" other_sql_class="System I/O"
        dbop_name="UWMonOps" rt="2">1</activity>
      </bucket>
      <bucket number="38"><activity sql="f7jdr4y8vfhhk" other_sql_class="Cpu"
        dbop_name="UWMonOps">1</activity>
      </bucket>
      <bucket number="43">
        <activity sql="38uq36jhk63hj" other_sql_class="Application"
        dbop_name="UWMonOps">1</activity>
      </bucket>
      <bucket number="47">
    <activity sql="ds3dfgk6y7b91" other_sql_class="Cpu" dbop_name="UWMonOps">1
    </activity>
    </bucket>
    <bucket number="69">
      <activity sql="60kg2qh1agn3p" other_sql_class="Cpu" top_sql_id="2575qdsqsnz83"
      dbop_name="UWMonOps">1</activity>
    </bucket>
    </activity_detail>
    <plan_monitor/>
      <stattype name="metrics" cpu_cores="1" hyperthread="Y">
      <stat_info>
        <stat id="1" name="nb_cpu"/>
        <stat id="2" name="nb_sess"/>
        <stat id="3" name="reads" unit="per_sec"/>
        <stat id="4" name="writes" unit="per_sec"/>
        <stat id="5" name="read_kb" unit="bytes_per_sec" factor="1024"/>
        <stat id="6" name="write_kb" unit="bytes_per_sec" factor="1024"/>
        <stat id="7" name="interc_kb" unit="bytes_per_sec" factor="1024"/>
        <stat id="8" name="cache_kb" unit="bytes_per_sec" factor="1024"/>
        <stat id="9" name="pga_kb" unit="bytes" factor="1024"/>
        <stat id="10" name="tmp_kb" unit="bytes" factor="1024"/>
      </stat_info>
      <buckets bucket_interval="4" bucket_count="69" start_time="04/27/2021 00:42:43"
        end_time="04/27/2021 00:47:16" duration="274">
        <bucket bucket_id="1">
          <stat id="1" value=".01"/>
          <stat id="5" value="4"/>
          <stat id="7" value="4"/>
          <stat id="8" value="2164"/><stat id="9" value="29968"/></bucket>
        </bucket>
...
          <stat id="7" value="62"/>
          <stat id="8" value="5008"/>
          <stat id="9"value="19805"/>
        </bucket>
      </buckets>
    </stattype>
  </sql_monitor_report>
</report>

PL/SQL procedure successfully completed.


BEGIN
  dbms_sql_monitor.end_operation('UWMonOps', 1);
END;
/

PL/SQL procedure successfully completed.

SELECT UNIQUE dbop_name
FROM gv$sql_monitor;
 
END_OPERATION
Called to end the operation in the current session. If there is no operation, this will be NO-OP. dbms_sql_monitor.end_operation(
dbop_name IN VARCHAR2,
dbop_eid  IN NUMBER);
See BEGIN OPERATION Demo Above
 
REPORT_SQL_MONITOR
Builds a detailed report for a specific database operation that has been monitored by Oracle 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;
DECLARE
 c CLOB;
BEGIN
  c := dbms_sql_monitor.report_sql_monitor(dbop_name=>'UWMonOps');
  dbms_output.put_line(c);
END;
/

SQL Monitoring Report

Global Information
------------------------------
Status                                : EXECUTING

Instance ID                           : 1
Session                               : SYS (13:117)
DBOP Name                             : UWMonOps
DBOP Execution ID                     : 1
First Refresh Time                    : 04/10/2021 21:47:33
Last Refresh Time                     : 04/10/2021 21:48:20
Duration : 47s

Module/Action                         : sqlplus.exe/-
Service                               : SYS$USERS
Program                               : sqlplus.exe
PLSQL Entry Ids (Object/Subprogram)   : 10702,3
PLSQL Current Ids (Object/Subprogram) : 10544,61


Global Stats
=============================================================================
|Elapsed|CPU    |IO      |Application|Other   |Buffer|Read|Read |Write|Write|
|Time(s)|Time(s)|Waits(s)|Waits(s)   |Waits(s)|Gets  |Reqs|Bytes|Reqs |Bytes|
=============================================================================
|  3.38 |  2.98 |   0.09 |      0.00 |   0.32 | 8050 | 234| 2MB |  32 | 40MB|
=============================================================================

-- which when formatted correctly becomes:
=============================================================================
|Elapsed|CPU    |IO      |Application|Other   |Buffer|Read|Read |Write|Write|
|Time(s)|Time(s)|Waits(s)|Waits(s)   |Waits(s)|Gets  |Reqs|Bytes|Reqs |Bytes|
=============================================================================
|  3.38 |  2.98 |   0.09 |      0.00 |   0.32 | 8050 | 234| 2MB |  32 | 40MB|
=============================================================================

PL/SQL procedure successfully completed.
 
REPORT_SQL_MONITOR_LIST
Builds a report for all or a subset of database operations that have been monitored by Oracle 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;
DECLARE
 c CLOB;
BEGIN
  c := dbms_sql_monitor.report_sql_monitor_list(dbop_name => 'UWMonOps');
  dbms_output.put_line(c);
END;
/
 
REPORT_SQL_MONITOR_LIST_XML
Builds a report for all or a subset of database operations that have been monitored by Oracle in XML dbms_sql_monitor.report_sql_monitor_list_xml(
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,
con_name          IN VARCHAR2 DEFAULT NULL)
RETURN XMLTYPE;
DECLARE
 x XMLTYPE;
BEGIN
  x := dbms_sql_monitor.report_sql_monitor_list_xml('UWMonOps');
  dbms_output.put_line(x.getCLOBVal());
END;
/
 
REPORT_SQL_MONITOR_XML
Builds a detailed report for a specific database operation that has been monitored by Oracle in XML dbms_sql_monitor.report_sql_monitor_xml(
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',
auto_refresh        IN NUMBER   DEFAULT NULL,
sql_plan_hash_value IN NUMBER   DEFAULT NULL,
con_name            IN VARCHAR2 DEFAULT NULL)
RETURN XMLTYPE;
See BEGIN_OPERATIONS Demo Above

Related Topics
Built-in Functions
Built-in Packages
DBMS_REPORT
DBMS_SQLTUNE
XMLTYPE
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx