Monitoring Real-Time SQL Execution

posted Aug 2, 2011, 5:12 PM by Sachchida Ojha   [ updated Jan 28, 2012, 9:05 AM ]

Oracle 11gR1 introduced two new views V$SQL_MONITOR and V$SQL_MONITOR_PLAN to provide runtime execution statistics. This was part of feature called Real-Time SQL Monitoring which allows you to monitor sql in near real-time as the statement executes.

To recap, please find below description for both views

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. Timing information could be viewed by joining V$SQL_PLAN_MONITOR with V$ACTIVE_SESSION_HISTORY on SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, and SQL_PLAN_LINE_ID (simply named PLAN_LINE_ID in V$SQL_PLAN_MONITOR).

In 11gR1, you could use DBMS_SQLTUNE.REPORT_SQL_MONITOR to generate html/text reports . Click here to read more.

11gR2 Oracle Enterprise Manager provides a graphical interface which makes job easier.