How to monitor real time SQL execution statistics in Oracle?
Problem:
You want to monitor currently executing SQL statistics in Oracle.
Solution
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.
Example
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;
Published on 05-Dec-2020 10:52:04
