Real-Time SQL Monitoring (Oracle 11.1.0.7.0) – May I Present The GUI…

Real Time SQL Monitoring is more or less old news because it was build-in in Oracle version 11.1.0.6.0. The documentation can be found in the Oracle® Database Performance Tuning Guide 11g Release 1 (11.1). To be exact in chapter 10, paragraph 4: Real-Time SQL Monitoring. Output in text or HTML was already available, but now with 11.1.0.7.0, it is also build-in in DB Console (your local OEM).

As said in the very nice and informative post from Greg Rahn, from the Real-World Performance group at Oracle Corporation, in his post: Oracle 11g: Real-Time SQL Monitoring Using DBMS_SQLTUNE.REPORT_SQL_MONITOR; You need to set some parameters to enable this feature + you will have to use a hint ,to actually show the outcome in your DB Console, in de SQL statement you want to monitor :

• STATISTICS_LEVEL initialization parameter is either set to ALL or TYPICAL (the default value)

• CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING (the default value)

To enable monitoring on a statement you use the hint MONITOR in your statement and to disable this feature you can enforce no monitoring by using an explicit NO_MONITOR hint.

So if you now would use the hint /*+MONITOR*/ in your statement; The "SQL Monitoring" link in the under the "Performance" tab will show you, for example while doing a "select /*+MONITOR*/ * from dba_objects;", the following graphical overviews:

There is a small oddity in this screen. Do you see the difference in time duration, between the default (right from the bar) and while left clicking the bar with the cursor (mouse pointer isn’t shown in this screenshot)…?

You can zoom in, via mark the whole row and the left clicking it.

This will show you a list menu that re-directs you to other parts of the DB Console or the SQL Monitoring text output or the GUI "Monitored SQL Execution Output"  output. If you choose the GUI output, then this will lead you to an plan statistics graphical report.

Here you can also choose for an activity overview while the statement is running, as the following picture shows.

Graphics are dynamically build-up, so you can see the performance indicator responses in real-time (and zoom in)!

The following shows the 3rd time of executing the "select /*+MONITOR*/ * from dba_objects;" statement. The third statement in the following picture is still running and is shown here with the "star" indicator in the status column.

Be-aware that these parameters themselves can have a performance impact and/or using this functionality needs probably also the appropriate license.

HTH

Marco