Parallel Query

Databases

Oracle

Oracle Configuration

Parameters

PARALLEL_MIN_SERVERS

Controls the number of Parallel Query Slave Processes to be started at database startup.

In 11g the default value of the PARALLEL_MIN_SERVERS parameter is 0
In 12c the default value of the PARALLEL_MIN_SERVERS parameter is CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2

NOTE: In most cases the default is fine, but it can have an effect on memory usage if the value of CPU_COUNT has been incorrectly set (because each Parallel Query Slave will reserve PGA/UGA memory).

Note that on AIX the CPU_COUNT defaults to: "Online Virtual CPUs" (from lparstat -i) * number of SMT threads (i.e. 4 CPUs, with 4 smt threads leads to a potentially inappropriate default setting of 32 for PARALLEL_MIN_SERVERS). See Example below...

sqlplus / as sysdba

SHOW PARAMETER CPU_COUNT

SHOW PARAMETER PARALLEL_MIN_SERVERS

SHOW PARAMETER PARALLEL_THREADS_PER_CPU

exit

As a general rule CPU_COUNT should be equal to the output of:

lparstat -i | grep "Entitled Capacity"

Check that Entitled Capacity reflects actual workload before continuing. If the LPAR is actually running at a higher figure (i.e. it is stealing CPU from the pool) then consider correcting "Entitled Capacity". If correcting "Entitled Capacity" is not possible in the short term then set CPU_COUNT to reflect a reasonable average of actual CPU usage. Given that all threads in an SMT environment are not equal (i.e. only the first thread generally runs at full throughput) then you should not initally base CPU_COUNT on "Entitled Capacity" * SMT threads, although it may be appropriate to experiment with this later if performance is being throttled by the CPU_COUNT or other paramaters derived from it.

If this is not the case use the following commands to fix the situation:

ALTER SYSTEM SET cpu_count = &EntitledCapacity SCOPE=BOTH;

PARALLEL_MIN_SERVERS will be recalculated at instance startup to be equal to:

SELECT cc.value * ptpc.value * 2

FROM (SELECT value FROM v$parameter WHERE name = 'cpu_count') cc,

(SELECT value FROM v$parameter WHERE name = 'parallel_threads_per_cpu') ptpc

Optionally, you can force this using:

ALTER SYSTEM SET parallel_min_servers = &ParallelMinServers SCOPE=BOTH;

Use this variant of the SQL above to calculate the default value for parallel_min_servers based on potential settings for cpu_count:

SELECT &cpu_count * ptpc.value * 2

FROM (SELECT value FROM v$parameter WHERE name = 'parallel_threads_per_cpu') ptpc

lparstat