Oracle Parallel Execution in Production: When PX Helps and When It Starves a RAC

Oracle parallel execution makes a big query faster by splitting it across many server processes - but only when the machine has spare CPU and I/O and few statements compete for those processes. Switch it on for everything and the parallel servers become the scarcest thing on the box. I diagnosed exactly that on a production 19c RAC at a financial organisation: an average of 109 sessions queued for parallel servers while the host sat 88.6% idle.

1. What Is Oracle Parallel Execution?

Oracle parallel execution is the ability to split one SQL statement across several parallel execution (PX) server processes, coordinated by the session that issued it, so that a large scan, join, sort or load finishes sooner. The number of servers working on each step is the degree of parallelism, or DOP.

The issuing session becomes the query coordinator, or QC. It hands out work in chunks called granules, gathers the results and returns them to the client.

The workers come from each instance's pool of PX servers, capped by PARALLEL_MAX_SERVERS. One detail drives most capacity arguments: with at most two server sets per coordinator, most statements use DOP × 2 servers - eight for a query at DOP 4.

2. When Does Parallel Query Help, and When Does It Hurt?

The Oracle VLDB and Partitioning Guide puts it plainly. Parallel beats serial for large data sets, low concurrency and when elapsed time matters; serial wins for small data sets, high concurrency and when efficiency matters.

The hardware row matters most. The guide warns that parallel execution exists to exploit additional resources, and on an over-utilised system it can make things worse.

Coordination has a cost too. For a statement that runs a few seconds, starting servers and passing rows between server sets can outweigh the gain.

So on a core banking or ERP database, parallelism belongs to named jobs. My notes on keeping the end-of-day batch window on a banking database show where it pays.

3. Which Parameters Decide How Many PX Servers You Get?

Seven parameters do most of the work; the last column shows the value found in this case.

PARALLEL_MAX_SERVERS is the ceiling; PARALLEL_SERVERS_TARGET is the queue line. In a non-CDB the default target sits below the maximum so that admitted statements get every server they ask for. Here both were 4, which left no headroom at all.

Run the default formula for your own node. PARALLEL_THREADS_PER_CPU defaults to 1, and "concurrent parallel users" is 4 when SGA_TARGET or MEMORY_TARGET is set.

With CPU_COUNT at the host's 32, that lands in the hundreds. A 4 is nowhere near the default for that hardware; it was set by hand.

SELECT inst_id, name, value, isdefault
FROM   gv$parameter
WHERE  name LIKE 'parallel%'
   OR  name IN ('cpu_count', 'optimizer_features_enable')
ORDER  BY name, inst_id;

4. What Is the Difference Between a DOP Downgrade and Statement Queuing?

When a parallel statement cannot get its servers, PARALLEL_DEGREE_POLICY decides what happens next.

Under MANUAL or LIMITED, it is downgraded. It runs at whatever DOP the free servers allow, or serially if the pool is exhausted. If PARALLEL_MIN_PERCENT is set and that share is not available, it fails with ORA-12827 instead.

Under AUTO or ADAPTIVE, it is queued. It waits, first in, first out, until enough servers are free for the DOP the optimizer chose, on the wait event resmgr:pq queued.

A downgrade starts now but may run far longer than planned. A queue guarantees the full DOP, and everything behind it waits - even when the CPUs are idle.

Serial statements never queue. On RAC, Oracle's page on parallel statement queuing bases the queue on the sum of PARALLEL_SERVERS_TARGET across instances, but PARALLEL_FORCE_LOCAL=TRUE confines PX servers to the coordinator's own node.

5. What Did PX Starvation Look Like on a Production RAC?

The system was a two-node Oracle 19c RAC at a financial organisation, running a banking and loan workload with a Data Guard standby. The node I examined had 32 CPUs on 16 cores and 250 GB of RAM.

I took a one-hour AWR window from the second instance, one afternoon:

