Oracle DBMS_SQL_MONITOR
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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_HPROF DBMS_SQLTUNE_UTIL1 KUPM$MCP
DBMS_REPORT DBMS_SYS_ERROR XMLTYPE
Documented Yes
Exceptions
Error Code Reason
ORA-13812 Current session does not have database operation monitored
First Available 12.1.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC. The reporting functionality requires the SELECT_CATALOG_ROLE role
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)
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;
/

SELECT UNIQUE dbop_name
FROM gv$sql_monitor;

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

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="12.1.0.1.0" cpu_cores="2" hyperthread="Y"
 con_id="1"  con_name="CDB$ROOT" timezone_offset="-28800"
 elapsed_time="0.08" cpu_time="0.05" packs="2">
  <report_id>
    <![CDATA[/orarep/sqlmonitor/main?dbop_name=UWMonOps]]>
  </report_id>
  <sql_monitor_report version="4.0" sysdate="12/10/2016 21:58:20">
    <report_parameters>
      <dbop_name>UWMonOps</dbop_name>
      <bucket_count>82</bucket_count>
      <interval_start>12/10/2016 21:47:33</interval_start>
      <interval_end>12/10/2016 21:58:21</interval_end>
    </report_parameters>
    <target instance_id="1" session_id="13" session_serial="117"
     dbop_name="UWMonOps" dbop_exec_id="2" dbop_exec_start="12/10/2016 21:47:33"
     db_unique_name="orabase2" db_platform_name="Microsoft Windows x86 64-bit"
     report_host_name="PERRITO4">
  <user_id>0</user_id>
  <user>SYS</user>
  <con_id>1</con_id>
  <con_name>CDB$ROOT</con_name>
  <program>sqlplus.exe</program>
  <module>sqlplus.exe</module>
  <service>SYS$USERS</service>
  <plsql_entry_object_id>10702</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>10544</plsql_object_id>
  <plsql_subprogram_id>61</plsql_subprogram_id>
  <plsql_name>SYS.DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML</plsql_name>
  <status>EXECUTING</status>
  <refresh_count>0</refresh_count>
  <first_refresh_time>12/10/2016 21:47:33</first_refresh_time>
  <last_refresh_time>12/10/2016 21:58:20</last_refresh_time>
  <duration>647</duration>
  <optimizer_env type="sys">
    <param name="active_instance_count">1</param>
    <param name="is_recur_flags">0</param>
    <param name="parallel_autodop">0</param>
    <param name="parallel_ddl_mode">enabled</param>
    <param name="parallel_ddldml">0</param>
    <param name="parallel_degree">0</param>
    <param name="parallel_dml_mode">disabled</param>
    <param name="parallel_execution_enabled">true</param>
    <param name="parallel_max_degree">8</param>
    <param name="parallel_query_default_dop">0</param>
    <param name="parallel_query_mode">enabled</param>
    <param name="pga_aggregate_target">819200 KB</param>
    <param name="total_cpu_count">4</param>
  </optimizer_env></target>
  <stats type="monitor">
    <stat name="elapsed_time">1355796</stat>
    <stat name="cpu_time">1326008</stat>
    <stat name="user_io_wait_time">29229</stat>
    <stat name="application_wait_time">559</stat>
    <stat name="buffer_gets">1618</stat>
    <stat name="disk_reads">39</stat>
    <stat name="direct_writes">18446744073709551603</stat>
    <stat name="read_reqs">39</stat>
    <stat name="read_bytes">663552</stat>
    <stat name="write_reqs">18446744073709551603</stat>
    <stat name="write_bytes">12689408</stat>
  </stats>
  <activity_detail start_time="12/10/2016 21:47:33" end_time="12/10/2016 21:58:21"
first_sample_time="12/10/2016 21:47:41" last_sample_time="12/10/2016 21:47:41"
duration="1" sample_interval="1" bucket_interval="8" bucket_count="82"
bucket_duration="649" cpu_cores="2" total_cpu_cores="2" hyperthread="Y"><bucket
number="2">
   <activity sql="77u1typwj1h7k" other_sql_class="Cpu" dbop_name="UWMonOps">1</activity>
</bucket>
  </activity_detail>
  <plan_monitor/>
    <stat type name="metrics" cpu_cores="2" 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="8" bucket_count="82" start_time="12/10/2016 21:47:33"
       end_time="12/10/2016 21:58:21" duration="649">
        <bucket bucket_id="1">
          <stat id="1" value=".17"/>
          <stat id="3" value="7"/>
          <stat id="4" value="6"/>
          <stat id="5" value="104"/>
          <stat id="6" value="3980"/>
          <stat id="7" value="4084"/>
          <stat id="8" value="17297"/>
          <stat id="9" value="12775"/>
        </bucket>
        <bucket bucket_id="2">
          <stat id="1" value=".01"/>
          <stat id="5" value="4"/>
          <stat id="6" value="137"/>
          <stat id="7" value="141"/>
          <stat id="8" value="597"/>
          <stat id="9" value="441"/>
        </bucket>
      </buckets>
    </stattype>
  </sql_monitor_report>
</report>

PL/SQL procedure successfully completed.

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

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                    : 12/10/2016 21:47:33
Last Refresh Time                     : 12/10/2016 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 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved