Oracle DBMS_SQL_MONITOR
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_SQLTUNE GV$SQL_MONITOR_STATNAME
DBA_SQL_MONITOR_USAGE DBMS_SQLTUNE_UTIL1 KUPM$MCP
DBMS_REPORT DBMS_SYS_ERROR XMLTYPE
DBMS_SQLMON_LIB GV$SQL_MONITOR  
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/2013 21:58:20">
    <report_parameters>
      <dbop_name>UWMonOps</dbop_name>
      <bucket_count>82</bucket_count>
      <interval_start>12/10/2013 21:47:33</interval_start>
      <interval_end>12/10/2013 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/2013 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/2013 21:47:33</first_refresh_time>
  <last_refresh_time>12/10/2013 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/2013 21:47:33" end_time="12/10/2013 21:58:21"
first_sample_time="12/10/2013 21:47:41" last_sample_time="12/10/2013 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/2013 21:47:33"
       end_time="12/10/2013 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
Ends the monitoring operation in the current session

The docs are wrong on this: Eric informed 17 Dec 2013
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/2013 21:47:33
Last Refresh Time                     : 12/10/2013 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
DBMS_REPORT
DBMS_SQLTUNE
Packages
XMLTYPE

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