PARALLEL_MAX_SERVERS - Limit Oracle Parallel Query Processes

PARALLEL_MAX_SERVERS

Overview

PARALLEL_MAX_SERVERS sets the absolute maximum number of parallel execution (PX) server processes that Oracle will allow to run simultaneously across the entire instance. When a parallel query, parallel DML, or parallel DDL operation requests PX servers, Oracle allocates them up to this ceiling. Once the ceiling is reached, subsequent parallel operations either run serially (if PARALLEL_MIN_PERCENT is not set or is 0) or return an error.

This parameter is the primary resource control for Oracle’s parallel execution engine. Without it, a workload with many concurrent parallel queries could spawn hundreds of PX server processes, exhausting CPU, memory, and session resources. In RAC environments, each instance enforces its own PARALLEL_MAX_SERVERS limit, so the cluster-wide maximum PX servers is the sum across all instances. Proper sizing prevents runaway parallelism while allowing legitimate large queries to benefit from parallel execution.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: Derived formula: PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * (max(2 * CPU_COUNT, PARALLEL_SERVERS_TARGET * 2)) — effectively Oracle calculates a large default based on CPU count Valid Range: 0 to 32767 Available Since: Oracle 7 Modifiable: Yes — SCOPE=BOTH (SPFILE + running instance) PDB Modifiable: Yes (within CDB limits)

Configuration

Viewing Current Value

-- Check current PARALLEL_MAX_SERVERS setting
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'parallel_max_servers';

-- Check SPFILE value
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'parallel_max_servers';

-- View all parallel-related parameters together
SELECT name, value, isdefault
FROM v$parameter
WHERE name IN (
  'parallel_max_servers',
  'parallel_min_servers',
  'parallel_servers_target',
  'parallel_degree_policy',
  'parallel_threads_per_cpu',
  'cpu_count'
)
ORDER BY name;

-- Check current PX server usage
SELECT COUNT(*) AS total_px_servers,
       SUM(CASE WHEN status = 'BUSY' THEN 1 ELSE 0 END) AS busy,
       SUM(CASE WHEN status = 'AVAILABLE' THEN 1 ELSE 0 END) AS available
FROM v$px_process;

Setting the Parameter

-- Set a conservative maximum for OLTP-primary databases
ALTER SYSTEM SET parallel_max_servers = 16 SCOPE=BOTH;

-- Set a larger limit for data warehouse workloads
ALTER SYSTEM SET parallel_max_servers = 128 SCOPE=BOTH;

-- Disable parallel execution entirely (no PX servers allowed)
ALTER SYSTEM SET parallel_max_servers = 0 SCOPE=BOTH;

-- Set in SPFILE only (takes effect on next restart)
ALTER SYSTEM SET parallel_max_servers = 64 SCOPE=SPFILE;

-- Verify change took effect
SELECT name, value
FROM v$parameter
WHERE name = 'parallel_max_servers';

Tuning Guidance

Recommended Values

Environment	Recommended PARALLEL_MAX_SERVERS
OLTP (minimal parallel workload)	8–16
Mixed OLTP + reporting	32–64
Data warehouse (dedicated)	CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2
RAC node (per instance)	32–128 per node depending on CPUs
Exadata (per node)	Oracle sets this automatically; do not reduce
Systems with memory pressure	Reduce to limit PX server memory consumption
Environments with PX exhaustion history	Increase, or use PARALLEL_SERVERS_TARGET

The default value Oracle calculates is intentionally generous. In practice, most environments benefit from explicitly setting a value that reflects the actual capacity of the system — typically CPU_COUNT * PARALLEL_THREADS_PER_CPU * 4 as a starting upper bound.

How to Size

-- Determine the CPU-based starting formula
SELECT TO_NUMBER(p1.value) AS cpu_count,
       TO_NUMBER(p2.value) AS threads_per_cpu,
       TO_NUMBER(p1.value) * TO_NUMBER(p2.value) * 4 AS suggested_max
FROM v$parameter p1, v$parameter p2
WHERE p1.name = 'cpu_count'
  AND p2.name = 'parallel_threads_per_cpu';

-- Check historical peak PX server usage
SELECT MAX(value) AS peak_px_servers
FROM v$sysstat
WHERE name = 'Parallel operations not downgraded';

-- Monitor current active PX servers by query
SELECT s.sid, s.serial#, s.status,
       p.degree, p.req_degree,
       s.sql_id
FROM v$px_session p
JOIN v$session s ON p.sid = s.sid
ORDER BY p.degree DESC;

-- Check how often parallel operations are being downgraded
-- (reduced degree due to PX server shortage)
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;

Sizing Formula

Starting value:
  PARALLEL_MAX_SERVERS = CPU_COUNT * PARALLEL_THREADS_PER_CPU * 4

For dedicated data warehouse:
  PARALLEL_MAX_SERVERS = CPU_COUNT * PARALLEL_THREADS_PER_CPU * 8

For RAC (per instance):
  Use same formula per node; cluster total = per_instance_limit * node_count

Memory check:
  Each PX server uses ~1-2MB PGA minimum
  Total PX memory = PARALLEL_MAX_SERVERS * avg_PGA_per_server
  Ensure total PX memory < PGA_AGGREGATE_LIMIT

Monitoring

-- Monitor PX server pool status over time
SELECT inst_id, status, COUNT(*) AS server_count
FROM gv$px_process
GROUP BY inst_id, status
ORDER BY inst_id, status;

-- Check for queued parallel operations (PX servers exhausted)
SELECT event, total_waits, time_waited
FROM v$system_event
WHERE event LIKE '%parallel%'
   OR event = 'PX Deq Credit: send blkd'
   OR event = 'resmgr:become active'
ORDER BY time_waited DESC;

-- Review parallel execution statistics
SELECT name, value
FROM v$sysstat
WHERE name LIKE 'Parallel%'
ORDER BY name;

Common Issues

Issue 1: PX Server Pool Exhausted — Queries Running Serially

When all PX servers are busy, Oracle silently degrades parallel queries to serial execution (unless PARALLEL_MIN_PERCENT enforces a minimum degree). This is visible in v$sysstat as parallel operations downgraded to serial.

Resolution: Either increase PARALLEL_MAX_SERVERS if the hardware supports it, implement Resource Manager to allocate PX servers fairly across consumer groups, or reduce the default degree of parallelism for objects.

-- Identify queries consuming large numbers of PX servers
SELECT sql_id, degree, req_degree,
       COUNT(*) AS px_server_count
FROM v$px_session
GROUP BY sql_id, degree, req_degree
ORDER BY px_server_count DESC;

-- Limit parallel degree for a specific table
ALTER TABLE large_table PARALLEL 4;

-- Or limit at session level
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;

Issue 2: PX Servers Consuming Excessive Memory

Each PX server process allocates PGA memory for sort, hash join, and other operations. With a high PARALLEL_MAX_SERVERS and large parallel queries, total PGA consumption can exceed PGA_AGGREGATE_LIMIT, causing ORA-04036.

Resolution: Reduce PARALLEL_MAX_SERVERS to a value where the peak PGA usage stays within PGA_AGGREGATE_LIMIT. Use Resource Manager to throttle concurrent parallel operations.

-- Check current total PGA usage
SELECT ROUND(SUM(pga_alloc_mem)/1024/1024, 1) AS total_pga_mb,
       ROUND(SUM(pga_used_mem)/1024/1024, 1) AS used_pga_mb
FROM v$process;

-- Check PGA limit
SELECT name, value/1024/1024 AS mb
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit');

Issue 3: Downgraded Parallel Operations Causing Performance Regressions

If PARALLEL_MAX_SERVERS is set too low (or left at 0), queries that expect parallelism run serially and may miss SLA targets. This is particularly impactful for large data warehouse loads.

Resolution: Increase PARALLEL_MAX_SERVERS incrementally, monitoring CPU and memory utilization to ensure the system can support additional parallelism. Use PARALLEL_SERVERS_TARGET as a soft target to manage the transition between in-memory and out-of-memory parallelism gracefully.

-- Set a soft target below the hard maximum
ALTER SYSTEM SET parallel_servers_target = 32 SCOPE=BOTH;
ALTER SYSTEM SET parallel_max_servers = 64 SCOPE=BOTH;

Related Parameters

• CLUSTER_DATABASE — In RAC environments, PARALLEL_MAX_SERVERS is enforced per instance; total cluster-wide limit is the sum
• INSTANCE_NUMBER — RAC instance identity; parallel query coordinator and server affinity is influenced by instance number
• PGA_AGGREGATE_TARGET — PX servers consume PGA; ensure PARALLEL_MAX_SERVERS * avg_PGA_per_server fits within this limit
• PGA_AGGREGATE_LIMIT — Hard ceiling on PGA; PX server spawning that would breach this limit will be refused

Related Errors

• ORA-04036: PGA Memory Limit Exceeded — Too many PX servers consuming PGA beyond PGA_AGGREGATE_LIMIT
• ORA-12801: Error Signaled in Parallel Query Server — General parallel server error; often caused by resource exhaustion when PX server count approaches PARALLEL_MAX_SERVERS
• ORA-01555: Snapshot Too Old — Long-running parallel queries exhaust undo retention; related to parallel query resource management

Version Notes

Version	Notes
Oracle 7	Parameter introduced
Oracle 10g	Default calculation changed to account for CPU_COUNT and PARALLEL_THREADS_PER_CPU
Oracle 11g	PARALLEL_DEGREE_POLICY=AUTO and PARALLEL_SERVERS_TARGET introduced alongside this parameter
Oracle 12c+	PDB-level override supported; each PDB can limit parallel server consumption independently
Oracle 19c+	Automatic Degree of Parallelism (Auto DOP) makes this parameter more important as a safety ceiling
Oracle 21c / 23ai	Behavior unchanged; Oracle recommends setting an explicit value rather than relying on the auto-calculated default