Session Activity

A simple starting point for seeing active user sessions and the SQL identifiers associated with their current activity.

SELECT
    sid,
    serial#,
    username,
    status,
    machine,
    program,
    sql_id,
    event,
    wait_class
FROM v$session
WHERE status = 'ACTIVE'
ORDER BY sid;

Use this view when you need more context around a particular session, including its user, client information, SQL and current wait event.

SELECT
    sid,
    serial#,
    username,
    status,
    machine,
    program,
    module,
    action,
    sql_id,
    event,
    wait_class,
    state,
    seconds_in_wait
FROM v$session
WHERE username IS NOT NULL
ORDER BY status DESC, sid;

Waiting sessions can provide an important clue during performance investigation. Review the event together with the wait class and SQL identifier rather than treating the wait event as the complete root cause.

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

This query helps identify sessions that are currently blocking another session. Use the blocking information as the starting point for further investigation.

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;