How to monitor real time SQL execution statistics in Oracle?


You want to monitor currently executing SQL statistics in Oracle.


If your database is Oracle Database 11g, you can use the following query to select from the “V$SQL_MONITOR” to monitor the near real time resource consumption of SQL queries.

The statistics in “V$SQL_MONITOR” are updated every second. This helps us to view the resource consumption as it updates. These statistics are gathered by default when a SQL statement runs in parallel or consumes more than 5 seconds of CPU or I/O time.

The “V$SQL_MONITOR" view includes a subset of statistics contained in the "V$SQL”, “V$SQLAREA", and "V$SQLSTATS” views.

The “V$SQL_MONITOR" view displays real-time statistics for each execution of a resource-intensive SQL statement, whereas "V$SQL”, “V$SQLAREA", and "V$SQLSTATS” contain over-all sets of statistics over multiple executions of a SQL statement.


select * from ( select  a.sid session_id ,a.sql_id ,a.status ,a.cpu_time/1000000 cpu_sec ,a.buffer_gets ,a.disk_reads ,b.sql_text sql_text from v$sql_monitor a     ,v$sql b where a.sql_id = b.sql_id order by a.cpu_time desc) where rownum <=10;