{"id":3767,"date":"2014-05-15T17:28:13","date_gmt":"2014-05-15T15:28:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/"},"modified":"2014-05-15T17:28:13","modified_gmt":"2014-05-15T15:28:13","slug":"oracle-sql-monitoring-reports-in-flash-html-text","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/","title":{"rendered":"Oracle SQL Monitoring reports in flash, html, text"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nI 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<\/a> that he prefers the text format – easier to search, copy, and paste. And that’s a very good remark. We still need plain text.<\/p>\n

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.<\/p>\n

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:<\/p>\n

\"CaptureSQLMon001.PNG\"<\/a><\/p>\n

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:<\/p>\n

\"CaptureSQLMon002.PNG\"<\/a><\/p>\n

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:<\/p>\n

\"CaptureSQLMon005.PNG\"<\/a><\/p>\n

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.<\/p>\n

So don’t worry, you have linux utilities to uncompress that:<\/p>\n

\"CaptureSQLMon007.PNG\"<\/a><\/samp><\/p>\n

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 \ud83d\ude42<\/p>\n

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.<\/p>\n

First I extract only the xml and include it within ‘report’ tags:<\/p>\n

\"CaptureSQLMon008.PNG\"<\/a><\/p>\n

[sorry for the code as screenshot, but the html tags in the awk script are hard to display here]<\/p>\n

From that xml, I apply the sqlmonitorText.xsl XSLT template which is found in ORACLE_HOME:<\/p>\n

xsltproc $ORACLE_HOME\/rdbms\/xml\/orarep\/sqlmonitor\/sqlmonitorText.xsl sqlmon12c_extracted.xml > sqlmon12c_text.html<\/samp><\/p>\n

And here is the result, a nice HTML table that perfectly\u00a0fits in my .doc document:<\/p>\n

\"CaptureSQLMon003.PNG\"<\/a><\/p>\n

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.<\/p>\n

No need to call the DBMS_REPORT functions for that. Linux has text web browsers. For example:<\/p>\n

elinks -dump -dump-width 150 sqlmon12c_text.html | grep -v “^ |–” > sqlmon12c_text.txt<\/strong><\/samp><\/p>\n

…which gives me something useful for notepad.exe or vim:<\/p>\n

\"CaptureSQLMon006.PNG\"<\/a><\/p>\n

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:\u00a0decode-sqlmon.zip<\/a><\/p>\n

But if someone wants to make a clean script for that, don’t hesitate to share \ud83d\ude42<\/p>\n

Update 25-JUL-2014<\/h3>\n

Tyler Muth (@tmuth<\/a>) has a very nice way to get uncompressed unencoded xml in: this project<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

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. […]<\/p>\n","protected":false},"author":27,"featured_media":3768,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[348,143,96,209,452],"type_dbi":[],"class_list":["post-3767","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","category-oracle","tag-execution-plan","tag-monitoring","tag-oracle","tag-oracle-12c","tag-sql-monitoring"],"acf":[],"yoast_head":"\nOracle SQL Monitoring reports in flash, html, text - dbi Blog<\/title>\n<meta name=\"description\" content=\"How to decode active html reports from Oracle SQL Monitor to different formats\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle SQL Monitoring reports in flash, html, text\" \/>\n<meta property=\"og:description\" content=\"How to decode active html reports from Oracle SQL Monitor to different formats\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-05-15T15:28:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1062\" \/>\n\t<meta property=\"og:image:height\" content=\"670\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle SQL Monitoring reports in flash, html, text\",\"datePublished\":\"2014-05-15T15:28:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/\"},\"wordCount\":635,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png\",\"keywords\":[\"Execution plan\",\"Monitoring\",\"Oracle\",\"Oracle 12c\",\"SQL Monitoring\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/\",\"name\":\"Oracle SQL Monitoring reports in flash, html, text - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png\",\"datePublished\":\"2014-05-15T15:28:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"How to decode active html reports from Oracle SQL Monitor to different formats\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png\",\"width\":1062,\"height\":670},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle SQL Monitoring reports in flash, html, text\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle SQL Monitoring reports in flash, html, text - dbi Blog","description":"How to decode active html reports from Oracle SQL Monitor to different formats","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/","og_locale":"en_US","og_type":"article","og_title":"Oracle SQL Monitoring reports in flash, html, text","og_description":"How to decode active html reports from Oracle SQL Monitor to different formats","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/","og_site_name":"dbi Blog","article_published_time":"2014-05-15T15:28:13+00:00","og_image":[{"width":1062,"height":670,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png","type":"image\/png"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle SQL Monitoring reports in flash, html, text","datePublished":"2014-05-15T15:28:13+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/"},"wordCount":635,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png","keywords":["Execution plan","Monitoring","Oracle","Oracle 12c","SQL Monitoring"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/","name":"Oracle SQL Monitoring reports in flash, html, text - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png","datePublished":"2014-05-15T15:28:13+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"How to decode active html reports from Oracle SQL Monitor to different formats","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSQLMon001.png","width":1062,"height":670},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-sql-monitoring-reports-in-flash-html-text\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle SQL Monitoring reports in flash, html, text"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3767"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3767\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/3768"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3767"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}