RAC Session Distribution

See how current database sessions are distributed across services and RAC instances.

SELECT
    inst_id,
    service_name,
    COUNT(*) AS session_count
FROM gv$session
WHERE service_name IS NOT NULL
GROUP BY
    inst_id,
    service_name
ORDER BY
    service_name,
    inst_id;

Compare currently active sessions across RAC instances to identify where foreground activity is running.

SELECT
    inst_id,
    COUNT(*) AS active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
GROUP BY inst_id
ORDER BY active_sessions DESC;

Identify services with the largest number of active sessions across the cluster.

SELECT
    service_name,
    COUNT(*) AS active_sessions
FROM gv$session
WHERE status = 'ACTIVE'
  AND service_name IS NOT NULL
GROUP BY service_name
ORDER BY active_sessions DESC
FETCH FIRST 20 ROWS ONLY;

Summarize active, inactive and total sessions for each RAC instance.

SELECT
    inst_id,
    COUNT(*) AS total_sessions,
    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END)
        AS active_sessions,
    SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END)
        AS inactive_sessions
FROM gv$session
GROUP BY inst_id
ORDER BY inst_id;

Identify which client programs are generating sessions through each RAC service.

SELECT
    service_name,
    program,
    COUNT(*) AS session_count
FROM gv$session
WHERE service_name IS NOT NULL
GROUP BY
    service_name,
    program
ORDER BY session_count DESC
FETCH FIRST 20 ROWS ONLY;

Inspect active sessions that are currently waiting and identify the instance, service and wait event.

SELECT
    inst_id,
    service_name,
    sid,
    serial#,
    username,
    event,
    wait_class,
    seconds_in_wait
FROM gv$session
WHERE status = 'ACTIVE'
  AND wait_class <> 'Idle'
ORDER BY seconds_in_wait DESC
FETCH FIRST 20 ROWS ONLY;