Shulou(Shulou.com)06/01 Report--

Which scenarios can use parallelism?

Table scan, fast full index scans, partition index range scans (local index only)

Create table as, create index, rebuild index, move, split, DML (insert\ update\ delete)

When parallel execution is used, there is an extra column in SQL's execution plan: in-out. Related views are: v$pq_sysstat, v$pq_tqstat

Alter session force parallel query parallel N-N parallelism is enforced, and force parallel overrides the default parallel setting

Alter session enable parallel query-use the default number of parallelism

The method of opening parallelism of table

Alter table sales parallel

Alter table sales parallel 8

Hint enables the parallelism of table queries

Select / * + parallel (tablename,8) * / * from tablename

Select / * + parallel (sales,8) * / * from sales

When DML uses hint to enable parallelism, the table must enable parallelism, because in update, the data of the table must be found first, that is, select must be executed first, so parallelism is enabled in update and parallelism is also enabled in select, otherwise only update is parallelism, but select is not.

Parallelism GRANULES

1. A basic unit of parallel work is called GRANULES

2. A GRANULES can only have one server executed in parallel to read.

3. A parallel server can execute tasks from one GRANULES to another GRANULES, that is, when one task is completed, it moves on to the next task.

There are two kinds of parallelism GRANULES.

1.block range granules (automatically divided according to the number of blocks)

Block range granules: Server processes are generated dynamically when they are executed.

2.partition granules (for partitioned tables, for example, with 8 partitions, it may be divided into four parallelism, so that four parallelism may be on four different partitions)

Partition granules: a static decision on the number of partitions.

Application scenarios of three famous parameters

PARALLEL_DEGREE_POLICY

PARALLEL_MIN_PERCENT

PARALLEL_SERVERS_TARGET

For example, 80 CPU, now 60 have been used, the remaining 20 (PARALLEL_SERVERS_TARGET), now there is a 60 degree of parallelism query, what to do?

If PARALLEL_MIN_PERCENT=10, that is, 10% can be used, that is, at least 60% 10% 6 parallelism, 620, if PARALLEL_DEGREE_POLICY=MANUAL, it is not enough, directly report an error, if PARALLEL_DEGREE_POLICY=AUTO, enter the queue.

PARALLEL_FORCE_LOCAL controls parallel execution in an Oracle RAC environment. By default, the parallel server processes selected to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster. By setting PARALLEL_FORCE_LOCAL to true, the parallel server processes are restricted so that they can only operate on the same Oracle RAC node where the query coordinator resides (the node on which the SQL statement was executed on).

Controls parallel execution in the Oracle RAC environment. By default, parallel server processes that choose to execute SQL statements can run on any or all Oracle RAC nodes in the cluster. Parallel server processes are restricted by setting PARALLEL_FORCE_LOCAL to true, so they can only run on the same Oracle RAC node where the query coordinator is located (the node where the SQL statement is executed)

The default FORCE, which means that you can use the CPU of other nodes, does not mean that you can execute SQL on other nodes and execute SQL on this node (that is, PX is on this node), but you can use the resources of other nodes to do coordinator process or QC (that is, a process that manages the following parallelism, and each parallel program is called parallel execution servers, that is, PX). This parameter is best set to TRUE.

PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle Database increases the number of processes from the number created at instance startup up to this value

PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for the instance. As the demand increases, the Oracle database increases the number of processes created when the instance is started to this value

PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started

PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes created by Oracle when the instance is started

PARALLEL_MIN_SERVERS defaults to 0. If you change it to 5, it means that even if you are not idle, you can start 5 processes. The related view is v$px_process.

For example, there are five processes such as ora_p001_sid, ora_p002_sid, ora_p003_sid, ora_p004_sid and ora_p004_sid as soon as they are started. This is partition slave process they do parallel dml ddl and query jobs...

PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism,statement queuing, and in-memory parallel execution will be enabled

PARALLEL_DEGREE_POLICY specifies whether parallelism is enabled, the degree of automation of statement queuing and parallel execution in memory

PARALLEL_MIN_PERCENT lets you specify the minimum percentage of the requested number of parallel execution processes required for parallel execution. This parameter controls the behavior for parallel operations when parallel statement queuing is not enabled (when PARALLEL_DEGREE_POLICY is set to manual or limited). It ensures that an operation always gets a minimum percentage of parallel execution servers or errors out. Setting this parameter ensures that parallel operations will not execute unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set.

If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

PARALLEL_MIN_PERCENT can specify the minimum number of parallel execution processes required for parallel execution. This parameter controls the behavior of parallel operations when the parallel statement queue is not enabled (PARALLEL_DEGREE_POLICY is set to manual or limited). It ensures that the operation always gets the minimum percentage or error of the parallel execution server. Setting this parameter ensures that parallel operations are not performed unless sufficient resources are provided. The default value of 0 means that the minimum process percentage is not set.

PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available.

PARALLEL_SERVERS_TARGE specifies the number of parallel server processes that are allowed to run parallel statements before queuing using statements. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle queues SQL statements that need to be executed in parallel if the required parallel server processes are not available.

PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 10 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before considering the degree of automatic parallelism. By default, it is set to 10 seconds. Automatic parallelism can only be used when PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

PARALLEL_ADAPTIVE_MULTI_USER, when set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction facto

PARALLEL_ADAPTIVE_MULTI_USER, when set to true, enables an adaptive algorithm designed to improve the performance of multiuser environments that use parallel execution. The algorithm automatically reduces the parallelism of the request according to the system load when the query is started. The effectiveness of parallelism is based on the default parallelism, or divided by the restore factor from the degree of the table or prompt.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.