SQL Monitoring

For managing and helping to tune long-running SQL statements, Enterprise Manager Cloud Control 12c provides a very useful utility called SQL Monitoring, which is enabled out of the box with no performance impact.

This will automatically monitor any long-running SQL statement that has gone beyond 5 seconds of either CPU or I/O time, as well as any parallel statements. However, in a very busy system with lots of such queries, SQL monitoring may not take place for all the queries. You can use the /*+MONITOR*/ hint in your SQL statements to explicitly ask for SQL monitoring in this case.

You can access the SQL monitoring page by going to Performance | SQL Monitoring. Any SQL statements that are long-running or parallel will appear on this page. For example, the SQL executed by user PORUSHH has taken 36 seconds to execute so far, as shown in the following screenshot:

The completed SQL statements are shown with a tick mark. There is a rotating wheel shown against the statements that are still being processed at that point in time.

You can drill down on the long-running SQL and examine the actual execution steps it is working on. This is shown dynamically by Enterprise Manager in the following screenshot. The text in this screenshot may not be readable, but is shown for the purpose of illustration:

Under the Plan Statistics tab, green arrows instantly appear against the currently executing steps. Certain columns in this section such as Actual Rows, Memory, and Temp (this is the temporary space used) are refreshed with the latest data at every refresh interval—this is shown by the appearance of a green shading on the column data at the moment of refresh.

This makes it possible for the DBA to understand exactly the progress of the long-running SQL, and is very useful when it is required to find the status of a long-running major report or job working in the database. This feature is a part of the Database Tuning Pack.

One point to note in the preceding screenshot is that the red band in the Wait Activity column against the SORT ORDER BY operation signifies that the sort has spilled over to the disk. So this is an expensive operation.

From Oracle Database 11g Release 2 onwards, PL/SQL programs can also be monitored on the SQL monitoring page. You can drill down to the slow SQL statements in PL/SQL programs.

On navigating back to Targets | Databases, if the database list on this page had been changed to a search list instead of a Load Map, and you were to drill down to the database in that list, the database home page would appear instead as seen in the following screenshot for the Oracle database:

On this database home page, some summary information (such as Up Time, Version, Total SGA, and Available Space) about the database is displayed as well as some performance information (such as Wait, Active Sessions, Host CPU, and the long-running sessions shown by SQL Monitoring).