Oracle | DBMS_SQL

Oracle DBMS_SQL_MONITOR Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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	REPORT_SQL_MONITOR	REPORT_SQL_MONITOR_LIST_XML
END_OPERATION	REPORT_SQL_MONITOR_LIST	REPORT_SQL_MONITOR_XML

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