Parsing RTSM(Real-Time SQL Monitor) XML Reports

Posted on by Posted in oracle, RTSM 31 Page views

In the previous part, I forgot to mention one important detail: if you want to export or extract RTSM (Real-Time SQL Monitoring) reports directly in XML format for further analysis, you can simply use the following functions:

  • dbms_sql_monitor.report_sql_monitor_xml() or
    dbms_sqltune.report_sql_monitor_xml() — for reports still present in gv$sql_monitor.
  • dbms_auto_report.report_repository_detail_xml() — for reports already stored in history (AWR), under dba_hist_reports.

To format these XML reports into TEXT, HTML, or Active HTML, you can use:

dbms_report.format_report(
    report      IN xmltype,
    format_name IN varchar2
)

Setting format_name => 'ACTIVE' produces the Active HTML version.

The Main Topic: How to Parse RTSM XML Reports

Starting with Oracle 19.16, table SQL_MACROs became available, enabling a very elegant way to encapsulate XML parsing logic inside SQL macros.

For convenience, I updated the package PKG_RTSM and added the following SQL macro functions to parse various sections of an RTSM XML report:

function rtsm_xml_macro_report_info(xmldata xmltype) return varchar2 SQL_MACRO;
function rtsm_xml_macro_plan_info(xmldata xmltype) return varchar2 SQL_MACRO;
function rtsm_xml_macro_plan_ops(xmldata xmltype) return varchar2 SQL_MACRO;
function rtsm_xml_macro_plan_monitor(xmldata xmltype) return varchar2 SQL_MACRO;

(These declarations can be found in the package header in the uploaded source file pkg_rtsm.)

This means you can use them directly in SQL, for example:

select *
from pkg_rtsm.rtsm_xml_macro_plan_ops(:YOUR_XML_REPORT) ops;

Overview of the SQL Macro Functions

1. rtsm_xml_macro_report_info

Returns the main metadata of the RTSM report, such as:

  • sql_id, sql_exec_id, sql_exec_start
  • rep_date, inst_count, cpu_cores
  • con_name, platform information, optimizer environment
  • SQL text, execution statistics, activity samples, and more

In addition, the function exposes two extremely useful columns:

DBMS_REPORT.FORMAT_REPORT(XMLDATA,'TEXT'  )  as RTSM_REPORT_TEXT
DBMS_REPORT.FORMAT_REPORT(XMLDATA,'ACTIVE') as RTSM_REPORT_ACTIVE

These allow you to obtain the formatted TEXT or Active HTML version of the report directly from SQL without extra steps.

This logic is fully visible in the macro implementation in the package body.

2. rtsm_xml_macro_plan_info

Returns essential information about the execution plan, including:

  • has_user_tab
  • db_version
  • parse_schema
  • Full and partial plan hash values (plan_hash_full, plan_hash, plan_hash_2)
  • peeked_binds
  • xplan_stats
  • qb_registry
  • outline_data
  • hint_usage

This macro extracts the <other_xml> block attached to the root plan operation (id="1").

3. rtsm_xml_macro_plan_ops

Returns the full list of plan operations, including:

  • operation id, name, options, depth, position
  • object information
  • cardinality, bytes, cost
  • I/O and CPU cost
  • access and filter predicates

This essentially exposes the plan as a SQL-friendly dataset.

4. rtsm_xml_macro_plan_monitor

This is the most important macro for analyzing performance metrics.
It returns all operations from the plan monitor section, together with all runtime statistics, including:

  • starts, cardinality, memory usage, temp usage
  • I/O operations and spilled data
  • CPU and I/O optimizer estimates
  • Monitoring timestamps (first_active, last_active)
  • Activity samples by class (CPU, User I/O, Cluster, etc.)

Most importantly, it computes:

ROUND(100 * RATIO_TO_REPORT(NVL(wait_samples_total,0)) OVER (), 3)
    AS TIME_SPENT_PERCENTAGE

This is an analogue of “Activity%” in the Active HTML report — showing what percentage of sampled activity belongs to each plan step.

The full implementation, with all xmltable parsing logic, is available in the uploaded code file pkg_rtsm.

Getting the Updated Code

You can download the latest version of the package here:

https://github.com/xtender/xt_scripts/blob/master/rtsm/parsing/pkg_rtsm.sql

Page views: 31