Oracle ORA-12805 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12805: Parallel Query Server Died Unexpectedly — What You Need to Know

ORA-12805 is thrown when one or more Oracle Parallel Query (PQ) server processes terminate abnormally during execution of a parallel query. Because parallel execution relies on coordinated work across multiple slave processes, the failure of even a single process causes the entire query to be rolled back and the error surfaced to the user. This error typically appears during heavy batch processing, large table scans, or parallel DML operations under resource pressure.

Top 3 Causes

1. Insufficient PGA / SGA Memory

When parallel server processes cannot allocate enough memory for sort, hash join, or other in-memory operations, they crash mid-execution. This is the most common root cause in production environments.

-- Check current PGA settings and usage
SELECT name, value
FROM v$parameter
WHERE name IN ('pga_aggregate_target',
               'pga_aggregate_limit',
               'sort_area_size',
               'hash_area_size');

-- View real-time PGA consumption
SELECT name, value
FROM v$pgastat
WHERE name IN ('total PGA allocated',
               'maximum PGA allocated',
               'aggregate PGA target parameter');

-- Increase PGA target if undersized
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 4G SCOPE=BOTH;

2. Excessive Degree of Parallelism (DOP)

When the number of requested parallel slaves exceeds PARALLEL_MAX_SERVERS, Oracle may forcibly terminate existing parallel processes to free resources, triggering ORA-12805. Auto DOP (PARALLEL_DEGREE_POLICY=AUTO) can silently assign very high DOP values, making this worse.

-- Check parallel server limits
SELECT name, value
FROM v$parameter
WHERE name IN ('parallel_max_servers',
               'parallel_min_servers',
               'parallel_degree_policy');

-- View active parallel sessions
SELECT qcsid, degree, req_degree, server#, status
FROM v$px_session
ORDER BY qcsid;

-- Cap DOP on a specific table
ALTER TABLE large_orders PARALLEL 4;

-- Use hint to limit DOP per query
SELECT /*+ PARALLEL(o, 4) */
       customer_id,
       SUM(order_total) AS revenue
FROM large_orders o
GROUP BY customer_id;

-- Switch to manual DOP policy to prevent auto over-allocation
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = 'MANUAL' SCOPE=BOTH;
ALTER SYSTEM SET PARALLEL_MAX_SERVERS = 64 SCOPE=BOTH;

3. OS-Level Resource Limits or Oracle Bugs

OS constraints such as insufficient process limits (ulimit), file descriptor exhaustion, or kernel parameter misconfigurations can cause PQ server processes to be killed by the OS. Known Oracle bugs in specific patch levels can also produce this error alongside ORA-00600 or ORA-07445.

-- Check for internal errors accompanying ORA-12805
SELECT incident_id, error_number, error_message, create_time
FROM v$diag_incident
WHERE error_number IN (12805, 600, 7445)
ORDER BY create_time DESC;

-- Identify the trace file for deeper analysis
SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File';

-- Verify parallel process wait events
SELECT event, COUNT(*) AS cnt
FROM v$session
WHERE wait_class != 'Idle'
  AND program LIKE '%P0%'
GROUP BY event
ORDER BY cnt DESC;

If ORA-00600 or ORA-07445 appears alongside ORA-12805, open a Service Request on My Oracle Support immediately — it is almost certainly a bug requiring a patch.

Quick Fix Solutions

-- 1. Temporarily disable parallelism for problem queries
SELECT /*+ NO_PARALLEL */ *
FROM large_orders
WHERE order_date >= DATE '2024-01-01';

-- 2. Kill runaway parallel sessions consuming excess resources
SELECT sid, serial#, program, status
FROM v$session
WHERE program LIKE '%P0%';

-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 3. Reset parallel settings at session level
ALTER SESSION DISABLE PARALLEL QUERY;
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL DDL;

Prevention Tips

Monitor parallel resource usage regularly.
Schedule a periodic query against V$PX_SESSION and V$PGASTAT. Keep PARALLEL_MAX_SERVERS at no more than 2–4× your CPU core count and set PARALLEL_DEGREE_POLICY = 'MANUAL' to retain explicit control over DOP in production.

-- Useful monitoring query to add to your DBA toolkit
SELECT px.qcsid,
       px.degree,
       px.req_degree,
       s.sql_id,
       s.status
FROM v$px_session px
JOIN v$session s ON px.sid = s.sid;

Keep Oracle patches current.
Parallel query bugs are regularly fixed in Oracle PSU and RU patches. Review the patch readme and My Oracle Support (MOS) for known parallel query issues matching your Oracle version before each patching cycle. Applying relevant one-off patches proactively can eliminate recurring ORA-12805 incidents caused by engine bugs.

Related Errors

Error	Description
ORA-12801	Error signaled in parallel query server — often appears alongside ORA-12805
ORA-00600	Internal Oracle error; if paired with ORA-12805, indicates a bug
ORA-07445	OS signal caused process crash; check core dump and trace files
ORA-04031	Shared memory allocation failure affecting SGA and parallel processes
ORA-12853	Insufficient memory for PX buffers; tune `PARALLEL_MAX_SERVERS`

📖 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