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;
Смотрите видео sp_whoisactive for Oracle онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Ken Kim 26 Март 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 216 раз и оно понравилось 3 людям.