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;

Kiran P

Published on 05-Dec-2020 06:22:04

Previous Page Print Page