Oracle Parallel Query Waits - PX Deq & Downgrade Analysis

Oracle Parallel Query Waits: PX Deq and Downgrade Analysis

Overview

Oracle parallel query (PQ) distributes a SQL operation across multiple server processes — called PX servers (parallel execution servers) — to complete large operations faster by using more CPU and I/O bandwidth simultaneously. When parallel query runs correctly, it provides dramatic performance improvements for large table scans, hash joins, sorts, and aggregations.

However, parallel query introduces a set of wait events that are unique to its coordination overhead. These events appear when:

• PX server processes are being allocated or are communicating
• A parallel query cannot obtain its requested Degree of Parallelism (DOP) and is downgraded or serialized
• PX server processes are waiting for work from the query coordinator or other PX processes

The most important parallel query wait events are:

PX Deq: reap credit — A PX server or the query coordinator is waiting to acknowledge completion of a message dequeue operation. This is coordination overhead between parallel processes.

PX Deq: Slave Session Stats — The query coordinator is waiting for PX slaves to report their session statistics after the parallel operation completes. Elevated time here indicates many parallel operations completing, each generating coordinator overhead.

PX Deq: Execute Reply — A PX slave is waiting to receive an execution reply from another PX server (parallel-to-parallel communication, e.g., in a hash join between two parallel streams).

PX Deq Credit: send blkd — A PX process is blocked trying to send data to its consumer because the consumer’s input queue is full. This indicates unbalanced data distribution between producers and consumers.

enq: PS - contention — Parallel server process allocation: there are not enough PX servers available and sessions are contending for the limited pool.

OS Thread startup — On platforms that use OS threads for parallel execution, this event fires when a new thread needs to be created for a parallel operation. Thread creation latency appears here.

Understanding which of these events is dominant, and at what average wait times, guides the correct resolution.

When This Wait Is a Problem

Acceptable: Brief PX Deq: reap credit and PX Deq: Slave Session Stats waits are normal parallel query overhead. They should be present but small relative to the actual query execution events.

Investigate when:

• enq: PS - contention appears in AWR top waits — parallel server pool is exhausted
• PX Deq Credit: send blkd is elevated — data distribution skew or buffer sizing issue
• Actual DOP in V$PQ_SESSTAT or V$SQL consistently lower than requested DOP — parallel downgrade is occurring
• Parallel operations are taking longer than expected despite no I/O bottleneck
• PARALLEL_MAX_SERVERS has been hit (check V$SYSSTAT “parallel operations not downgraded”)

Critical: Parallel downgrade (where Oracle reduces the DOP from the requested value) can silently cause a 4x query that was supposed to run in 30 seconds to run in 2 minutes. The query finishes without error, but the SLA is missed. Detecting downgrade requires proactive monitoring.

Diagnostic Queries

1. Active Parallel Query Sessions and Their DOP

-- Active parallel query operations: coordinator and PX slave sessions
SELECT
    qc.sid AS coordinator_sid,
    qc.serial# AS coordinator_serial,
    qc.username,
    qc.program,
    qc.sql_id,
    qc.event AS coordinator_event,
    COUNT(px.sid) AS active_px_slaves,
    -- Requested vs actual DOP
    MAX(px.server_group) AS server_group,
    MAX(px.server_set) AS server_set
FROM
    v$session qc
    JOIN v$px_session px ON qc.sid = px.qcsid
WHERE
    qc.status = 'ACTIVE'
GROUP BY
    qc.sid, qc.serial#, qc.username, qc.program, qc.sql_id, qc.event
ORDER BY
    active_px_slaves DESC;

2. V$PX_PROCESS — All PX Server Processes and Their State

-- All PX server processes: busy vs idle
SELECT
    server_name,
    status,
    sid,
    serial#,
    pid,
    spid,
    sql_id,
    last_sql_active_time
FROM
    v$px_process
ORDER BY
    status DESC, server_name;

3. PX Server Pool Utilization — Detect Pool Exhaustion

