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_MONITOR is a replacement for v$session_longops
Statistics collection is now automatic in 11g with v$sql_monitor.
With V$SQL_MONITOR, Oracle 11g will automatically collect the execution plans for these long-running statements. These statements are visible within V$SQL_PLAN_MONITOR. The V$SQL_MONITOR view will also display execution statistics.
Each time the execution of a SQL statement is monitored, an entry is created in V$SQL_MONITOR.
The refresh rate for the statistics in v$sql_monitor is once per second, which is very close to real time.
The retention for v$sql_monitor is designed such that the SQL monitoring information is not deleted immediately after the execution is completed. In fact, v$sql_monitor retains the information for at least one minute; however, v$sql_monitor continues to monitor new statements, so entries will be deleted in order to reclaim the space.
The important execution statistics inside v$sql_monitor include:
•v$sql_monitor. elapsed_time
•v$sql_monitor. cpu_time
•v$sql_monitor. fetches
•v$sql_monitor. buffer_gets
•v$sql_monitor. disk_reads
•v$sql_monitor. direct_writes
•v$sql_monitor. application_wait_time
•v$sql_monitor. concurrency_wait_time
•v$sql_monitor. cluster_wait_time
•v$sql_monitor. user_io_wait_time
•v$sql_monitor. plsql_exec_time
•v$sql_monitor. java_exec_time
Of course, like many of Oracle's most appealing features, v$sql_monitor is available via a license for the Oracle Diagnostic & Tuning Pack.
V$SQL_MONITOR V$SQL_PLAN_MONITOR