How PARALLEL_MAX_SERVERS and PROCESSES Parameters are Related?

Asset ID:	1-79-1968840.1
Update Date:	2018-03-29
Keywords:

Solution Type Predictive Self-Healing Sure

Solution 1968840.1 : How PARALLEL_MAX_SERVERS and PROCESSES Parameters are Related?

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Exadata X3-2 Eighth Rack
Information in this document applies to any platform.

Purpose

This document explains the relationship between parallel_max_servers and processes parameter which also includes how you can approach error ORA-20 in case of parallel execution.

Details

PARALLEL_MAX_SERVERS and PROCESSES

As per Oracle Documentation:
PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

As per documentation, PARALLEL_MAX_SERVERS is calculated as:

PARALLEL_MAX_SERVERS = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5

In the formula, the value assigned to concurrent_parallel_users running at the default degree of parallelism on an instance is dependent on the memory management setting.

If automatic memory management is disabled (manual mode), then the value of concurrent_parallel_users is 1.
If PGA automatic memory management is enabled, then the value of concurrent_parallel_users is 2.
If global memory management or SGA memory target is used in addition to PGA automatic memory management, then the value of concurrent_parallel_users is 4.

From 11gR2 onwards, there is a new method to compute the default for PARALLEL_MAX_SERVERS. In 11gR2, the value of PARALLEL_MAX_SERVERS is capped by PROCESSES - 15. In 12c, the value of PARALLEL_MAX_SERVERS is capped by PROCESSES - N, where N is the result of an internal calculation that estimates the maximum number of background processes that need to be reserved for the particular database.

For example, using a value of 15, as in 11gR2:

parallel_threads_per_cpu = 2
cpu_count = 4
pga_aggregate_target = 500M
sga_target = 900M
processes = 150

parallel_max_servers = 2 * 4 * 4 * 5 = 160
default value of: parallel_max_servers = min( 150-15 , 160 ) = 135

So with these values we get a default of 135 for parallel_max_servers.

Note if the parallel_max_servers is reduced due to value of processes, then you see similar to the following in alert log (e.g. at instance start up):

Mon May 06 18:43:06 2013
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)

ORA-20 and Parallel Query

Can I get ORA-20 during parallel execution?

Yes.
One of the issues being discussed here: Number of Sessions Exceeded During Database Creation While Executing SYS.UTL_RECOMP (Doc ID 749359.1)
Workaround suggested:

1. Before creating database, set parallel parameters to zero.
PARALLEL_MAX_SERVERS=0
PARALLEL_MIN_SERVERS =0

2. Set number of sessions to a higher number than default, i.e. SESSIONS=500

There are 3 types of processes:

1. background (SMON, PMON, DWBR, LGWR, etc), 2. server processes for user sessions, and 3. parallel query processes.

When you have number of parallel servers processes exceeding, once the system crosses threshold set by PROCESSES parameters, ORA-20 will occurr. It does not mean that if failed because of the parallel query server error. You may need to take a look at all the processes and what they are doing.

To get the list of all processes: ps -ef | grep 'db_name' | wc -l

Attachments

This solution has no attachment