ThatJeffSmith

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.

Before you waste a lot of precious time with your fancy reading and such, go play with this interactive report RIGHT NOW.

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

Watch each step of the plan execute!

I added the red-circles to highlight where the CBO is encountering more data than it’s expecting, the number of executions per step, and the amount of memory consumed.

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)

Example:

SELECT /*+MONITOR*/ t.table_name, USER AS owner, t.cluster_name,
       t.partitioned, t.iot_type, t.tablespace_name, 
       t.last_analyzed, ROUND (t.num_rows) num_rows,
       t.TEMPORARY, t.table_type, t.table_type_owner, t.NESTED,
       DECODE (   NVL (tablespace_name, 'x')
               || UPPER (partitioned)
               || NVL (iot_type, 'x')
               || TO_CHAR (pct_free),
               'xNOx0', 'YES',
               'NO'
              ) is_external,
       t.dropped, t.initial_extent
  FROM SYS.user_all_tables t
 WHERE 1 = 1;

Here's what a query that has already finished looks like in SQL Developer

Thanks to Kris for pointing this out to me yesterday.

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!