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.
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.
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.htmland https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1for more details about that.
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.
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) reportFROM dba_hist_reports r, dba_hist_reports_details dWHERE r.report_id = d.report_idAND 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.
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/tartesmonyou can create a filter here based on following attributes:report_id NUMBERcon_id NUMBERinst_id NUMBERstatus VARCHAR2username VARCHAR2module VARCHAR2action VARCHAR2service_name VARCHAR2program VARCHAR2sid NUMBERsession_serial# NUMBERelapsed_time NUMBERsql_text VARCHAR2sql_id VARCHAR2sql_exec_start DATEsql_exec_id NUMBERsql_plan_hash_value NUMBERfirst_refresh_time DATElast_refresh_time DATErefresh_count NUMBERstill_in_memory VARCHAR2Press enter if you do not want to apply a filterEnter a filter here: elapsed_time > 100000000generation 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 herehttp://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.
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.