Oracle ORA-12827 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12827: Insufficient Parallel Query Slaves Available

ORA-12827 is thrown when Oracle cannot allocate enough parallel query slave processes to satisfy a parallel execution request. This typically happens when the number of available slaves falls below the threshold defined by the PARALLEL_MIN_PERCENT parameter, or when the system-wide PARALLEL_MAX_SERVERS limit has been reached. It is most common in busy Data Warehouse environments where multiple large parallel queries compete for the same pool of slave processes.

Top 3 Causes

1. PARALLEL_MIN_PERCENT Set Too High

When PARALLEL_MIN_PERCENT is set to a non-zero value, Oracle enforces a minimum percentage of requested slaves. If that threshold cannot be met, the query is rejected with ORA-12827 instead of degrading gracefully.

-- Check current setting
SHOW PARAMETER PARALLEL_MIN_PERCENT;

-- Reset to default (0) at session level for immediate relief
ALTER SESSION SET PARALLEL_MIN_PERCENT = 0;

-- Reset at system level permanently
ALTER SYSTEM SET PARALLEL_MIN_PERCENT = 0 SCOPE = BOTH;

2. PARALLEL_MAX_SERVERS Limit Exhausted

PARALLEL_MAX_SERVERS caps the total number of parallel server processes across the entire instance. During peak workloads, this ceiling is often hit when many sessions run parallel queries simultaneously.

-- Check the current limit
SHOW PARAMETER PARALLEL_MAX_SERVERS;

-- Check how many slaves are currently in use
SELECT COUNT(*) AS IN_USE
FROM V$PX_PROCESS
WHERE STATUS = 'IN USE';

-- Increase the limit (tune based on CPU count)
ALTER SYSTEM SET PARALLEL_MAX_SERVERS = 128 SCOPE = BOTH;

-- Identify sessions consuming parallel slaves
SELECT QCSID, COUNT(*) AS SLAVE_COUNT
FROM V$PX_SESSION
GROUP BY QCSID
ORDER BY SLAVE_COUNT DESC;

3. Resource Manager Parallel Server Limit

In environments using Oracle Resource Manager, each Consumer Group can have a hard cap on parallel server usage. Once that cap is hit, any new parallel request from that group triggers ORA-12827.

-- Check parallel limits per consumer group
SELECT PLAN, GROUP_OR_SUBPLAN, PARALLEL_SERVER_LIMIT
FROM DBA_RSRC_PLAN_DIRECTIVES
WHERE PARALLEL_SERVER_LIMIT IS NOT NULL;

-- Increase the limit for a specific group
BEGIN
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
    PLAN                      => 'MY_PLAN',
    GROUP_OR_SUBPLAN          => 'BATCH_GROUP',
    NEW_PARALLEL_SERVER_LIMIT => 75
  );
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

Quick Fix Solutions

If you need an immediate workaround while investigating the root cause:

-- Disable parallel query for the current session
ALTER SESSION DISABLE PARALLEL QUERY;

-- Force a lower degree of parallelism with a hint
SELECT /*+ PARALLEL(t, 2) */ *
FROM large_table t
WHERE created > SYSDATE - 7;

-- Temporarily reduce parallelism on a specific table
ALTER TABLE large_table PARALLEL 2;

Prevention Tips

Keep PARALLEL_MIN_PERCENT at 0.
The default value of 0 allows Oracle to degrade gracefully by using fewer slaves rather than failing outright. Only set this to a non-zero value if your workload strictly requires a minimum degree of parallelism, and even then, test thoroughly.

-- Validate the parameter is at safe default
SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = 'parallel_min_percent';

Monitor slave utilization proactively.
Schedule the query below to run every few minutes and alert when usage exceeds 80% of the PARALLEL_MAX_SERVERS limit. This gives you time to react before users start hitting ORA-12827.

SELECT
    SYSDATE AS checked_at,
    (SELECT VALUE FROM V$PARAMETER
     WHERE NAME = 'parallel_max_servers')   AS max_servers,
    COUNT(*)                                AS in_use,
    ROUND(COUNT(*) /
        (SELECT VALUE FROM V$PARAMETER
         WHERE NAME = 'parallel_max_servers') * 100, 1) AS pct_used
FROM V$PX_PROCESS
WHERE STATUS = 'IN USE';

Related Errors

Error Code	Description
ORA-12801	Error signaled in parallel query server — often accompanies ORA-12827
ORA-12805	Parallel query server died unexpectedly
ORA-12826	Similar slave availability error under different threshold conditions
ORA-04031	Shared Pool memory exhaustion — can co-occur during heavy parallel workloads

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community