Wait Events

Review wait events accumulating time across the database instance. Idle waits are excluded from the result.

SELECT
    event,
    wait_class,
    total_waits,
    ROUND(time_waited / 100, 2) AS time_waited_seconds
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC;

Identify active sessions that are currently waiting and inspect the event, wait class and duration.

SELECT
    sid,
    serial#,
    username,
    status,
    event,
    wait_class,
    seconds_in_wait,
    state
FROM v$session
WHERE status = 'ACTIVE'
  AND wait_class <> 'Idle'
ORDER BY seconds_in_wait DESC;

Group system wait activity into broader wait classes to understand the type of database pressure involved.

SELECT
    wait_class,
    SUM(total_waits) AS total_waits,
    ROUND(SUM(time_waited) / 100, 2) AS time_waited_seconds
FROM v$system_event
WHERE wait_class <> 'Idle'
GROUP BY wait_class
ORDER BY time_waited_seconds DESC;

Find sessions with significant accumulated wait time and use them as a starting point for deeper investigation.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.event,
    s.wait_class,
    ROUND(se.time_waited / 100, 2) AS time_waited_seconds
FROM v$session_event se
JOIN v$session s
    ON s.sid = se.sid
WHERE se.wait_class <> 'Idle'
ORDER BY se.time_waited DESC
FETCH FIRST 20 ROWS ONLY;

Identify sessions waiting on another session and inspect the blocking relationship during concurrency investigation.

SELECT
    sid,
    serial#,
    username,
    status,
    event,
    wait_class,
    blocking_session,
    blocking_session_status,
    seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

Combine the wait event with the affected session and SQL identifier before moving deeper into the investigation.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.state
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.wait_class <> 'Idle'
ORDER BY s.seconds_in_wait DESC;