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 🙂
#1 von Niall Litchfield am Oktober 1, 2009 - 17:16
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.
#2 von Uwe Hesse am Oktober 2, 2009 - 09:19
Niall, thank you for this indication! I apparently did not yet stumble across that possibility in Database Control 11g release 1 🙂
#3 von Amit am Oktober 4, 2009 - 13:38
As Doug pointed here, these screen’s were introduced in 188.8.131.52 patchset. As you can see from the post , it show’s execution plan for queries and also parallel query execution details.
#4 von Rahul am Oktober 20, 2009 - 13:03
Oracle is trying to make Enterprise Manager more stronger in new releases.