On Real-Time SQL Monitoring and the /*+MONITOR*/ Hint

Oracle 11gR1 Enterprise Edition with the Diagnostic and Tuning packs has a very compelling new feature, Real-Time SQL Monitoring. Oracle thinks enough of the feature that it was highlighted prominently in whitepaper by ACE Directory Arup Nanda titled “Oracle Database 11g: The Top Features for DBAs and Developers” (read it here)

Long story short: Too late or too lazy to start a trace on a poorly performing query? Want to know what is going on while it is running? Simply fire up Real-Time SQL Monitoring. Now you can see what steps of the execution plans are running, how much CPU is being consumed, top wait events, number of rows processed per step (expected vs actual!), and a whole lot more.

I’ll take a second to pick on someone I respect (I only pick on the people I really like), who was complaining that SQL is not something you can debug. Well with this feature, it feels like you can!

Here’s what it looks like

the info updates in real time as the query runs

A Trick for Seeing This for All Your Queries

It’s possible that you don’t live in the real world where you have queries that take many minutes or hours to execute. If you want to fire up a monitring session for a query that runs relatively fast, just use the /*+MONITOR*/ Hint.

This Hint tells Oracle (assuming you have the Tuning pack features enabled) to make real-time monitoring for the query available, even if the query isn’t still running. (docs)

SQL Developer Reports

While Kris had my attention, he also pointed out a nifty feature that allows the trouble-shooter to send over a very detailed analysis report to the person who wrote the offending SQL statement. Play with the SQL Monitoring Report It’s interactive, don’t be afraid to click around in it!

Knowing how to make the DBMS package calls really opens up a lot of doors for automation. However I find that getting people to recognize and use the tools is a big first hurdle. And after that, maybe 15% of those people take it to the next level with automation. Those 15% are the folks you want working for you though!

Thanks Robin for the excellent tip and giving us another reason to upgrade to 11gR2!