Oracle 下检查当前 SQL 和等待事件

jiawang

-- 当前用于阻塞和等待会话的 SQL
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.status,
  s.event,
  s.wait_time,
  s.seconds_in_wait,
  s.sql_id,
  sq.sql_text
FROM v$session s, v$sqlarea sq
WHERE s.sql_address = sq.address(+)
  AND s.sql_hash_value = sq.hash_value(+)
  AND (s.blocking_session IS NOT NULL
       OR s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL))
ORDER BY s.blocking_session NULLS FIRST;


-- 与锁相关的等待事件
SELECT
  sid,
  seq#,
  event,
  wait_class,
  state,
  wait_time_micro/1000000 as wait_seconds,
  time_since_last_wait_micro/1000000 as since_last_wait_seconds
FROM v$session_wait
WHERE event LIKE '%enq%'
   OR event LIKE '%lock%'
   OR event LIKE '%busy%'
ORDER BY wait_time_micro DESC;

登录/注册后可看大图