• Top wait: resmgr:pq queued, at 55.7% of DB time.
• Queued sessions: 109 average active sessions waiting for parallel servers.
• Top three SQL statements: about 99% of elapsed time in the PX queue, 0% on CPU or I/O.
• Everything else: buffer cache hit 100%, host 88.6% idle, redo, I/O and interconnect healthy.

Read the third line twice. Those statements were not slow; they were not running.

A busy database on an idle server is the signature of PX starvation, and OS monitoring alone would have shown nothing wrong. For the general reading order, see my guide to reading AWR and ASH reports by wait event.

6. Why Was an Idle 32-CPU Node Making 109 Sessions Wait?

The spfile explained it:

# parallel settings in the spfile, as found
parallel_degree_policy   = AUTO
parallel_max_servers     = 4
parallel_servers_target  = 4
parallel_force_local     = TRUE

AUTO turned on automatic DOP for the whole workload. Any statement estimated above PARALLEL_MIN_TIME_THRESHOLD - 10 seconds by default - became a parallel candidate.

Four servers is less than one parallel query. At DOP 2 with two server sets, one statement takes all four, and the next parallel statement queues.

The target equalled the maximum. Any parallel statement that needed more servers than were free went straight to the queue, and PARALLEL_FORCE_LOCAL=TRUE stopped it borrowing servers from the other node.

The queue is first in, first out, which is how SQL can spend 99% of its elapsed time standing in line. Each setting is defensible alone; together they built a turnstile four processes wide in front of a 32-CPU machine.

I also saw optimizer_features_enable='11.2.0.4' on this 19c database. It did not cause the queue, so I flagged it for a later maintenance window instead.

7. How Do You Diagnose PX Server Starvation, Step by Step?

This is my order of work, and none of it changes anything. Licensing first: AWR and ASH need the Diagnostics Pack, V$SQL_MONITOR needs the Tuning Pack, and V$PQ_SYSSTAT, V$PX_SESSION and V$SYSSTAT appear in neither pack's list.

1. AWR top foreground events. resmgr:pq queued near the top means statements are waiting for servers, not for hardware.
2. ASH. Group the queued samples by SQL_ID with the first query below.
3. GV$PQ_SYSSTAT. Servers Busy at your maximum, Servers Idle at zero and a climbing Queries Queued confirm starvation.
4. GV$PX_SESSION. DEGREE lower than REQ_DEGREE means a statement was cut down.
5. GV$SYSSTAT. Rising "Parallel operations downgraded" counters are the same shortage under MANUAL.
6. Parameters on every instance, with the query in section 3.
7. The plan's Note section, which reads "automatic DOP: Computed Degree of Parallelism is n". With the Tuning Pack, add STATUS = 'QUEUED' and QUEUING_TIME from GV$SQL_MONITOR.

-- Step 2: which SQL is waiting in the PX queue? (ASH, last hour)
SELECT sql_id, COUNT(*) AS samples
FROM   gv$active_session_history
WHERE  event = 'resmgr:pq queued'
AND    sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR
GROUP  BY sql_id
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;
-- older window: dba_hist_active_sess_history with a snap_id range

-- Step 3: the PX pool on each instance
SELECT inst_id, statistic, value
FROM   gv$pq_sysstat
WHERE  statistic IN ('Servers Busy', 'Servers Idle', 'Servers Highwater',
                     'Queries Queued')
ORDER  BY inst_id, statistic;

-- Step 4: granted versus requested degree, per coordinator
SELECT qcinst_id, qcsid, server_set,
       MAX(degree) AS granted_dop, MAX(req_degree) AS requested_dop
FROM   gv$px_session
WHERE  server_set IS NOT NULL
GROUP  BY qcinst_id, qcsid, server_set;

-- Step 5: how often parallel statements were cut down
SELECT inst_id, name, value
FROM   gv$sysstat
WHERE  name LIKE 'Parallel operations%'
ORDER  BY inst_id, name;

One trap: these counters are cumulative since instance startup. Take two readings an hour apart and work with the difference.

