Real-Time SQL Monitoring with Database Control
Another Oracle Database 11g Release 2 “Enterprise Manager New Feature” is Real-Time SQL Monitoring. On the command line, that was already possible with 11g Release 1, where the views V$SQL_MONITOR and V$SQL_PLAN_MONITOR have been introduced. Now with the new Release, we can do that with the GUI. I have prepared a little demonstration for that by creating a larger sales table (560 M), so that the select statement takes more than 30 seconds on my tiny system. That gives me enough time to watch it real-time and capture the screen:
First we go to the Performance page, Top Activity as usual to spot our High Load statements. Notice the additional card named SQL Monitoring
The statement did run several times before, so there are already statistics from previous runs on the screen. Now while the statement is running, we can leave the refresh rate to 15 seconds or refresh manually:
The statement is shown with a duration of 20 seconds and still running. After completion, the screen looks like that:
Now isn’t that a nice feature to observe the most critical and long running statements in Real-Time?
P.S.: As I just got aware (thanks to Niall Litchfield) this was already possible with Database Control in Release 1 – just from a less obvious place in that tool. So just take it as a 11g New Feature, not as 11g R2 New Feature 
uwe, isn’t it just that the location has moved. The visual sql monitoring screen was accessible from the more monitoring links area at the foot of the page in 11gr1.
Niall Litchfield
October 1, 2009 at 17:16
Niall, thank you for this indication! I apparently did not yet stumble across that possibility in Database Control 11g release 1
Kind regards
Uwe
Uwe Hesse
October 2, 2009 at 09:19
Hi Uwe,
As Doug pointed here, these screen’s were introduced in 11.1.0.7 patchset. As you can see from the post , it show’s execution plan for queries and also parallel query execution details.
Cheers
Amit
Amit
October 4, 2009 at 13:38
Hi,
Oracle is trying to make Enterprise Manager more stronger in new releases.
Rahul
October 20, 2009 at 13:03