RAC Services

View services currently active on RAC instances and inspect their connection and load-balancing attributes.

SELECT
    inst_id,
    service_id,
    name,
    network_name,
    goal,
    clb_goal,
    blocked
FROM gv$active_services
ORDER BY name, inst_id;

Identify which RAC instances are currently hosting each active database service.

SELECT
    s.name AS service_name,
    i.inst_id,
    i.instance_name,
    i.host_name,
    s.blocked
FROM gv$active_services s
JOIN gv$instance i
    ON i.inst_id = s.inst_id
ORDER BY s.name, i.inst_id;

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

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

Review service names, network names, runtime load-balancing goals and HA notification settings.

SELECT
    service_id,
    name,
    network_name,
    goal,
    dtp,
    aq_ha_notification,
    clb_goal
FROM v$services
ORDER BY name;

Compare recent service-level call rate, CPU per call and database time per call.

SELECT
    service_name,
    begin_time,
    end_time,
    dbtimepercall,
    cpupercall,
    callspersec
FROM gv$servicemetric
WHERE group_id = 10
ORDER BY
    service_name,
    begin_time DESC
FETCH FIRST 20 ROWS ONLY;

Identify wait classes accumulating the most waits and wait time for each database service.

SELECT
    service_name,
    wait_class,
    total_waits,
    ROUND(time_waited / 100, 2) AS wait_seconds
FROM gv$service_wait_class
WHERE wait_class <> 'Idle'
ORDER BY
    time_waited DESC
FETCH FIRST 20 ROWS ONLY;