-- Parallel server pool statistics
SELECT
    statistic,
    value
FROM
    v$pq_sysstat
WHERE
    statistic IN (
        'Servers Busy',
        'Servers Idle',
        'Servers Started',
        'Servers Shutdown',
        'Servers Highwater',
        'Queries Initiated',
        'DFO Trees',
        'Local Msgs Sent',
        'Distr Msgs Sent',
        'Sessions Active'
    )
ORDER BY
    statistic;

4. V$SYSTEM_EVENT — All Parallel Query Wait Events

-- Parallel query wait events — comprehensive view
SELECT
    event,
    wait_class,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2) AS total_wait_secs,
    ROUND(average_wait / 100, 4) AS avg_wait_secs,
    ROUND(average_wait / 100 * 1000, 2) AS avg_wait_ms
FROM
    v$system_event
WHERE
    event LIKE 'PX%'
    OR event LIKE 'enq: PS%'
    OR event LIKE 'OS thread startup%'
    OR event = 'wait for unread message on broadcast channel'
ORDER BY
    total_wait_secs DESC;

5. Detect Parallel Downgrade — Requested vs Actual DOP

-- SQL statements that were downgraded (requested DOP > actual DOP)
SELECT
    sql_id,
    sql_text,
    px_servers_executions,
    px_servers_max,
    executions,
    -- Calculate average actual DOP per execution
    CASE
        WHEN executions > 0 AND px_servers_executions > 0
        THEN ROUND(px_servers_executions / executions, 1)
        ELSE 0
    END AS avg_actual_px_servers,
    last_active_time,
    parsing_schema_name
FROM
    v$sql
WHERE
    px_servers_executions > 0
    AND executions > 0
ORDER BY
    px_servers_executions DESC
FETCH FIRST 30 ROWS ONLY;

6. V$PQ_SESSTAT — Per-Session Parallel Query Statistics

-- Parallel query stats for current session (or join to V$SESSION for all)
SELECT
    s.sid,
    s.username,
    s.program,
    pq.statistic,
    pq.last_query AS last_query_value,
    pq.session_total AS session_total_value
FROM
    v$pq_sesstat pq
    JOIN v$session s ON pq.sid = s.sid
WHERE
    s.username IS NOT NULL
    AND pq.last_query > 0
ORDER BY
    s.sid, pq.statistic;

7. ASH — Historical Parallel Query Wait Analysis

-- Historical parallel query waits from ASH
-- Identify when downgrade or coordination overhead was highest
SELECT
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI') AS sample_minute,
    ash.event,
    COUNT(*) AS ash_samples,
    COUNT(DISTINCT ash.session_id) AS sessions,
    COUNT(DISTINCT ash.sql_id) AS distinct_sql_statements
FROM
    v$active_session_history ash
WHERE
    (
        ash.event LIKE 'PX%'
        OR ash.event LIKE 'enq: PS%'
        OR ash.event = 'OS thread startup'
    )
    AND ash.sample_time > SYSDATE - 1
GROUP BY
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI'),
    ash.event
ORDER BY
    sample_minute DESC, ash_samples DESC;

8. PARALLEL_MAX_SERVERS Setting and Current Usage

-- Parallel configuration parameters
SELECT name, value, description
FROM v$parameter
WHERE name IN (
    'parallel_max_servers',
    'parallel_min_servers',
    'parallel_degree_policy',
    'parallel_degree_limit',
    'parallel_servers_target',
    'parallel_min_time_threshold',
    'parallel_force_local',
    'cpu_count'
)
ORDER BY name;

Root Causes

1. PX Server Pool Exhaustion (enq: PS - contention)

When all available PX server processes are in use and a new parallel query requests additional servers, the query coordinator must either wait for servers to become free or accept downgrade to a lower DOP. enq: PS - contention fires when sessions wait for PX server processes from the pool.

PARALLEL_MAX_SERVERS defines the ceiling. If Servers Busy in V$PQ_SYSSTAT consistently equals PARALLEL_MAX_SERVERS, the pool is exhausted. Increasing PARALLEL_MAX_SERVERS allows more concurrent parallelism but consumes more memory and CPU.

