Calculating Parameter Values for Parallelism
By Jean-Pierre Dijcks on Apr 20, 2011
I did get some questions on some of the default parameter settings/values for parallel_servers_target and parallel_max_servers. While these are being addressed in the documentation, I figured I should just list them here and create a reference now, rather than wait for a doc library update.
All of the below are on a per instance basis, and should be aggregated for a cluster.
The default value is calculated as follows:
cpu_count (hyperthreading is relevant here!) * parallel_threads_per_cpu * <concurrent users> * 2
The number of concurrent users running at default DOP on an instance is dependent on the memory management setting. If automatic memory management is disabled (manual mode) then the number of parallel users is 1. If PGA automatic memory management is enabled, the number of users is 2. If global memory management or SGA memory target is used in addition to PGA automatic memory management, then the number of supported concurrent users is 4.
1) If memory_target or sga_target is written in pfile or spfile,
2) Else if pga_aggregate_target is written in pfile or spfile,
3) Else <concurrent users>=1
On an Exadata X2-2 system this comes to the following for a single instance with the latest Oracle best practices in place:
(2 CPUs * 6 Cores * 2 (for hyperthreading)) * 1 (different from the default) * (we set SGA target: 4) * 2 = 192
This parameter is dependent on a number of things, which can be summarized in the following formula for its default value:
cpu_count (hyperthreading is relevant here!) * parallel_threads_per_cpu * <concurrent users> * 5
The same logic for <concurrent users> applies here and this leads to a default on Exadata X2-2 of:
(2 CPUs * 6 Cores * 2 (for hyperthreading)) * 1 (different from the default) * (we set SGA target: 4) * 5 = 480
You will notice that parallel_servers_target is certainly lower than parallel_max_servers. That should always be the case on your system! The system should start to queue parallel statements (note: only available with parallel_degree_policy = Auto) well before they fall of the cliff of running out of processes available for parallel statements.
With respect to whether parallel_servers_target is appropriate for your system, do read the posts on concurrency and how to assess how many concurrent statements can run on your system.