V$SESSION - Monitor Active Sessions, Blocking Chains & Wait Events

V$SESSION

Overview

V$SESSION is the most frequently queried dynamic performance view in Oracle, displaying one row for every session currently connected to the database instance. DBAs rely on it as the primary entry point for diagnosing performance problems, identifying blocking sessions, correlating foreground waits with SQL activity, and auditing connected users. It is often joined with V$SQL, V$PROCESS, V$LOCK, and V$WAIT_CHAINS to build a complete picture of session activity.

View Type: Dynamic Performance View Available Since: Oracle 7 Required Privileges: SELECT on V_$SESSION or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SID	NUMBER	Session identifier — unique within the instance for the lifetime of the session
SERIAL#	NUMBER	Serial number — increments each time the SID is reused, used together with SID to uniquely identify a session
USERNAME	VARCHAR2(128)	Oracle username; NULL for background processes
STATUS	VARCHAR2(8)	ACTIVE (currently executing or waiting on a non-idle event), INACTIVE (idle, waiting for work), KILLED, CACHED, or SNIPED
SQL_ID	VARCHAR2(13)	SQL_ID of the SQL statement currently being executed; NULL when idle
SQL_CHILD_NUMBER	NUMBER	Child cursor number of the current SQL statement
PREV_SQL_ID	VARCHAR2(13)	SQL_ID of the most recently executed SQL statement
EVENT	VARCHAR2(64)	Current wait event, or the last wait event if the session is on CPU
WAIT_CLASS	VARCHAR2(64)	Wait class of the current or last wait event (e.g., User I/O, Concurrency, Network)
SECONDS_IN_WAIT	NUMBER	Number of seconds the session has been waiting for the current event
STATE	VARCHAR2(19)	WAITING, WAITED SHORT TIME, WAITED KNOWN TIME, or WAITED UNKNOWN TIME
LAST_CALL_ET	NUMBER	Elapsed seconds since the session last became active (ACTIVE status) or last completed a call (INACTIVE status)
BLOCKING_SESSION	NUMBER	SID of the session blocking this session; NULL if not blocked
BLOCKING_SESSION_STATUS	VARCHAR2(11)	VALID (blocking session identified), NOT IN WAIT, UNKNOWN, GLOBAL, or NO HOLDER
MACHINE	VARCHAR2(64)	Client machine name
PROGRAM	VARCHAR2(48)	Client program name (e.g., sqlplus.exe, JDBC Thin Client)
MODULE	VARCHAR2(64)	Application module name set via DBMS_APPLICATION_INFO
ACTION	VARCHAR2(64)	Application action name set via DBMS_APPLICATION_INFO
SERVICE_NAME	VARCHAR2(64)	Service name the session connected through
LOGON_TIME	DATE	Time the session logged on
OSUSER	VARCHAR2(128)	Operating system username of the client
PROCESS	VARCHAR2(24)	Client OS process ID
PADDR	RAW(8)	Address of the process owning this session; joins to V$PROCESS.ADDR
ROW_WAIT_OBJ#	NUMBER	Object ID of the object the session is waiting to lock a row on
ROW_WAIT_ROW#	NUMBER	Row number the session is waiting for

Essential Queries

Basic Usage

List all non-background active sessions with their current SQL and wait event:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait    AS secs_waiting,
    s.last_call_et       AS secs_since_call,
    s.sql_id,
    s.machine,
    s.program,
    s.module
FROM
    v$session s
WHERE
    s.username IS NOT NULL
    AND s.status = 'ACTIVE'
ORDER BY
    s.last_call_et DESC;

Monitoring Query

Show all active sessions grouped by wait class with counts and average wait time, useful for a quick health snapshot:

SELECT
    wait_class,
    COUNT(*)                                    AS session_count,
    ROUND(AVG(seconds_in_wait), 2)              AS avg_secs_waiting,
    MAX(seconds_in_wait)                        AS max_secs_waiting,
    COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'INACTIVE' THEN 1 END) AS inactive_count
FROM
    v$session
WHERE
    username IS NOT NULL
GROUP BY
    wait_class
ORDER BY
    session_count DESC;

Combined with Other Views

Join V$SESSION with V$SQL and V$PROCESS to get OS PID, session details, and the full SQL text for all active sessions:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait                           AS secs_waiting,
    s.last_call_et                              AS secs_since_call,
    p.spid                                      AS os_pid,
    s.machine,
    s.program,
    s.module,
    s.action,
    SUBSTR(q.sql_text, 1, 120)                  AS sql_text
FROM
    v$session   s
    JOIN v$process p ON p.addr = s.paddr
    LEFT JOIN v$sql     q ON q.sql_id = s.sql_id
                          AND q.child_number = s.sql_child_number
WHERE
    s.username IS NOT NULL
    AND s.status = 'ACTIVE'
ORDER BY
    s.last_call_et DESC;

Blocking Chain Analysis

Identify the full blocking chain — who is blocking whom — including the root blocker:

