Category Archives: RTSM

Parsing RTSM(Real-Time SQL Monitor) XML Reports

Posted on by Posted in oracle, RTSM 59 Page views Leave a comment

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

Parsing Real-Time SQL Monitor (RTSM) ACTIVE Reports Stored as HTML

Posted on by Posted in java, oracle, RTSM, SQL, statistics, troubleshooting 223 Page views Leave a comment

When you work with a large number of Real-Time SQL Monitor (RTSM) reports in the ACTIVE format (the interactive HTML report with JavaScript), it quickly becomes inconvenient to open them one by one in a browser. Very often you want to load them into the database, store them, index them, and analyze them in bulk.

Some RTSM reports are easy to process — for example, those exported directly from EM often contain a plain XML payload that can be extracted and parsed with XMLTABLE().

But most ACTIVE reports do not store XML directly.
Instead, they embed a base64-encoded and zlib-compressed XML document inside a <report> element.
These reports typically look like this:

<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
  <script ...>
     var version = "19.0.0.0.0";
     ...
  </script>
 </head>
 <body onload="sendXML();">
  <script id="fxtmodel" type="text/xml">
   <!--FXTMODEL-->
   <report db_version="19.0.0.0.0" ... encode="base64" compress="zlib">
     <report_id><![CDATA[/orarep/sqlmonitor/main?...]]></report_id>
       eAHtXXtz2ki2/38+hVZ1a2LvTQwS4pXB1GJDEnYc8ALOJHdrSyVA2GwAYRCOfT/9
       ...
       ffUHVA==
   </report>
   <!--FXTMODEL-->
  </script>
 </body>
</html>

At first glance it’s obvious what needs to be done:

  1. Extract the base64 block
  2. Decode it
  3. Decompress it with zlib
  4. Get the original XML <sql_monitor_report>...</sql_monitor_report>

And indeed — if the database had a built-in zlib decompressor, this would be trivial.

Unfortunately, Oracle does NOT provide a native zlib inflate function.

  • UTL_COMPRESS cannot be used — it expects Oracle’s proprietary LZ container format, not a standard zlib stream.
  • There is no PL/SQL API for raw zlib/DEFLATE decompression.
  • XMLType, DBMS_CRYPTO, XDB APIs also cannot decompress zlib.

Because the RTSM report contains a real zlib stream (zlib header + DEFLATE + Adler-32), Oracle simply cannot decompress it natively.

Solution: use Java stored procedure

The only reliable way to decompress standard zlib inside the database is to use Java.
A minimal working implementation looks like this:

InflaterInputStream inflaterIn = new InflaterInputStream(in);

InflaterInputStream with default constructor expects exactly the same format that RTSM uses.

I created a tiny Java helper ZlibHelper that inflates the BLOB directly into another BLOB.
It lives in the database, requires no external libraries, and works in all Oracle versions that support Java stored procedures.

Source code: https://github.com/xtender/xt_scripts/blob/master/rtsm/parsing/ZlibHelper.sql

PL/SQL API: PKG_RTSM

On top of the Java inflater I wrote a small PL/SQL package that:

  1. Extracts and cleans the base64 block
  2. Decodes it into a BLOB
  3. Calls Java to decompress it
  4. Returns the resulting XML as CLOB
  5. Optionally parses it with XMLTYPE

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

This allows you to do things like:

  xml:=xmltype(pkg_rtsm.rtsm_html_to_xml(:blob_rtsm));

Or load many reports, store them in a table, and analyze execution statistics across hundreds of SQL executions.