Session Performance

Identify currently active sessions and review the SQL, wait state and session context associated with their activity.

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

Review session-level activity including elapsed activity, current SQL identifiers and wait information.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.last_call_et,
    s.event,
    s.wait_class
FROM v$session s
WHERE s.type = 'USER'
ORDER BY s.last_call_et DESC;

Identify sessions associated with high CPU consumption and correlate the session with its current SQL.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    ss.value AS cpu_used
FROM v$session s
JOIN v$sesstat ss
    ON ss.sid = s.sid
JOIN v$statname sn
    ON sn.statistic# = ss.statistic#
WHERE s.type = 'USER'
  AND sn.name = 'CPU used by this session'
ORDER BY ss.value DESC
FETCH FIRST 20 ROWS ONLY;

Identify sessions currently waiting and review the wait event, wait class and duration to determine where investigation should begin.

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

Use a session identifier to identify the SQL currently associated with that session and continue the investigation in V$SQL.

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    q.plan_hash_value,
    q.executions,
    q.elapsed_time,
    q.cpu_time,
    q.buffer_gets
FROM v$session s
LEFT JOIN v$sql q
    ON q.sql_id = s.sql_id
WHERE s.sid = &sid
  AND s.serial# = &serial;

Inspect detailed session context including client information, machine, module, program and current SQL identifier.

SELECT
    sid,
    serial#,
    username,
    status,
    osuser,
    machine,
    program,
    module,
    action,
    sql_id,
    prev_sql_id,
    event,
    wait_class
FROM v$session
WHERE sid = &sid
  AND serial# = &serial;