8. What Do the PX Deq Wait Events Actually Tell You?

Most PX Deq events are processes passing messages, and Oracle classes most of them as idle. They show that parallel execution is happening, rarely why it is slow.

PX Deq: Execute Reply is the coordinator waiting on its servers, so look at what the servers themselves wait on. PX Deq Credit: send blkd is a producer held up by a consumer, or a client, that has not caught up.

resmgr:pq queued is different in kind. The statement has not started, and the wait counts in DB time, which is how it reached 55.7% here. Check how your release classes them:

SELECT name, wait_class
FROM   v$event_name
WHERE  name LIKE 'PX Deq%' OR name LIKE 'resmgr:pq%'
ORDER  BY wait_class, name;

When PX servers spread across nodes, the cost moves to the interconnect instead. I wrote up that opposite failure as the parallel-query storm that nearly evicted a RAC node.

9. How Do PARALLEL Hints and Table DEGREE Get Left On by Accident?

Most parallelism in an OLTP database was never designed. It was left behind.

The classic source is an ALTER INDEX ... REBUILD PARALLEL 8 in a maintenance window. Oracle's ALTER INDEX reference is explicit: the clause also changes the degree of the index itself, and all subsequent operations on the index use it.

CREATE TABLE ... AS SELECT and ALTER TABLE ... PARALLEL do the same for tables. A statement-level /*+ PARALLEL */ hint enables automatic DOP whatever the policy says.

-- tables and indexes that will try to run in parallel
SELECT 'TABLE' AS obj_type, owner, table_name AS name, TRIM(degree) AS degree
FROM   dba_tables
WHERE  TRIM(degree) NOT IN ('0', '1')
AND    owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
UNION ALL
SELECT 'INDEX', owner, index_name, TRIM(degree)
FROM   dba_indexes
WHERE  TRIM(degree) NOT IN ('0', '1')
AND    owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
ORDER  BY 1, 2, 3;

-- the habit that prevents it: reset straight after the job
ALTER INDEX app_owner.big_table_ix1 NOPARALLEL;

Now the twist. The VLDB guide says a table or index degree takes effect only under MANUAL or LIMITED, so while the policy is AUTO those leftovers sit dormant.

Switch to MANUAL and they wake up. An object marked plain PARALLEL, with no number, then gets the default DOP of PARALLEL_THREADS_PER_CPU × the sum of CPU_COUNT - so run this query before the fix, not after.

10. Which Fix Is Right: MANUAL, Bigger Limits, or Resource Manager?

I judged four options against what the AWR showed: idle hardware, a banking workload, and statements gaining nothing from the queue.

I recommended MANUAL. Auto DOP, not design, had made those statements parallel, the host had 88.6% of its CPU unused, and the parameter is dynamic, with a one-line rollback:

ALTER SYSTEM SET parallel_degree_policy = 'MANUAL' SCOPE = BOTH SID = '*';
-- rollback
ALTER SYSTEM SET parallel_degree_policy = 'AUTO'   SCOPE = BOTH SID = '*';

Because it was production, the change went to the change advisory board. A good board asks what else one line can touch, and the honest answer is the dormant DEGREE settings - so bring that query's output.

I rejected raising the limits. It would shorten the queue by inviting more of a transactional workload to run in parallel, which is how a starved system becomes a swamped one.

Where batch and OLTP genuinely share a database, Resource Manager is the long-term answer. One directive per consumer group caps the DOP, the share of the pool and the queue time:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                     => 'DAYTIME_PLAN',
    group_or_subplan         => 'BATCH_GROUP',
    comment                  => 'Batch: capped parallelism in business hours',
    parallel_degree_limit_p1 => 4,
    parallel_server_limit    => 50,    -- % of PARALLEL_SERVERS_TARGET
    parallel_queue_timeout   => 600);  -- seconds allowed in the queue
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

The plan and group must already exist, so treat this as the shape of a directive, not a drop-in script. For a group that must never wait, parallel_stmt_critical => 'BYPASS_QUEUE' skips the queue - use it sparingly.