SELECT
    LPAD(' ', 2 * LEVEL)
    || s.sid                                    AS session_tree,
    s.serial#,
    s.username,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait                           AS secs_waiting,
    s.blocking_session                          AS blocked_by,
    s.machine,
    s.module,
    SUBSTR(q.sql_text, 1, 100)                  AS current_sql
FROM
    v$session s
    LEFT JOIN v$sql q ON q.sql_id = s.sql_id
                      AND q.child_number = s.sql_child_number
START WITH
    s.blocking_session IS NULL
    AND s.sid IN (
        SELECT blocking_session
        FROM   v$session
        WHERE  blocking_session IS NOT NULL
    )
CONNECT BY PRIOR
    s.sid = s.blocking_session
ORDER SIBLINGS BY
    s.sid;

Long-Running Sessions

Find sessions that have been active for more than 5 minutes, a common first step when users report slow queries:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.last_call_et                              AS secs_active,
    NUMTODSINTERVAL(s.last_call_et, 'SECOND')   AS duration,
    s.event,
    s.wait_class,
    s.sql_id,
    s.machine,
    s.program,
    s.module,
    s.action,
    SUBSTR(q.sql_text, 1, 150)                  AS sql_text
FROM
    v$session   s
    LEFT JOIN v$sql q ON q.sql_id = s.sql_id
                      AND q.child_number = s.sql_child_number
WHERE
    s.username IS NOT NULL
    AND s.status   = 'ACTIVE'
    AND s.last_call_et > 300
ORDER BY
    s.last_call_et DESC;

Session Resource Usage

Join with V$SESSTAT and V$STATNAME to show resource consumption (logical reads, physical reads, redo) per session:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    SUM(CASE WHEN st.name = 'session logical reads'      THEN ss.value END) AS logical_reads,
    SUM(CASE WHEN st.name = 'physical reads'             THEN ss.value END) AS physical_reads,
    SUM(CASE WHEN st.name = 'redo size'                  THEN ss.value END) AS redo_bytes,
    SUM(CASE WHEN st.name = 'parse count (total)'        THEN ss.value END) AS parses,
    SUM(CASE WHEN st.name = 'execute count'              THEN ss.value END) AS executions,
    SUM(CASE WHEN st.name = 'user commits'               THEN ss.value END) AS commits
FROM
    v$session   s
    JOIN v$sesstat  ss ON ss.sid = s.sid
    JOIN v$statname st ON st.statistic# = ss.statistic#
WHERE
    s.username IS NOT NULL
    AND st.name IN (
        'session logical reads',
        'physical reads',
        'redo size',
        'parse count (total)',
        'execute count',
        'user commits'
    )
GROUP BY
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program
ORDER BY
    logical_reads DESC NULLS LAST;

Common Use Cases

• Blocking lock investigation — Quickly find who is blocking whom using BLOCKING_SESSION, then decide whether to kill the blocker or wait
• Identifying runaway queries — Sort by LAST_CALL_ET DESC to find sessions that have been executing far longer than expected
• Wait event triage — Grouping by WAIT_CLASS gives an instant summary of where database time is being spent across all sessions
• Application connection auditing — Filter by MACHINE, PROGRAM, or MODULE to see how many connections a given application has open and what they are doing
• Session kill preparation — Retrieve SID and SERIAL# together before issuing ALTER SYSTEM KILL SESSION to avoid accidentally killing the wrong session after SID reuse
• Service-level monitoring — Filter by SERVICE_NAME to isolate sessions belonging to a specific application tier or pluggable database service

Related Views

• V$SQL — Look up the full SQL text and execution statistics for any SQL_ID found in V$SESSION
• V$SQL_PLAN — Retrieve the cached execution plan for the SQL_ID currently running in a session
• V$SYSSTAT — System-wide cumulative statistics that provide the aggregate picture behind individual session activity
• V$SYSTEM_EVENT — System-wide wait event totals that complement the per-session wait detail in V$SESSION
• V$LOCK — Shows locks held and requested; join on SID to understand exactly what object a blocking session holds
• V$WAIT_CHAINS — Pre-built blocking chain view available from Oracle 11g; simpler than the hierarchical V$SESSION query above

Version Notes

• Oracle 10g: Added MODULE, ACTION, CLIENT_IDENTIFIER, and SERVICE_NAME columns; DBMS_APPLICATION_INFO integration became standard practice
• Oracle 11g: BLOCKING_SESSION_STATUS column added; V$WAIT_CHAINS introduced as a companion view for blocking chain analysis
• Oracle 12c (Multitenant): CON_ID column added to identify the container (CDB root = 1, PDB = 2+); queries from the CDB root see all PDB sessions
• Oracle 19c: No structural changes; execution plan adaptive features can affect SQL_ID stability within a session
• Oracle 21c / 23ai: CON_NAME column added in some editions for direct PDB name lookup; RESOURCE_CONSUMER_GROUP available for Resource Manager integration