Oracle教程 2023-02-26

Oracle 参数 PARALLEL_DEGREE_POLICY 官方解释，作用，如何配置最优化建议

本站中文解释

PARALLEL_DEGREE_POLICY参数用来指定Oracle并发度控制策略，影响查询、操作等操作时Oracle对应对对象的并发程度。可选参数有：AUTO（默认）、MANUAL和LIMITED。

AUTO：表示Oracle完全由Oracle内部自动控制并发的级别，即Oracle会根据当前系统的活动情况、CPU利用率和I/O负载动态调整并发程度，这种情况下实现最佳性能。

MANUAL：管理人员需要负责指定具体的并发度，不过一般情况下，MySQL并发对象建议使用参数PARALLEL_MAX_SERVERS来控制如何分配服务器资源，或者PARALLEL_THREADS_PER_CPU来影响Oracle如何分配CPU等资源。

LIMITED：表示Oracle只允许使用有限的并发程度，表示Oracle只允许使用参数PARALLEL_EXECUTION_MESSAGE_SIZE设置的字节数作为并发度。

正确设置Oracle参数：

1.根据系统业务需求正确选择并发度控制策略，一般情况下Oracle会根据用户需求自动控制并发程度，也可以选择手动设置。

2.根据实际需求正确设置相应的参数。比如：MANUAL选项配置时，需要根据应用目的来设置PARALLEL_MAX_SERVERS和PARALLEL_THREADS_PER_CPU等参数；LIMITED选项配置时，需要正确设置PARALLEL_EXECUTION_MESSAGE_SIZE参数。

3.注意监控实例的具体数据库统计情况，定期调整参数设置，确保实例的性能。

官方英文解释

PARALLEL_DEGREE_POLICY specifies whether automatic degree of parallelism, statement queuing, and in-memory parallel execution will be enabled.

Property	Description
Parameter type	String
Syntax	`PARALLEL_DEGREE_POLICY = { MANUAL \| LIMITED \| AUTO \| ADAPTIVE }`
Default value	`MANUAL`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`
Modifiable in a PDB	Yes
Basic	No

Values

Note:

Automatic degree of parallelism will be enabled regardless of the value of PARALLEL_DEGREE_POLICY if a PARALLEL hint is used at the SQL statement level.

MANUAL

Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.
LIMITED

Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the DEFAULT degree of parallelism using the PARALLEL clause. Statements that do not access any tables or indexes decorated with the DEFAULT degree of parallelism will retain the MANUAL behavior.
AUTO

Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
ADAPTIVE

This value enables automatic degree of parallelism, statement queuing and in-memory parallel execution, similar to the AUTO value. In addition, performance feedback is enabled. Performance feedback helps to improve the degree of parallelism automatically chosen for repeated SQL statements. After the initial execution of a statement, the degree of parallelism chosen by the optimizer is compared to the degree of parallelism computed based on the actual execution performance. If they vary significantly, then the statement is marked for re-parse and the initial execution performance statistics (for example, CPU-time) are provided as feedback for subsequent executions. The optimizer uses the initial execution performance statistics to better determine a degree of parallelism for subsequent executions.

本站中文解释

官方英文解释

相关推荐