11. What Should You Check Before and After the Change?

Before the change:

1. Run the DEGREE query from section 9 and reset unwanted leftovers with NOPARALLEL.
2. List the SQL that ran in parallel during your batch window: ASH samples with a non-zero QC_SESSION_ID come from PX servers. That work runs serially afterwards unless it is hinted or decorated.
3. Record baseline readings of GV$PQ_SYSSTAT and the downgrade counters.

After the change:

1. resmgr:pq queued should be gone from the next comparable AWR window. MANUAL does not queue, so this is the check that decides it.
2. Expect CPU to rise. Waiting work is now running, and on a node that was 88.6% idle that is the point.
3. If "Parallel operations downgraded to serial" climbs, decorated objects or hints are fighting over four servers. Reset them, or raise the limit modestly.

12. What Is the Lesson From This Case?

Oracle parallel execution is a deliberate tool for big jobs on spare hardware. Left on for everything with a pool of four, it turned an idle 32-CPU node into a waiting room.

The problem was admission, not capacity, so measure the queue before anyone buys hardware. It is the same top-down method as my Oracle performance tuning guide: start from DB time and follow the biggest wait.

Frequently Asked Questions

What does resmgr:pq queued mean in Oracle?

A parallel statement is waiting in the parallel statement queue, because starting it would push active PX servers above PARALLEL_SERVERS_TARGET. It appears under PARALLEL_DEGREE_POLICY AUTO or ADAPTIVE, or when a hint or Resource Manager directive applies queuing. It counts in DB time, so a large share means statements are waiting to start, not running slowly.

What is the difference between PARALLEL_MAX_SERVERS and PARALLEL_SERVERS_TARGET?

PARALLEL_MAX_SERVERS is the hard ceiling on PX server processes per instance. PARALLEL_SERVERS_TARGET is how many may be active before statement queuing starts, and it matters only when queuing is enabled. When the two are equal there is no headroom between the queue line and the ceiling.

Is it safe to change PARALLEL_DEGREE_POLICY on a live database?

It is dynamic: ALTER SYSTEM ... SCOPE=BOTH SID='*' changes every instance without a restart, and the old value reverses it. The risk is behaviour, not availability - under MANUAL, leftover table and index degrees take effect and auto DOP statements run serially. Check DBA_TABLES and DBA_INDEXES first and use normal change control.

How do I find tables and indexes that are set to run in parallel?

Query DBA_TABLES and DBA_INDEXES for a DEGREE other than 1 or 0, trimming the column because it is stored as text, and count DEFAULT as parallel. Leftovers usually come from a parallel index rebuild or a CREATE TABLE AS SELECT. Reset them with ALTER TABLE or ALTER INDEX ... NOPARALLEL.

Should I worry about PX Deq wait events in my AWR report?

Usually not on their own. Most PX Deq events are messages between the query coordinator and its servers, and Oracle classes most of them as idle. If one stands out, check its class in V$EVENT_NAME, then look in ASH at what the PX servers were waiting on.

Should an OLTP database use parallel query at all?

Rarely, and only on purpose. Oracle's VLDB guide says typical OLTP statements gain no benefit, because coordination costs more than it saves on short work. Keep it for named jobs such as index builds, loads and end-of-day batch, with the policy at MANUAL.

What is a DOP downgrade and how can I see it?

A downgrade is a parallel statement running with fewer PX servers than it requested, down to serial if none are free, when statement queuing is not in force. V$SYSSTAT counts it in the "Parallel operations downgraded" statistics, and V$PX_SESSION shows it live as a DEGREE lower than REQ_DEGREE.

Does PARALLEL_FORCE_LOCAL help or hurt on RAC?

Both. Setting it to TRUE keeps PX servers on the node where the statement started, so parallel traffic stays off the interconnect. The cost is that a statement can use only its own node's pool, so a small PARALLEL_MAX_SERVERS there becomes a hard wall, as it was in this case.