{"id":1506,"date":"2009-07-02T12:00:00","date_gmt":"2009-07-02T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1506"},"modified":"2009-07-02T12:00:00","modified_gmt":"2009-07-02T12:00:00","slug":"real-time-sql-monitoring-in-sql-developer","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2009\/07\/02\/real-time-sql-monitoring-in-sql-developer\/","title":{"rendered":"Real-Time SQL Monitoring in SQL Developer"},"content":{"rendered":"

Features in this post require both Diagnostics and Tuning Pack licenses.<\/em><\/p>\n

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 via Greg Rahn’s blog post<\/a>.<\/p>\n

To date I’ve used it via DB Control for demos and it’s sweet, but one of the problems with any demo based on DB\/Grid Control is that it’s use is likely to be limited to those who have DBA access. Yes, you can set up view access to GC so that people can monitor performance of targets that they have sufficient account privileges on, but a lot of sites won’t want the overhead of setting that up for developers and app support teams.<\/p>\n

So I was intrigued when I noticed Tom Kyte mention<\/a> that Oracle have been working on making more of these tools available to developers. After a quick email, a look at his slides and some further help from Sue Harper<\/a>, I was able to try out RTSM in SQL Developer 1.5.4<\/p>\n

Once you have selected Tools->Monitor SQL<\/p>\n

\"\"<\/p>\n

you’ll see a grid table of records. This will include all monitored statements, including this example parallel query that I’m running just now.<\/p>\n

\"\"<\/p>\n

If I right-click any of the statements and select ‘Show SQL Details’ I’ll see the Real Time SQL Monitoring screen, which is deeply cool. <\/p>\n

\"\"<\/a><\/p>\n

One criticism, though. On my dinky laptop screen, the execution plan steps don’t display properly as that column’s too narrow. I can resize it <\/p>\n

\"\"<\/a><\/p>\n

but then it just annoyingly sets it back to it’s original width every time the screen refreshes. Hopefully that’s something that can be fixed, for those of us with dinky monitors 😉 Even so, it saved me during my last course teach because 11g DB Control started to play up on my laptop, so I was able to fall back on the SQL Developer option. It’s definitely worth a look.<\/p>\n

Thanks to Sue and Tom for their help …<\/p>\n","protected":false},"excerpt":{"rendered":"

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 via Greg Rahn’s blog… Continue reading Real-Time SQL Monitoring in SQL Developer<\/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-1506","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1320,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g-part-2\/","url_meta":{"origin":1506,"position":0},"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":[]},{"id":1614,"url":"http:\/\/orcldoug.com\/blog\/2010\/10\/03\/network-events-in-ash\/","url_meta":{"origin":1506,"position":1},"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":1030,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/21\/monitoring-index-usage\/","url_meta":{"origin":1506,"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":1649,"url":"http:\/\/orcldoug.com\/blog\/2011\/09\/21\/real-time-sql-monitoring-retention-part-2\/","url_meta":{"origin":1506,"position":3},"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":1044,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/04\/tracing-session-activity-over-a-remote-database-link\/","url_meta":{"origin":1506,"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":1112,"url":"http:\/\/orcldoug.com\/blog\/2009\/10\/29\/10g-consolidated-trace-files-and-px\/","url_meta":{"origin":1506,"position":5},"title":"10g Consolidated Trace Files and PX","date":"October 29, 2009","format":false,"excerpt":"At the end of my Tracing Parallel Execution presentation at the Scottish OUG conference, Michael M\u00f8ller of Miracle asked whether the consolidated trace files produced by the DBMS_MONITOR package and trcsess utility are in strict time order. i.e. Will I see the actions of the query coordinator, then the actions\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1506","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=1506"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1506\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1506"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1506"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1506"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}