{"id":1642,"date":"2011-08-10T12:00:00","date_gmt":"2011-08-10T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1642"},"modified":"2011-08-10T12:00:00","modified_gmt":"2011-08-10T12:00:00","slug":"real-time-sql-monitoring-statement-not-appearing","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2011\/08\/10\/real-time-sql-monitoring-statement-not-appearing\/","title":{"rendered":"Real-Time SQL Monitoring – Statement Not Appearing"},"content":{"rendered":"

Like Greg Rahn<\/a>, I’ve looked at many SQL Monitoring reports over the past year or two. Possibly not as many as Greg, but it’s become my default method of communicating* SQL performance issues to colleagues to the point that some might be finding it irritating by now, whilst others are hooked from the start. (Personally, I can’t understand those who aren’t hooked from the start!)<\/p>\n

One of those who have been hooked came to me with a problem last week. He simply couldn’t see his report in the OEM SQL Monitoring screen and after asking him if it was really running right now (it was) and attempting a re-run with a \/*+ MONITOR *\/ hint, I was almost stumped. Then I suggested we fall back on using DBMS_XPLAN.DISPLAY_CURSOR to get the plan and when I saw the results, I suddenly understood what the problem was. This was a massive plan! It wasn’t a particularly complex query but it was referencing a Data Dictionary view (I can’t remember which one now) which expanded out into what looked like hundreds of lines. Which was the problem.<\/p>\n

There’s a hidden parameter – _sqlmon_max_planlines with a default value of 300 – which limits the number of lines beyond which a statement will not be monitored. This statement exceeded that limit.<\/p>\n

A small post, but hopefully useful should you ever wonder why a statement isn’t appearing. Some day soon there’ll be another post about why you can’t find statements that you know executed fairly recently, which is a much more common problem in my day-to-day work. (As a preview, that parameter is _sqlmon_max_plan.) Then another one about the mysterious disappearing tabs!<\/p>\n

* Communication is where SQL Mon excels, with the ability to send ACTIVE reports to allow others to dig around in the detail.<\/p>\n","protected":false},"excerpt":{"rendered":"

Like Greg Rahn, I’ve looked at many SQL Monitoring reports over the past year or two. Possibly not as many as Greg, but it’s become my default method of communicating* SQL performance issues to colleagues to the point that some might be finding it irritating by now, whilst others are hooked from the start. (Personally,… Continue reading Real-Time SQL Monitoring – Statement Not Appearing<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1642","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1506,"url":"http:\/\/orcldoug.com\/blog\/2009\/07\/02\/real-time-sql-monitoring-in-sql-developer\/","url_meta":{"origin":1642,"position":0},"title":"Real-Time SQL Monitoring in SQL Developer","date":"July 2, 2009","format":false,"excerpt":"Features in this post require both Diagnostics and Tuning Pack licenses.If you haven't seen 11g's Real Time SQL Monitoring feature, you need to. It's one of the most useful Oracle performance troubleshooting tools I've seen since I started working with Oracle too long ago. I was first aware of it\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"\/serendipity\/uploads\/xsqldev1.png.pagespeed.ic.lI55QN9Yna.png","width":350,"height":200},"classes":[]},{"id":1646,"url":"http:\/\/orcldoug.com\/blog\/2013\/08\/15\/real-time-sql-monitoring-retention\/","url_meta":{"origin":1642,"position":1},"title":"Real-Time SQL Monitoring – Retention","date":"August 15, 2013","format":false,"excerpt":"As I suggested in my last post, there's at least one more reason that your long-running SQL statements might not appear in SQL Monitoring views (e.g. V$SQL_MONITOR) or the related OEM screens. When the developers at my current site started to use SQL Monitoring more often, they would occasionally contact\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/RTSM_1.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1649,"url":"http:\/\/orcldoug.com\/blog\/2011\/09\/21\/real-time-sql-monitoring-retention-part-2\/","url_meta":{"origin":1642,"position":2},"title":"Real-Time SQL Monitoring – Retention (part 2)","date":"September 21, 2011","format":false,"excerpt":"As I mentioned in my last post, I've been looking at increasing the SQL Monitoring Retention at my current site using _sqlmon_max_plan but, as well as confirming with Oracle Support that they're happy for us to do so, it would be nice to know what the resulting memory footprint would\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1495,"url":"http:\/\/orcldoug.com\/blog\/2009\/05\/01\/diagnosing-locking-problems-using-ashlogminer-the-end\/","url_meta":{"origin":1642,"position":3},"title":"Diagnosing Locking Problems using ASH\/LogMiner \u2013 The End","date":"May 1, 2009","format":false,"excerpt":"Except\u00a0it's not the\u00a0end, of course. What I mean is\u00a0that I usually agree with\u00a0what Miladin Modrakovic said in one of his comments on his first deadlock blog post.\"There is always way around.\"As I keep saying, there are many different ways of diagnosing locking problems. Which one works best\u00a0depends on the situation\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1614,"url":"http:\/\/orcldoug.com\/blog\/2010\/10\/03\/network-events-in-ash\/","url_meta":{"origin":1642,"position":4},"title":"Network Events in ASH","date":"October 3, 2010","format":false,"excerpt":"Note - using ASH and the Top Activity screen require the use of the Diagnostics Pack License.This post was prompted by yet another performance problem identified using pretty pictures and Active Session History data. Although, as you'll see, some pretty old-fashioned tools played their part too!ASH entries only exist for\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1016,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/12\/table-reorgs-and-statistics\/","url_meta":{"origin":1642,"position":5},"title":"Table reorgs and statistics","date":"July 12, 2006","format":false,"excerpt":"While working on the ITL deadlock problem (which looks like it's been fixed by the initrans increase and table rebuild), the developers highlighted another table as hitting this problem in the past. When I investigated, I found that initrans had already been set to 6 so this had obviously happened\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1642","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1642"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1642\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1642"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1642"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1642"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}