{"id":1646,"date":"2013-08-15T12:00:00","date_gmt":"2013-08-15T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1646"},"modified":"2013-08-15T12:00:00","modified_gmt":"2013-08-15T12:00:00","slug":"real-time-sql-monitoring-retention","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2013\/08\/15\/real-time-sql-monitoring-retention\/","title":{"rendered":"Real-Time SQL Monitoring – Retention"},"content":{"rendered":"

As I suggested in my last post<\/a>, 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<\/a>) or the related OEM screens. <\/p>\n

When the developers at my current site started to use SQL Monitoring more often, they would occasionally contact me to ask why a statement didn’t appear in this screen, even though they knew for certain that they had run it 3 or 4 hours ago and had selected ‘All’ or ’24 Hours’ from the ‘Active in last’ drop-down list.<\/p>\n

\"\"<\/p>\n

I noticed when I investigated that some of our busiest test systems only displayed statements from the past hour or two, even when selecting ‘All’ from the drop-down. I asked some friends at Oracle about this and they informed me that there is a configurable limit on how many SQL plans will be monitored that is controlled by the _sqlmon_max_plan<\/code> hidden parameter. It has a default value of the number of CPUs * 20 and controls the size of a memory area dedicated to SQL Monitoring information. This is probably a sensible approach in retrospect because who knows how many long-running SQL statements might be executed over a period of time on your particular system?<\/p>\n

I included this small snippet of information in my SQL Monitoring presentations earlier this year because it’s become a fairly regular annoyance and planned to blog about it months ago but first I wanted to check what memory area would be increased and whether there would be any significant implications.<\/p>\n

Now that I’ve suggested to my client that we increase it across our systems I had a dig around in various V$ views to try to identify the memory implications but didn’t notice anything obvious. My educated guess is that the additional memory requirement is unlikely to be onerous on modern systems but would still like to know for sure and so I’ll keep digging but, if anyone knows already, I’d be very interested …<\/p>\n

Updated later – thanks to Nick Affleck for pointing out the additional ‘s’ I introduced on the parameter name. Fixed now to read _sqlmon_max_plan<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

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 me to ask why a… Continue reading Real-Time SQL Monitoring – Retention<\/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-1646","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":1646,"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":1649,"url":"http:\/\/orcldoug.com\/blog\/2011\/09\/21\/real-time-sql-monitoring-retention-part-2\/","url_meta":{"origin":1646,"position":1},"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":1642,"url":"http:\/\/orcldoug.com\/blog\/2011\/08\/10\/real-time-sql-monitoring-statement-not-appearing\/","url_meta":{"origin":1646,"position":2},"title":"Real-Time SQL Monitoring – Statement Not Appearing","date":"August 10, 2011","format":false,"excerpt":"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\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1030,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/21\/monitoring-index-usage\/","url_meta":{"origin":1646,"position":3},"title":"Monitoring Index Usage","date":"July 21, 2006","format":false,"excerpt":"A common requirement cropped up this week. A new Data Warehouse has just gone live and is still in the 'do we have the right indexes here' phase. In this case, the suspicion is that there are too many indexes in a specific schema and that a number of them\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1570,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-5\/","url_meta":{"origin":1646,"position":4},"title":"Statistics on Partitioned Tables – Part 5","date":"February 28, 2010","format":false,"excerpt":"Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle. The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some\u2026","rel":"","context":"With 18 comments","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":1646,"position":5},"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":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1646","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=1646"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1646\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1646"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1646"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1646"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}