Donnerstag, 11. Oktober 2018

SQL Real Time Monitoring pure HTML report (thanks to SQLDeveloper 18.3)

Yesterday (from the writing of this post perspective) SQLDeveloper 18.3 came out.
(it seems SQL Developer does not strict follow Oracle Database Release Number schema, otherwise it must be 18.4 already as it's released in 4th quarter of 2018)

There are many bugs fixed on 18.3 and some nice enhancements there as well.

My favorite enhancement is Real Time SQL Monitoring, HTML exports now available - no flash!

As Tanel Poder wrote about it already (Generate Oracle SQL Monitoring Reports as HTML using SQL Developer v18.3 (no Flash needed)) I'll show how you can use it without SQL Developer.
I'm not against SQL Developer, in fact I'm very happy with it!
Just the Real Time SQL Monitor tab seems kind of unresponsive to me, especially on DBs with many active sessions and high load.

So if I don't want to watch a java process drawing circles and showing blur previews, I first go to query v$sql_monitor. Beside all the columns I'll need to identify my SQL of interest, I need the columns SQL_ID, SQL_EXEC_ID, and SQL_EXEC_START.
And, of course at least once you must create a new HTML Report with SQL Developer! Save this report as a template for later use.

When you open the HTML report in an editor, there are 2 lines of special interest:
 var data_sqlId = '3v64dcg0rja6k'; 
 var data_xml = '<report db_version=">"

You can replace them easily with the context of your SQL of interest.
To get the proper XML you can use DBMS_SQLTUNE.report_sql_monitor. But as this function provides a multiline XML, but the HTML expects the XML in one line, the call should be
SELECT XMLSerialize( DOCUMENT xmltype(DBMS_SQLTUNE.report_sql_monitor(
  sql_id         => '3v64dcg0rja6k',
  type           => 'XML',
  SQL_EXEC_ID    => 33554648, 
  SQL_EXEC_START => to_date('2018-10-10 05:36:17'),
  report_level   => 'ALL')) NO INDENT ) AS XML_report
FROM dual;
If you omit SQL_EXEC_ID or SQL_EXEC_START, the report will still compile but you should understand the difference in data you get displayed.

So the minimal count you create a new HTML-Only SQL Real Time Monitoring report with SQL Developer should be once. But if you read this ( and your DBs are properly licensed), spread the good news with all the people who might benefit from these reports!

Please keep in mind that the HTML file is loading CSS and JS files from and URL at Oracles Content Delivery platform - so they are subject to any change without a new release of SQL Developer, RDBMS or anything. So expect unexpected changes every time!

1 Kommentar:

Randolf Geist hat gesagt…

Hi Martin,

thanks a lot for pointing this out.

Actually I required an additional escape of single quotes in the generated string to paste representing the XML Monitoring report, otherwise the HTML would error out internally, so this did the trick for me:

SELECT XMLSerialize( DOCUMENT xmltype(replace(sys.dbms_auto_report.report_repository_detail(rid => 1804255, type => 'XML'), q'!'!', q'!\'!') ) NO INDENT ) AS XML_report FROM dual;

which also demonstrates that the new "Historic" SQL Monitoring Report available from 12c on can be used to generate suitable XML. The crucial point is the "replace" function call however.

Hopefully the less than / greater than signs don't get screwed up here.