On systems with PARALLEL_DEGREE_POLICY = AUTO or ADAPTIVE, Oracle manages DOP dynamically and may throttle individual queries to protect system resources.

2. Parallel Downgrade (Silent Performance Degradation)

When Oracle cannot provide the full requested DOP due to pool exhaustion or resource limits, it downgrades the query to a lower DOP. The query runs with fewer PX servers than expected — potentially much fewer. For example, a query with PARALLEL 8 hint may run with DOP 2 because only 2 PX servers were available.

Downgrade can also occur due to PARALLEL_SERVERS_TARGET. When the number of busy PX servers exceeds this threshold, Oracle begins downg rading new parallel queries automatically.

3. Producer-Consumer Imbalance (PX Deq Credit: send blkd)

In multi-granule parallel plans, data flows from producer PX servers to consumer PX servers through buffered queues. If one producer generates data much faster than its consumers can process it (due to data skew, partition elimination, or join selectivity differences), the producer’s output queue fills up and the producer blocks on PX Deq Credit: send blkd. This represents work imbalance within the parallel query.

4. PX Coordinator Overhead (PX Deq: Slave Session Stats)

When a parallel operation completes, each PX slave must report its statistics back to the query coordinator. This generates PX Deq: Slave Session Stats waits. In systems with very high parallel query concurrency (many small parallel operations completing rapidly), this coordination overhead accumulates. The fix is usually to avoid using parallel query for small operations where the overhead exceeds the benefit.

5. OS Thread Creation Latency (OS Thread startup)

On platforms that create new OS threads (rather than pre-spawning PX servers) for each parallel operation, OS thread startup records the thread creation latency. This only applies to specific platform/configuration combinations. The fix is to increase PARALLEL_MIN_SERVERS to pre-spawn threads so they are available immediately.

6. PARALLEL_MIN_TIME_THRESHOLD Too Low

With PARALLEL_DEGREE_POLICY = AUTO, Oracle automatically parallelizes operations that are expected to take longer than PARALLEL_MIN_TIME_THRESHOLD (default: 30 seconds). If this threshold is too low, Oracle parallelizes short-running operations that do not benefit from parallelism — generating PX coordination overhead and consuming PX server slots unnecessarily.

Resolution Steps

Step 1: Tune PARALLEL_MAX_SERVERS Based on Hardware Capacity

-- Check current settings
SHOW PARAMETER parallel_max_servers;
SHOW PARAMETER cpu_count;

-- Guideline: PARALLEL_MAX_SERVERS should be at most:
-- CPU_COUNT * 2 (for pure parallel workloads)
-- CPU_COUNT * (desired concurrent PQ sessions) * avg_DOP

-- Example for 32-core server running 4 concurrent parallel queries at DOP 8:
-- 4 * 8 = 32 PX servers needed minimum; set to 64 for headroom
ALTER SYSTEM SET parallel_max_servers = 64 SCOPE=BOTH;

-- Also set the target (when busy PX servers exceeds this, downgrade begins):
ALTER SYSTEM SET parallel_servers_target = 48 SCOPE=BOTH;

Step 2: Pre-Spawn PX Servers to Eliminate Thread Startup Overhead

-- Pre-spawn a base number of PX servers at instance startup
-- Eliminates OS thread startup waits for the first wave of parallel queries
ALTER SYSTEM SET parallel_min_servers = 16 SCOPE=BOTH;
-- Oracle will maintain at least 16 idle PX servers at all times

Step 3: Use PARALLEL_DEGREE_POLICY = AUTO for Adaptive Downgrade

-- AUTO degree policy: Oracle tunes DOP based on system load
-- Prevents runaway parallel queries from exhausting the server pool
ALTER SYSTEM SET parallel_degree_policy = AUTO SCOPE=BOTH;

-- Set minimum time threshold to avoid parallelizing small operations
ALTER SYSTEM SET parallel_min_time_threshold = 30 SCOPE=BOTH;  -- 30 seconds

