2.295 PARALLEL_SERVERS_TARGET
PARALLEL_SERVERS_TARGET
specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used.
Property | Description |
---|---|
Parameter type |
Integer |
Default value |
For a CDB: Equal to the For a PDB or non-CDB:
|
Modifiable |
|
Modifiable in a PDB |
Yes |
Range of values |
|
Basic |
No |
When the parameter PARALLEL_DEGREE_POLICY
is set to ADAPTIVE
or AUTO
, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. In a single instance database, statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVERS_TARGET
. In an Oracle RAC database, a statement running on one instance can allocate parallel server processes on another instance when necessary. For example, if a statement running on one instance requires parallel execution, but the number of parallel server processes active on that instance is equal to or greater than the value of PARALLEL_SERVERS_TARGET
for that instance, then the statement can allocate parallel server processes on a different instance. Therefore, statement queuing in an Oracle RAC database will begin only when every instance has reached its PARALLEL_SERVER_TARGET
threshold.
Note:
Consumer groups that have been marked with the PARALLEL_STMT_CRITICAL
directive set to BYPASS_QUEUE
are allowed to bypass the parallel statement queue, and therefore may drive the total number of active parallel server processes beyond PARALLEL_SERVERS_TARGET
. Parallel statements issued with PARALLEL_DEGREE_POLICY
not set to ADAPTIVE
and AUTO
can also drive the total number of active parallel server processes beyond PARALLEL_SERVERS_TARGET
.
By default, PARALLEL_SERVERS_TARGET
is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS
) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.
The number of concurrent parallel users running at default degree of parallelism on an instance depends on the memory initialization parameter settings for the instance. For example, if the MEMORY_TARGET
or SGA_TARGET
initialization parameter is set, then the number of concurrent_parallel_users
= 4
. If neither MEMORY_TARGET
or SGA_TARGET
is set, then PGA_AGGREGATE_TARGET
is examined. If a value is set for PGA_AGGREGATE_TARGET
, then concurrent_parallel_users
= 2
. If a value is not set for PGA_AGGREGATE_TARGET
, then concurrent_parallel_users
= 1
.
Note that all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.
By default, all PDB queries are subjected to queuing at the PDB level first by the PDB's PARALLEL_SERVERS_TARGET
value, and then at the CDB level by the CDB's PARALLEL_SERVERS_TARGET
value. This default behavior prevents any SQL statement inside a PDB from getting downgraded if parallel servers are exhausted in the CDB.
Parallel statement queuing is enabled by default at the CDB level because the CDB has a default value for PARALLEL_SERVERS_TARGET
. You can disable parallel statement queuing at the CDB level by using ALTER SYSTEM
to set PARALLEL_SERVERS_TARGET
to 0 for the CDB.
The default value for PARALLEL_SERVERS_TARGET
for a PDB is determined using the calculation in the table above with the PDB’s CPU_COUNT
value.
Note:
A PDB can set a lower limit for parallel execution servers than the limit specified in the CDB resource plan. When the PARALLEL_SERVERS_TARGET
initialization parameter is set in a PDB, and parallel execution server limit is specified for a PDB in the CDB resource plan, then the lower of the two limits is enforced. For example, assume that the PARALLEL_SERVERS_TARGET
initialization parameter is set to 100 in the CDB root. Also assume that hrpdb has its PARALLEL_SERVERS_TARGET
initialization parameter set to 50 and the CDB plan has a directive for hrpdb with parallel_server_limit
set to 70%. In this case, the limit for parallel execution servers for hrpdb is 50, because 50 is lower than the CDB resource plan limit of 70 for hrpdb.
See Also:
-
Oracle Database VLDB and Partitioning Guide for more information about this parameter