Oracle 参数 PARALLEL_SERVERS_TARGET 官方解释，作用，如何配置最优化建议
本站中文解释
parallel_servers_target参数，指定オラクルサーバーの最大並列操作サーバー数。长期来看，表示了oracle中最多可以运行多少个并行操作服务器。
此参数可以使用Oracle“ALTER SYSTEM”命令或从Oracle OEM进行设置。
例如，设置parallel_servers_target参数为200：
ALTER SYSTEM SET parallel_servers_target = 200 SCOPE=MEMORY;
此参数只能指定最大值，并且不会影响当前正在运行的任务。如果当前的并行操作服务器数量比指定的参数更多，则参数值将被忽略。因此，您在设置并行操作服务器数量时需要小心。此外，由于空间要求增加，这些设置通常会导致数据库更加耗费资源。
官方英文解释
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:
-
“PARALLEL_DEGREE_POLICY”
-
“PARALLEL_MAX_SERVERS”
-
“MEMORY_TARGET”
-
“SGA_TARGET”
-
“PGA_AGGREGATE_TARGET”
-
Oracle Database VLDB and
Partitioning Guide for more information about this parameter
