Oracle Parallel Query Diagnosis

Purpose

Identifies parallel query activity and performance issues:

1. Currently active parallel queries and slave counts
2. Sessions waiting on parallel query operations
3. Parallel query parameter settings
4. Object-level parallel degree configuration

Required Permissions

SELECT_CATALOG_ROLE or SELECT on V$SESSION, V$PX_SESSION, V$SQL, V$PARAMETER.

Risk Level

Low. Read-only. No changes to parallelism configuration.

Script

-- ============================================================
-- Oracle Parallel Query Diagnosis
-- Permissions: SELECT_CATALOG_ROLE
-- Risk: Read-only
-- ============================================================

SET LINESIZE 200
SET PAGESIZE 100

-- 1. Active parallel query coordinators and slave counts
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    s.sql_id,
    s.last_call_et             AS seconds_active,
    p.degree                   AS requested_degree,
    p.req_degree               AS actual_degree,
    COUNT(ps.sid)              AS slave_count
FROM v$session          s
JOIN v$px_session       p  ON s.sid = p.sid AND p.qcsid = s.sid
LEFT JOIN v$px_session  ps ON ps.qcsid = s.sid AND ps.sid != s.sid
WHERE s.status = 'ACTIVE'
GROUP BY
    s.sid, s.serial#, s.username, s.program,
    s.status, s.sql_id, s.last_call_et,
    p.degree, p.req_degree
ORDER BY slave_count DESC;

-- 2. Parallel query slaves currently active
SELECT
    qcsid             AS coordinator_sid,
    sid               AS slave_sid,
    inst_id,
    server_group,
    server_set,
    server#,
    degree,
    req_degree
FROM gv$px_session
ORDER BY qcsid, sid;

-- 3. Sessions waiting on PQ-related events
SELECT
    sid,
    serial#,
    username,
    event,
    wait_class,
    seconds_in_wait,
    sql_id
FROM v$session
WHERE wait_class IN ('Parallel Execution', 'Other')
  AND state = 'WAITING'
  AND event LIKE '%PX%'
ORDER BY seconds_in_wait DESC;

-- 4. Parallel query parameter settings
SELECT
    name,
    value,
    description
FROM v$parameter
WHERE name IN (
    'parallel_max_servers',
    'parallel_min_servers',
    'parallel_degree_policy',
    'parallel_degree_limit',
    'parallel_threads_per_cpu',
    'cpu_count',
    'parallel_adaptive_multi_user',
    'parallel_execution_message_size'
)
ORDER BY name;

-- 5. Objects with non-default parallel degree
SELECT
    owner,
    object_name,
    object_type,
    degree
FROM dba_objects
WHERE degree NOT IN ('1', '0', ' 1')
  AND object_type IN ('TABLE', 'INDEX')
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, object_name;

-- 6. SQL statements currently consuming the most parallel servers
SELECT
    sq.sql_id,
    sq.sql_text,
    sq.parallel_servers_target,
    sq.parallel_servers_allocated,
    sq.elapsed_time / 1000000 AS elapsed_seconds,
    sq.executions
FROM v$sql sq
WHERE sq.parallel_servers_allocated > 0
ORDER BY sq.parallel_servers_allocated DESC
FETCH FIRST 10 ROWS ONLY;

Interpreting Results

High slave counts: A coordinator running with more slaves than parallel_max_servers / (number of active PQ queries) indicates the system is over-allocating parallelism. Reduce the table or hint-level DOP, or adjust parallel_max_servers.

PQ waits: PX Deq: Execute Reply and PX Deq: Table Q Normal are normal parallel execution message passing waits. Excessive time in PX Deq: Slave Session Stats may indicate slave session overhead.

Objects with forced parallelism: ALTER TABLE ... PARALLEL n persists in the data dictionary and applies to all queries against the table, regardless of query structure. This is rarely appropriate in OLTP environments.

Safe Execution Guidance

Run in SQL*Plus or SQLcl. Safe at any time. Run during an active slow PQ incident to capture slave counts and coordinator state.

Rollback Steps

Not applicable — read-only script.