-- Set CPU degree limit to prevent any single query using all CPUs
ALTER SYSTEM SET parallel_degree_limit = CPU SCOPE=BOTH;

Step 4: Fix Data Skew Causing Producer-Consumer Imbalance

-- If PX Deq Credit: send blkd is elevated, investigate data distribution
-- Check partition data skew:
SELECT
    partition_name,
    num_rows,
    blocks
FROM dba_tab_partitions
WHERE table_owner = 'HR'
  AND table_name = 'LARGE_TABLE'
ORDER BY num_rows DESC;

-- For skewed hash partitions, consider using subpartitioning
-- or choosing a better partition key with higher cardinality

-- Alternatively, use a parallel hint with specific DOP to let Oracle redistribute:
SELECT /*+ PARALLEL(t, 4) PQ_DISTRIBUTE(t, HASH, HASH) */ *
FROM large_table t
JOIN dimension_table d ON t.key = d.key;

Step 5: Limit Parallel DOP Per SQL When Pool Is Oversubscribed

-- Use Resource Manager to limit parallel DOP by consumer group
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan                  => 'MIXED_WORKLOAD_PLAN',
        group_or_subplan      => 'BATCH_USERS',
        comment               => 'Limit batch parallel DOP',
        parallel_degree_limit_p1 => 4,  -- Max DOP = 4 for batch group
        max_utilization_limit => 40     -- Max 40% CPU
    );
END;
/

Step 6: Detect and Address Frequent Downgrade

-- Check V$SYSSTAT for downgrade frequency
SELECT name, value
FROM v$sysstat
WHERE name IN (
    'parallel operations not downgraded',
    'parallel operations downgraded to serial',
    'parallel operations downgraded 75 to 99 pct',
    'parallel operations downgraded 50 to 75 pct',
    'parallel operations downgraded 25 to 50 pct',
    'parallel operations downgraded 1 to 25 pct'
)
ORDER BY name;

-- "parallel operations downgraded to serial" > 0 means full serialization occurred
-- This is a critical problem for DWH workloads that depend on parallel execution

Prevention & Tuning

Right-size PARALLEL_MAX_SERVERS before production go-live: Profile the parallel workload in a load test, identify peak concurrent DOP demand, and set PARALLEL_MAX_SERVERS with 25% headroom above peak.

Use PARALLEL_SERVERS_TARGET to enable graceful downgrade: Setting PARALLEL_SERVERS_TARGET below PARALLEL_MAX_SERVERS allows Oracle to begin downgrading new queries before the pool is fully exhausted, preventing complete pool starvation.

Monitor “parallel operations downgraded to serial” continuously: This statistic being non-zero means parallel queries are running serially — a silent, significant performance regression. Alert on any increment.

Avoid using parallel query for OLTP workloads: Parallel query is designed for DSS/DWH workloads with large scan operations. Using PARALLEL hints or table-level parallelism on OLTP tables wastes PX server resources and can starve analytical queries of PX servers.

Consider parallel statement queuing (12c+): With PARALLEL_DEGREE_POLICY = AUTO, Oracle can queue parallel queries rather than downgrading them — ensuring full DOP when servers become available:

-- Enable parallel statement queuing
ALTER SYSTEM SET parallel_degree_policy = AUTO SCOPE=BOTH;
-- Queuing is automatically enabled with AUTO policy
-- Queries wait in queue for PX servers rather than running at reduced DOP

Related Wait Events

• PX Deq: Execute Reply — PX slave waiting for a reply from another PX slave (parallel-to-parallel communication in hash joins)
• PX Deq Credit: send blkd — PX producer blocked because consumer’s input queue is full; data distribution skew
• enq: PS - contention — PX server pool allocation contention; pool is exhausted
• db file scattered read — Full table scan reads in parallel query; often the dominant actual work event in parallel scans
• cell multiblock physical read — Exadata equivalent of db file scattered read in parallel query context