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()ordbms_sqltune.report_sql_monitor_xml()— for reports still present ingv$sql_monitor.dbms_auto_report.report_repository_detail_xml()— for reports already stored in history (AWR), underdba_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_startrep_date,inst_count,cpu_corescon_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_tabdb_versionparse_schema- Full (adaptive) PHV and normal final plan hash value (
plan_hash_full,plan_hash,plan_hash_2) peeked_bindsxplan_statsqb_registryoutline_datahint_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
