sp_whoisactive for Oracle
sp_whoisactive is used to monitor running queries in SQL Server databases. I have created an Oracle version to help identify TOP SQL consuming most resources, long running queries, and blocking sessions. In this demo, I have created a blocking session and go over the query output details.
I use this script daily on Production and non-Production environments and it works equally well on both single instance and RAC environments.
--sp_whoisactive for Oracle (single and RAC)
select s.inst_id, s.sid, s.serial#
, NVL(s.sql_id, s.prev_sql_id) sql_id, s.sql_child_number child#, s.sql_exec_start
, s.SECONDS_IN_WAIT, s.state, s.blocking_session blocking_session_SID, w.EVENT, s.WAIT_CLASS SESS_WAIT_CLASS, w.WAIT_CLASS RESOURCE_WAIT_CLASS
, sql.executions, ROUND(sql.elapsed_time/DECODE(executions,0,1,executions)/1000,1) avg_elapsed_ms
--, sql.sorts, sql.end_of_fetch_count, sql.rows_processed, sql.first_load_time
, ROUND(sql.cpu_time/DECODE(sql.executions,0,1,executions),1) avg_cpu_time
, ROUND(sql.disk_reads/DECODE(sql.executions,0,1,executions),1) avg_disk_reads
, ROUND(sql.buffer_gets/DECODE(sql.executions,0,1,executions),1) avg_buffer_reads
, ROUND(sql.concurrency_wait_time/DECODE(sql.executions,0,1,executions)/1000,1) avg_concur_wait_ms
, ROUND(sql.cluster_wait_time/DECODE(sql.executions,0,1,executions)/1000,1) avg_clstr_wait_ms
--, sql.user_io_wait_time, sql.optimizer_mode, sql.optimizer_cost, s.TERMINAL
--, s.username, s.osuser, s.machine, s.program
, sql.sql_text --, sql.sql_fulltext
from gv$session s
left join gv$sqlarea sql on s.inst_id = sql.inst_id and NVL(s.sql_id, s.prev_sql_id) = sql.sql_id
join gv$session_wait w on w.inst_id = s.inst_id and w.sid=s.sid
join gv$process p on s.paddr= p.addr and p.inst_id = s.inst_id
where 1=1
AND (s.WAIT_CLASS NOT IN ('Idle') OR s.SID IN (SELECT BLOCKING_SESSION FROM gv$session WHERE BLOCKING_SESSION IS NOT NULL)) --active sessions + BLOCKING; not to be confused with w.WAIT_CLASS
AND s.type NOT IN ('BACKGROUND')
ORDER BY seconds_in_wait desc, avg_elapsed_ms DESC;
Watch video sp_whoisactive for Oracle online without registration, duration hours minute second in high quality. This video was added by user Ken Kim 26 March 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 216 once and liked it 3 people.