Infrastructure at your Service

Franck Pachot

Oracle SQL Monitoring reports in flash, html, text

By Franck Pachot

.
I have recently posted on the way I like to extract execution plans. When we have Tuning pack, I suggest to get them with SQL Real-Time Monitoring as an active report. However, Martin Preiss said in a comment that he prefers the text format – easier to search, copy, and paste. And that’s a very good remark. We still need plain text.

My point is that if you send me the active report, then I can extract the text version from it. The opposite is not possible. It’s a bit tricky, I’ll show how to do.

The active report is very nice: it is an html file with an embedded xml data in it. When you open it, it loads a flash application from the oracle site and displays that nice report:

CaptureSQLMon001.PNG

Its easy to navigate, drill dow, etc. I often get to the html source in order to see some information that is hidden in the xml but which is not displayed, such as the optimizer parameters used by the query. Here is an extract from the html source:

CaptureSQLMon002.PNG

Yes. I used to do that in 11g. And once, I asked someone to send me his 12c report and I had a bad surprise. The xml is compressed and I can’t see anything:

CaptureSQLMon005.PNG

Oracle 12c introduced that nice optimization in order to have smaller files, but I prefered the previous one. But look at the attributes: it’s base 64 encoded and compressed with zlib.

So don’t worry, you have linux utilities to uncompress that:

CaptureSQLMon007.PNG

From the sqlmon12c.html, I have extracted to sqlmon12c_decoded.html the report with xml data as I’ve shown above. Good. I got my xml back 🙂

Then back to Martin’s comment. The active report is good to navigate on it. The xml is good to find some hidden information. But If I want to paste a plan to the report I deliver to my customer, I prefer text. A table in text is ok for a Word document.

First I extract only the xml and include it within ‘report’ tags:

CaptureSQLMon008.PNG

[sorry for the code as screenshot, but the html tags in the awk script are hard to display here]

From that xml, I apply the sqlmonitorText.xsl XSLT template which is found in ORACLE_HOME:

xsltproc $ORACLE_HOME/rdbms/xml/orarep/sqlmonitor/sqlmonitorText.xsl sqlmon12c_extracted.xml > sqlmon12c_text.html

And here is the result, a nice HTML table that perfectly fits in my .doc document:

CaptureSQLMon003.PNG

But there is something else that we cannot do with that. On large execution plans, I often have to bring the cursor up and down in order to go from a child operation to its parent (example: from a high ‘execs’ number within a nested loop I go to the rowsource that provided that number of rows). So I need text. Plain text.

No need to call the DBMS_REPORT functions for that. Linux has text web browsers. For example:

elinks -dump -dump-width 150 sqlmon12c_text.html | grep -v “^ |–” > sqlmon12c_text.txt

…which gives me something useful for notepad.exe or vim:

CaptureSQLMon006.PNG

So this is why I prefer to have the SQL Monitoring report in the active html format: I can read it immediately, and convert it to what I want later. I have a script for that. A very dirty script. I’ll share it anyway. Just call it with the name of the active html report file. here it is: decode-sqlmon.zip

But if someone wants to make a clean script for that, don’t hesitate to share 🙂

Update 25-JUL-2014

Tyler Muth (@tmuth) has a very nice way to get uncompressed unencoded xml in: this project

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod