Scops
Docs Download

Oracle problems

ORA-12827: The Parallel Server Pool Is Full. Who Holds It, and the Fix

3 November 2026

ORA-12827 is the one parallel error that stops a statement instead of slowing it: PARALLEL_MIN_PERCENT asked for a minimum share of parallel servers, the pool could not give it, and Oracle returned the error rather than a smaller degree. Far more often the pool runs out in silence. Statements are downgraded to a lower degree or to serial, the nightly batch takes three times as long, and nothing is logged anywhere. Both have the same cause, more parallel demand than the pool holds at that hour, and the same three queries find who holds it.

What the error says

Oracle's reference describes PARALLEL_MIN_PERCENT in one sentence: it ensures that an operation always gets a minimum percentage of parallel execution servers or errors out. Its default is 0, no minimum, so the error only appears where a session or a job set it, usually a batch that must not run serial. The message carries the numbers: the servers requested, the servers available, and the percentage asked for. The parameter applies when statement queuing is not enabled, with PARALLEL_DEGREE_POLICY at MANUAL or LIMITED; with AUTO or ADAPTIVE, a statement that cannot get its servers queues instead.

The pool right now

SELECT statistic, value
FROM   v$px_process_sysstat
WHERE  statistic LIKE 'Servers%'
OR     statistic = 'Server Sessions';

Servers in use close to the maximum is the full pool. Servers started that keeps climbing between two readings means Oracle creates and destroys server processes all day, because PARALLEL_MIN_SERVERS keeps too few alive. The parameters that bound the pool:

SELECT name, value
FROM   v$parameter
WHERE  name IN ('parallel_max_servers', 'parallel_min_servers',
                'parallel_servers_target', 'parallel_degree_policy',
                'parallel_degree_limit', 'parallel_min_percent',
                'parallel_threads_per_cpu', 'cpu_count')
ORDER  BY name;

Oracle sizes PARALLEL_MAX_SERVERS by default at PARALLEL_THREADS_PER_CPU × CPU_COUNT × concurrent parallel users × 5, capped by PROCESSES, and PARALLEL_SERVERS_TARGET at the same formula times 2: that is the level at which statement queuing begins when the policy is AUTO or ADAPTIVE.

Who holds the servers

Every parallel server session knows its query coordinator. Grouped by coordinator, with the degree the statement asked for and the degree it got:

SELECT s.qcsid, q.username, q.sql_id, q.module,
       COUNT(*)                                     AS servers,
       MAX(s.req_degree)                            AS requested,
       MAX(s.degree)                                AS got
FROM   v$px_session s
JOIN   v$session q ON q.sid = s.qcsid AND q.serial# = s.qcserial#
WHERE  s.sid <> s.qcsid
GROUP  BY s.qcsid, q.username, q.sql_id, q.module
ORDER  BY servers DESC;

A statement whose degree is below the one requested was downgraded at start, and Oracle's reference says why the requested figure is the one to read: it is the degree before any resource, multiuser or load balancing reductions. The hog at the top of the list usually holds twice its degree: a server group has at most two server sets, producers and consumers, so a degree of 64 is up to 128 server processes plus the coordinator. One report with a PARALLEL(64) hint on a 24-core host is the whole pool.

How often statements were downgraded

Oracle counts the downgrades since startup in V$SYSSTAT, in bands:

SELECT name, value
FROM   v$sysstat
WHERE  name LIKE 'Parallel operations%'
ORDER  BY name;

The rows run from not downgraded, through downgraded 1 to 25 percent, 25 to 50, 50 to 75 and 75 to 99, to downgraded to serial. Two readings a day apart give the daily rate. Downgraded to serial climbing by hundreds during the batch window is the silent form of ORA-12827: the batch ran on one CPU and nobody was told.

The fix, in order

  1. Find the statement that takes the pool, and cap it. In MANUAL mode the degree comes from hints and from the DEGREE attribute of tables and indexes, and a DEGREE set on a table years ago is the usual surprise: SELECT owner, table_name, degree FROM dba_tables WHERE TRIM(degree) NOT IN ('0', '1') lists them, and ALTER TABLE ... NOPARALLEL ends it. A report that hints PARALLEL(64) gets a degree that fits the host, which is rarely more than the core count divided by the number of statements that must run together.
  2. Give each group its share with the Resource Manager. A plan directive with PARALLEL_DEGREE_LIMIT_P1 caps the degree of a consumer group, PARALLEL_SERVER_LIMIT caps the share of the pool it may hold, and PARALLEL_QUEUE_TIMEOUT bounds how long it waits. The reporting group then cannot starve the batch, and the batch cannot starve the users. This is the fix that lasts.
  3. Queue instead of failing. PARALLEL_DEGREE_POLICY = AUTO enables automatic degree, statement queuing and in-memory parallel execution: a statement that cannot get its servers waits at PARALLEL_SERVERS_TARGET and runs at full degree when they free up, and Oracle's reference notes that serial statements execute immediately even while queuing is active. With queuing on, PARALLEL_MIN_PERCENT no longer applies, and ORA-12827 goes away with it.
  4. Size the pool for the CPU, not for the demand. More servers than CPU threads is a queue in the operating system instead of in Oracle, and a slower one. Raise PARALLEL_MIN_SERVERS when servers started climbs all day, keep PARALLEL_SERVERS_TARGET below the maximum so queuing starts before the pool is dry, and remember that every server counts toward PROCESSES: a full pool and a process limit arrive together.
  5. Use PARALLEL_MIN_PERCENT on purpose. A batch that must not run serial sets it for its own session, catches ORA-12827 and retries later; everyone else leaves it at 0 and takes the degree the pool can give.
  6. Move the collision. The downgrade counter says at which hour the pool runs out; two jobs that both want 128 servers at 02:00 run fine at 02:00 and 03:30.

See it coming

Radar Engine, the detection layer of Scops, reads the pool and the downgrade counters of every database each hour and knows what is usual for that hour, so a pool that is full every night at two, by design, is a Watch and a pool that is full at ten in the morning, with statements downgraded, is a Warning:

DetectorClockWhen it fires
Parallel server pool fullHourly. Full in 3 of 6 hours, with downgraded statements.Warning. With ORA-12827: Critical. Usual at this hour: Watch.
Sudden wait eventLive. A sudden large share of the waits, such as the PX Deq waits of a starved parallel statement.Watch. The largest one, if it continues: Warning.

The finding names the database, the hours the pool was full, the number of statements downgraded, and the coordinator that held the most servers, which is the statement to cap. Scops samples every session every 5 seconds, so the parallel sessions of that hour, with their coordinator, are in the Activity chart on any edition.

Questions

What does ORA-12827 mean?
The statement asked for a minimum share of parallel servers through PARALLEL_MIN_PERCENT and the pool could not provide it, so Oracle raised an error instead of running at a lower degree. It only happens where that parameter is above its default of 0.

Why do my parallel queries run serial without any error?
Because with PARALLEL_MIN_PERCENT at 0 Oracle downgrades silently when the pool is short. V$SYSSTAT's "Parallel operations downgraded to serial" counts them, and V$PX_SESSION shows the requested degree against the one a running statement got.

How many parallel servers does a degree of 64 use?
Up to 128, plus the coordinator: a server group has at most two server sets, and a producer set and a consumer set each run at the degree.

Should I raise PARALLEL_MAX_SERVERS?
Only up to what the CPUs can run; beyond that the servers queue in the operating system. Statement queuing with PARALLEL_DEGREE_POLICY = AUTO and Resource Manager limits per consumer group fix the collision without a bigger pool.

Related problems

Sources