{"id":1649,"date":"2011-09-21T12:00:00","date_gmt":"2011-09-21T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1649"},"modified":"2011-09-21T12:00:00","modified_gmt":"2011-09-21T12:00:00","slug":"real-time-sql-monitoring-retention-part-2","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2011\/09\/21\/real-time-sql-monitoring-retention-part-2\/","title":{"rendered":"Real-Time SQL Monitoring – Retention (part 2)"},"content":{"rendered":"

As I mentioned in my last post<\/a>, 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 be to help us come up with a sensible value. Here is how :-<\/p>\n

Connected to:\nOracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production\nWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,\nData Mining and Real Application Testing options\n\nSQL> select * from v$sgastat where name like '%keswx%' ;\n\nPOOL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BYTES\n------------ -------------------------- ----------\nshared pool\u00a0 keswx:plan en\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 645696\nshared pool\u00a0 keswxNotify:tabPlans\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16384\nshared pool\u00a0 keswx:batch o\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3646864<\/code><\/pre>\n
Those are the values on a system with _sqlmon_max_plan=320. <\/p>\n
Thanks to those who helped out with this – they know who they are.<\/p>\n
Coming up with an appropriate value is going to involve considering each system’s workload, though, because it’s not a time-based retention parameter. If people are interested in statements that ran in the last 12 hours, then the value would be different on each system. But at least now we’ll be able to see the impact, which looks pretty reasonable to me.<\/p>\n
Updated later – thanks to Nick Affleck for pointing out the
\nadditional ‘s’ I introduced on the parameter name. Fixed now to read
\n_sqlmon_max_plan<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
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 be to help us come… Continue reading Real-Time SQL Monitoring – Retention (part 2)<\/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-1649","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1646,"url":"http:\/\/orcldoug.com\/blog\/2013\/08\/15\/real-time-sql-monitoring-retention\/","url_meta":{"origin":1649,"position":0},"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":1506,"url":"http:\/\/orcldoug.com\/blog\/2009\/07\/02\/real-time-sql-monitoring-in-sql-developer\/","url_meta":{"origin":1649,"position":1},"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":1030,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/21\/monitoring-index-usage\/","url_meta":{"origin":1649,"position":2},"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":1614,"url":"http:\/\/orcldoug.com\/blog\/2010\/10\/03\/network-events-in-ash\/","url_meta":{"origin":1649,"position":3},"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":1044,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/04\/tracing-session-activity-over-a-remote-database-link\/","url_meta":{"origin":1649,"position":4},"title":"Tracing session activity over a remote database link","date":"August 4, 2006","format":false,"excerpt":"Yesterday someone asked me how to trace a session that selects from a view in a remote database via a link. If they activated the trace on the local instance, they wouldn't see the bulk of the work which was happening on the remote instance - just a bunch of\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1320,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g-part-2\/","url_meta":{"origin":1649,"position":5},"title":"Parallel Query and 11g – Part 2","date":"September 23, 2007","format":false,"excerpt":"Now, that's weird. A little surprising might be more accurate and maybe I'm missing something.During the various tests with and without parallel hints and different parallel_io_cap_enabled settings, I expected the runs that didn't use parallelism to show up \"db file scattered read\" events in the trace files. For example, here's\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\/1649","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=1649"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1649\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1649"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}