Oracle | DBMS_SQL

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	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