Database Diagnostics

Review accumulated wait information for the instance. This provides a broad view of where database time has been spent and can help identify areas for further investigation.

SELECT
    event,
    wait_class,
    total_waits,
    ROUND(time_waited / 100, 2) AS time_waited_seconds,
    ROUND(
        CASE
            WHEN total_waits > 0
            THEN time_waited / total_waits / 100
        END,
        4
    ) AS avg_wait_seconds
FROM v$system_event
ORDER BY time_waited DESC
FETCH FIRST 30 ROWS ONLY;

Identify active sessions currently waiting and review the associated event, wait class and SQL ID.

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

Review sessions that have identified blocking sessions. Correlate the blocker and waiter with SQL and application information before taking operational action.

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

V$SESSION_LONGOPS can expose operations that have reported measurable progress. Review elapsed time and remaining work when investigating long-running database activity.

SELECT
    sid,
    serial#,
    opname,
    target,
    sofar,
    totalwork,
    units,
    elapsed_seconds,
    time_remaining,
    sql_id
FROM v$session_longops
WHERE totalwork > 0
  AND sofar < totalwork
ORDER BY elapsed_seconds DESC;

Summarize currently waiting sessions by wait class to get a quick indication of the types of activity represented in the current workload.

SELECT
    wait_class,
    COUNT(*) AS waiting_sessions
FROM v$session
WHERE status = 'ACTIVE'
  AND state = 'WAITING'
GROUP BY wait_class
ORDER BY waiting_sessions DESC;

Combine session activity, SQL identifiers and wait information to establish which sessions and SQL statements deserve deeper investigation.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.event,
    s.wait_class,
    s.state,
    s.seconds_in_wait,
    s.blocking_session
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY
    s.wait_class,
    s.seconds_in_wait DESC;