Real-Time SQL Monitoring in SQL Developer

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 post.

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.

So I was intrigued when I noticed Tom Kyte mention 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, I was able to try out RTSM in SQL Developer 1.5.4

Once you have selected Tools->Monitor SQL



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.




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.



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




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.

Thanks to Sue and Tom for their help ...