Quickly identifying real time SQL performance issues has been one of the biggest headaches for Oracle DBAs. Often DBAs are asked, why has the system slowed down? Why is my query taking so long to run? Along with other performance related questions. The challenge for the DBA is to identify the statement(s) that are in question, and be able to quickly see what is actually going on, and then take the appropriate action quickly.
In the 11g release of their database, Oracle introduced a new feature that dramatically simplifies the detection and analysis of long running queries. The focus of Real Time SQL Monitoring is both parallel queries and resource intensive long running queries. By default, in Oracle Database 11g, any SQL statement that uses more than 5 seconds of CPU and/or IO time or any SQL statement that runs in parallel is automatically monitored.
The easiest way to see and work with the new feature is by using the Oracle Enterprise Manager performance page links into the graphical, interactive pages for SQL Monitoring. There are also v$ performance views available to provide the same information through manual queries.
This is all accomplished with new out-of-the-box fine-grained SQL statistics that are tracked with no performance cost to production systems. The new statistics include CPU time, elapsed time, IO waits, reads, and writes along with other wait information.
New V$ Views
To support this new feature, Oracle introduced two new V$ views. They would be used along with existing views such as V$SQL, V$ACTIVE_SESSION_HISTORY, V$SESSION_LONGOPS, V$SQL_PLAN.
This view actually contains a subset of what is in V$SQL, however, rather than being summarized over several executions of the statement, there is one row for each execution of each individual statement.
There are three columns that make up the key for this view (known as the execution key) which are:
SQL_ID - the SQL identifier
SQL_EXEC_START - the starting timestamp of the execution
SQL_EXEC_ID - a generated ID to guarantee uniqueness of the execution key
A new row is added to this view as soon as a query is selected for real-time monitoring. All of the gathered statistics (such as CPU time, elapsed time, IO waits, etc. are updated once per second while the statement runs (hence "real-time").
Once the statement finishes, the statistics are kept for at least one full minute. Like most V$ views, the underlying data is actually stored in the SGA, however, it is not aged out in the same manner as the data in the V$SQL view (cursor aging). The data is kept in a size-constrained memory buffer and it will eventually be overwritten by newer statements that are also monitored. This size constraint along with a built-in 5 minute retention guarantee does mean that there is a very small possibility that not all eligible statements will be monitored in an exceptionally busy system with lots of long running SQL commands.
Parallel SQL statements are all monitored, and there will be a row in V$SQL_MONITOR for each parallel process running the statement and one entry for the parallel process coordinator.
There is also a GV$ view for RAC systems.
In addition to the statistics that are kept in the V$SQL_MONITORING view, a second set of data is stored in the V$SQL_PLAN_MONITOR view. This view includes similar monitoring statistics for each step in the execution plan for the monitored statements. These statistics are also updated every second and will persist for at least 5 minutes after the statement has completed.
There will be multiple rows in V$SQL_PLAN_MONITOR for each statement, one for each step in the execution plan.
For Parallel SQL, there will be corresponding multiple entries in this view for each parallel execution process.
There is also a GV$ view for RAC systems.
Using the SQL Monitoring Pages in Enterprise Manager
The easiest way to see the new SQL Monitoring feature is to use the EM pages. From the database home page in EM, select the Performance Pages. Make sure that one of the Real Time Refresh options has been selected for the View Data option (top right hand side).
Enterprise Manager Performance Page
At the bottom of the performance page you will see the link for SQL Monitoring under the section titled "Additional Monitoring Links" (last link last column on the right).
Additional Monitoring Links
That will take you to the Monitored SQL Executions page
Monitored SQL Executions Page
This page is the initial summary page, which reflects the main statistics for statements that have been monitored. The rows are displayed in order from the most recent to the oldest. From here you can see if the statement is still executing (spinning wheel under status), if it completed successfully or encountered an error. Then you see information like the duration, SQL ID, user, parallel processes, total database time, IO requests, start time, end time and the first few characters of the statement being executed.
To see the detailed statistical information for any of the monitored statements, simply click on the value in the SQL ID column for the statement.
Detailed Statistical Information
From the Monitored SQL Execution Details page you can get a much deeper breakdown of what is truly going on behind the statement, all displayed in a user-friendly graphical layout.
At the top right side of this page, there are three buttons Save, Mail and View Report. These were added in Oracle Database 11g R2 as a way to be able to share this information with someone who does not have access to EM. The output is an HTLM file that can be opened in a browser.
On this page, simply hover over links or bars in the bar charts to see details such as the statement being executed, # of parallel processes, total execution time (or execution time so far), number of rows fetched (total or # so far).
In the table at the bottom of this page you can view details such as the execution plan - which will actually show you exactly what step is being done if the statement is still running along with statistics for each step in progress. This is the default tab for that bottom table.
The other tabs are Activity and Parallel. The Parallel tab provides a breakdown of the different parallel processes, while the Activity tab displays the Active Session History data for the statement.
In addition to all of the detailed information provided on these pages, clicking on the SQL ID link from the Execution Details page takes you to the Top Activity monitoring page for the statement.
Top Activity Monitoring Page
From here there a DBA has access to features such as overall Activity, Plan Control, Tuning History (and even a link that would take you to the SQL Monitoring page if you had come to this page first).
SQL Monitoring is easily one of my favorite new features in Oracle Database 11g. It provides powerful functionality to help DBAs detect and diagnose issues with long running SQL statements and the easy-to-use EM interface makes it even better.