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:
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 :-)