Subtitle: Performance Analysis with TaRTeSMon

Hi there,

In this blog post I want to write about Real-Time SQL monitoring enhancements of 12c and I do this almost simultaneously to my speeches about that topic. One is at the Trivadis internal Techevent on March 18th 2017. And the other is at DOAG Database conference in Düsseldorf (https://datenbank.doag.org) on May 30th 2017, which is a public event.

Feature History, Views and License

Real-Time SQL Monitoring is nothing new. It was introduced with 11g and allows monitoring a certain execution of a typically long running SQL statement. It is enabled automatically if parallel execution is used or if the statement uses at least 5 seconds on CPU or IO. It can be enabled manually with the MONITOR hint. Run-time information and real-time statistics are provided during the run of the queries; those contain information about CPU and IO times, cardinality of intermediate results, memory and temporary space consumptions of each operator of an execution plan. Another important detail is also stored: bind variables, as of 11.2. As of 12cR1 you can also build custom composite database operations with the help of dbms_sql_monitor.begin_operation and dbms_sql_monitor.end_operation.

The views which are used to exposure that information in real-time are gv$sql_monitor and gv$sql_monitor_plan. To be able to make use of it, you need diagnostics and tuning pack license and the parameter “control_management_pack_access” needs to be set to “DIAGNOSTIC+TUNING”. Querying the mentioned views is one way of getting the real-time statistics, a more useful way is to use the PL/SQL API, namely report_sql_monitor function in package dbms_sqltune, as of 12c also in package dbms_sql_monitor, or to use Oracle Enterprise Manager or even SQL Developer. The problem is (at least up to 11g), that the monitoring information is only available for a limited period after the statement has finished. After that time, which is dependent on workload and shared pool size, it was simply not available any more.

Active Reports

The mentioned package functions create reports of type HTML, XML, ACTIVE or TEXT. The active reports (set parameter to ‘ACTIVE’) can then be rendered by a standard browser with the help of flash elements which are downloaded from Oracles OTN website for that purpose. Active reports are also available in SQL Developer and in Oracle Enterprise Manager.
See also http://www.oracle.com/technetwork/database/manageability/sql-detail-099420.html
and https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1
for more details about that.

What is new as of 12c? – Real-Time SQL Monitoring Reports

New views: gv$sql_monitor_sesstat, gv$sql_monitor_statname for which I did not find use cases so far. Dba_hist_reports, dba_hist_reports_details: these 2 views are more interesting as they store real-time SQL monitoring reports in an XML format: stored reports.
New columns in gv$sql_monitor:
report_id: unique ID of the stored report, 0 means, not stored yet
as of 12.2 in_dbop_name, in_dbop_exec_id, io_cell_uncompressed_bytes, io_cell_offload_eligible_bytes, io_cell_offload_returned_bytes. The latest 3 are interesting on Exadata or SuperCluster.

So - one remarkable feature as of 12c is, that the real-time SQL monitoring information is stored for a longer period now. The saving of those XML reports is done by MMON background process / module name “MMON_SLAVE”, Action Name “Automatic Report Flush”. It does so only if control_management_pack_access is set to “DIAGNOSTICS+TUNING” and for that it uses sys.dbms_auto_report_internal.i_save_report, which calls sys.dbms_report.get_report_with_summary, both are undocumented. Reports are stored in wrp$_reports_details in SYSAUX tablespace. The XML report column is a BLOB, it contains a compressed representation of the original XML. The compression is done by the packages not by SecureFile compression. Reports are generated only for SQL statements which are not executing or queued any more. The reports are automatically purged in conformance to AWR retention policy.

Only for the 5 top statements per minute reports are generated and stored. You can use dbms_auto_report.start_report_capture and dbms_auto_report.finish_report_capture to store reports for all monitored statements. Internally a flag is swapped for this, which is respected by MMON. Basically it changes the execution mode for the MMON action from regular to full_capture and vice versa, which can also be seen in dba_hist_reports_control. The documented views dba_hist_reports and dba_hist_reports_details can be used to access XML reports; these views transparently decompress the contained data. Dba_feature_usage_statistics shows an active usage of “Real-Time SQL Monitoring”, if the saving of reports is done by MMON.

Getting stored reports with dba_hist-Views

Following example query can be used to get an XML report from the history with the help of the dba_hist-views:

SELECT
  XMLSERIALIZE( DOCUMENT
  XMLTYPE (
      d.report
  ) as CLOB INDENT) report
FROM dba_hist_reports r, dba_hist_reports_details d
WHERE r.report_id = d.report_id
AND r.component_name='sqlmonitor'
AND r.report_id = &report_id

The view dba_hist_reports contains overview information with XML-structured column report_summary.

The XML reports can be visualized with the help of the flash plugins, the same which are used by the standard active reports. But the pure XML reports contain even more, which is not visible in the browser, e.g.: sql_exec_id, session_id, session_serial, program, module, action, service; optimizer environment, outline of execution plan, peeked binds, …. You can access that information in the XML by using SQL/XPath within the database or by simply reading through carefully.

Spooling Active Reports from the Report History

To render a historic XML representation of a real-time SQL monitoring report as an active report one can simply use OEM (performance tab, “SQL Monitoring” page, switch to “View Data: Historical”) if available or has to create an HTML file with the XML, which is embedded by some HTML / Javascript code, which can be borrowed from reports created with dbms_sql_monitor.report_sql_monitor. I created a SQL*Plus script, which exactly does this for you. I called it TaRTeSMon: Trivadis Real-Time SQL Monitoring. You can download it from here.
http://zarick.de/tartesmon.sql

TaRTeSMon

The SQL*Plus script spools an HTML overview and the accessible real-time SQL monitoring reports. Optionally you can apply a filter, which is recommended to reduce the runtime. To use it you need a connection to the database in question with a user with “create session” and SELECT-privilege on the mentioned views gv$sql_monitor, dba_hist_reports, dba_hist_reports_details. TaRTeSMon creates a directory on the host where SQL*plus is called with all the HTML reports.

Example, which spools active reports for all executions with an elapsed time of more than 100 seconds:

SQL> @http://zarick.de/tartesmon
you can create a filter here based on following attributes:
report_id           NUMBER
con_id              NUMBER
inst_id             NUMBER
status              VARCHAR2
username            VARCHAR2
module              VARCHAR2
action              VARCHAR2
service_name        VARCHAR2
program             VARCHAR2
sid                 NUMBER
session_serial#     NUMBER
elapsed_time        NUMBER
sql_text            VARCHAR2
sql_id              VARCHAR2
sql_exec_start      DATE
sql_exec_id         NUMBER
sql_plan_hash_value NUMBER
first_refresh_time  DATE
last_refresh_time   DATE
refresh_count       NUMBER
still_in_memory     VARCHAR2

Press enter if you do not want to apply a filter
Enter a filter here: elapsed_time > 100000000
generation finished, output directory is tartesmon_PHYS_20170202_172407

The following picture shows an example of a TaRTeSMon page:

You can also browse to the following example output provided here
http://zarick.de/tartesmon_PHYS_20170317_113034/index_tartesmon_PHYS.html,

TaRTeSMon for me appeared to be useful as a supplemental tool for performance tuning sessions to access the historic real-time SQL monitoring reports. Of course it can only be used as of 12c and with Enterprise Edition with diagnostics and tuning pack. It helps to get more insights, e.g. to find bind variables of specific runs, to drill into execution plans with comparisons of estimated and actual rows and much more. It is also very convenient to have a possibility to take the reports for an offline analysis or if Oracle Enterprise Manager is not available.

Summary

12c introduced persistent real-time SQL monitor reports, which can be accessed only with diagnostics and tuning pack. TaRTeSMon helps you to spool a list of SQL monitoring reports from the history which can be opened in a browser.

I have much more details in my talks at Trivadis Techevent and DOAG database in Düsseldorf, so I encourage you to join them.