SQL Monitoring
The real-time SQL monitoring feature on Oracle Database 11g enables you to monitor the performance of SQL statements while they are executing.
The real-time SQL monitoring feature is enabled by default when the STATISTICS_LEVEL initialization parameter is either set to ALL or to TYPICAL, which is the default value. Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to
SQL Monitoring (continued)
You can monitor the statistics for SQL command execution using the V$SQL_MONITOR and
V$SQL_PLAN_MONITORviews.
After monitoring is initiated, an entry is added to the V$SQL_MONITOR dynamic performance view. This entry tracks key performance metrics collected for the execution, including the elapsed time, CPU time, number of reads and writes, I/O wait time, and various other wait times. These statistics are refreshed in near real time as the command executes, generally once every second.
After the execution ends, monitoring information is not deleted immediately, but is kept in the V$SQL_MONITORview for at least one minute. The entry is eventually deleted so its space can be reclaimed as new commands are monitored.
The V$SQL_MONITOR and V$SQL_PLAN_MONITOR views can be used in conjunction with the following views to get additional information about the execution that is monitored:
V$SQL, V$SQL_PLAN, V$ACTIVE_SESSION_HISTORY, V$SESSION_LONGOPS, and V$SESSION
SQL Monitoring in Oracle Database 11g Release 2
In Oracle Database 11g Release 2, you can access the SQL Monitoring feature in Enterprise Manager Database Control by clicking the Performance tab. You can choose from Real Time and Historical Settings. Scroll down to the Additional Monitoring Links area and click SQL Monitoring, as shown in the slide.
Note: The COMPATIBLE initialization parameter must be set to 11.2.0.0 (or higher) to use this feature.
SQL Monitoring with Enterprise Manager Database Control
This slide shows just one possible work flow in Enterprise Manager Database Control. You can navigate through the following pages:
• Monitored SQL Execution • SQL Execution Details • Session Details
Monitored SQL Executions
When you move the cursor over the values or symbols of each SQL execution, a relevant hint appears. The slide shows the actual SQL command being executed with the cursor on the SQL ID link.
When you click the link that shows the SQL ID, you navigate to the Monitored SQL Execution Details page, as shown in the following slide.
SQL Monitoring List
This slide shows another example of monitored SQL executions.
1. In the top left section, you see for each long-running SQL, the completion status (which can be executing, done, or error), execution duration (wall clock time), SQL ID, and Session ID where the SQL was executed.
2. Here you see the database time by wait class, and I/O read and write operations.
3. This detail shows you the degree of parallelism: the number of instances that are involved in this parallel execution.
Monitored SQL Execution Details
The details of the SQL execution are displayed on three different tabbed pages: • Plan Statistics, as shown in the bottom part of the slide
• Parallel, displaying the distribution of work across the parallel servers (not part of the example in the slide)
• Activity, displaying ASH data on a time line
SQL Execution Details for Parallel Queries
Details for Parallel Execution
This slide shows you monitored SQL execution details for parallel queries (on the Parallel tabbed page).
Activity Details for Parallel Execution
This Activity tabbed page displays the activity graphs for parallel queries. You see the oscillation of CPU and direct reads over time.
Viewing Session Details
This page shows you the session details for the MONI user, divided into the following sections: • Server • Client • Application • Contention • Wait • Other
SQL Details
Viewing the SQL Monitoring Report
The SQL Monitoring Report shows the same information as the previous slides, but this time in a textual, rather than a graphic way. It begins with the SQL Text, followed by global information, and then the SQL Plan Monitoring details, which also indicates the current operation with an arrow. When you see the new Save and Mail buttons, you can also save the report in HTML format and email the Active Report—for example, to a SQL Tuning expert, if your organization has such a division of work.
