Oracle "Parallel Execution" of the second-parallel execution type

Excerpt from the VLDB and partitioning guide

There are several main types of parallel execution:

1) Parallel query

2) Parallel DDL

3) Parallel DML

4) Parallel execution function

5) Other types of parallelism

The following are described separately, mainly including two aspects: can be parallel, determine DOP.

1. Parallel query

You can execute queries and subqueries in parallel in the SELECT statement, or you can execute the query section in parallel in DDL and DML statements. You can also query external tables in parallel. Parallelism consists of two parts: determining whether to parallel, determine the degree of parallelism (DOP). For queries, DDL, and DML, these two parts are determined differently. In order to determine the dop,oracle database you need to check the reference object (the object that determines DOP):

1) for a parallel query (the part of the query that will be executed in parallel), the database determines which object is the reference object by examining each table and index in the statement. The basic principle is to select the highest DOP table or index.

2) for parallel DML (INSERT, UPDATE, MERGE, and DELETE), the Reference object is the table that modifies the data. Parallel DML adds some restrictions to DOP to prevent deadlocks. If a parallel DML statement contains subqueries, then the DOP of the subquery is equal to the DOP of the DML statement.

3) for parallel DDL, the Reference object is the table, index, or partition that is created, rebuilt, split, or moved. If the parallel DDL statement contains subqueries, then the DOP of the subquery equals the DOP of the DDL statement.

Determine if parallel

Whether the SELECT statement can be executed in parallel depends on the following conditions:

1) The statement contains the parallel hint (parallel or parallel_index), or the reference object has a parallel attribute.

2) At least one table in the statement includes the following operations:

–A Full table Scan

–an Index range scan spanning multiple partitions

3) No scalar subquery in select list

Determining the degree of parallelism (DOP)

The DOP of the query depends on the following rules:

1) The DOP of the query depends on the maximum DOP of the Reference object in the statement.

2) If a table has both a parallel hint and a parallel attribute, the DOP of the parallel hint takes precedence over the DOP of the parallel property.

2. Parallel DDL

DDL statements that can be executed in parallel

For non-partitioned tables or indexes, the following statements are included:

CREATE INDEX

CREATE TABLE ... As SELECT

ALTER INDEX ... REBUILD

For partitioned tables or indexes, the following statements are included:

CREATE INDEX

CREATE TABLE ... As SELECT

ALTER TABLE ... [move| Split| COALESCE] PARTITION

ALTER INDEX ... [rebuild| SPLIT] PARTITION

All of these DDL operations can be executed in a nologging manner, regardless of whether they are parallel or not.

For IoT tables, the CREATE table operation can be executed in parallel, whether or not an AS SELECT clause is included.

Different operations have different degrees of parallelism (see table 8–2). Create table based on partition table ... As select parallel statements, CREATE index parallel statements based on partitioned indexes, their DOP equals the number of partitions.

A table that contains the object type columns and cannot execute parallel DDL. A non-partitioned table that contains LOB columns and cannot execute parallel DDL.

Parallel execution of Create TABLE ... As SELECT statement

This statement is especially useful when creating a summary table.

The cluster table cannot be created and manipulated in parallel.

Figure 8–4 describes the parallel creation summary table.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clipboard "border=" 0 "alt=" clipboard "src=" Http://s3.51cto.com/wyfs02/M00/82/8A/wKiom1dYJm _ijzxwaadozhnyoui095.png "" 644 "height=" 395 "/>

Parallel DDL and data recovery

Parallel DDL is often used to create summary tables or bulk data loads in a transaction, usually without requiring recoverability. With the logging feature turned off, the system does not generate undo and redo logs, and the performance of parallel DDL operations is better, but this operation becomes an "all or nothing" operation. In other words, if the operation fails, you have to redo it from the beginning and not restart from the middle.

If you disable logging while creating tables in parallel, you need to back up the tablespace to prevent data loss after the creation is successful.

For Create TABLE, CREATE INDEX, ALTER TABLE, and ALTER INDEX operations, you can use the nologging clause to suppress the generation of undo and redo.

Spatial management of parallel DDL

Creating tables and indexes in parallel has a spatial management implication that includes both the storage space requirements during the execution of parallel operations and the amount of free space available after the creation is successful.

Spatial requirements when using a dictionary to manage table spaces

When tables or indexes are created in parallel, each parallel server creates a temporary segment using the storage clause in the CREATE statement to store the data rows. Therefore, the process of creating a table with a next property of 4MB, PARALLEL degree 16, requires at least 64MB of space, and each parallel server starts with 4MB extent. When the parallel coordinator merges these segments, some segments may be trimmed off, and the last space may be less than 64MB.

Free space and parallel DDL

You can have at least one extent when you create a single object serially, and each parallel server has a minimum of one extent when you create the table or index in parallel. When the size of these temporary segments exceeds the required amount of space, it is possible to generate an idle area.

1) When the remaining space in the temporary segment is larger than the Tablespace minimum extent property size, the parallel coordinator merges the data rows in the temporary segment to prune the unused space and release it back to the system. These free spaces can be used as new extent allocations, but cannot be combined into a larger extent for use because they are not contiguous spaces.

2) When the space remaining in the temporary segment is less than the Tablespace minimum Extent property size, these unused spaces are not released back to the system by pruning. They become part of the table or index space and can only be used when additional space is required for subsequent insert\update operations.

See Figure 8-5.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clipboard[1] "border=" 0 "alt=" clipboard[1] "src=" http://s3.51cto.com/wyfs02/M01/82/8A/ Wkiom1dyjndqvxbraaevu9a94yc165.png "" 644 "height=" 476 "/>

DDL Statement Rules

• Determine if parallel

If the parallel clause is specified in the syntax, the DDL statement executes in parallel. For CREATE INDEX and ALTER index ... REBUILD or ALTER INDEX ... REBUILD the partition statement, the parallel clause attribute is stored in the data dictionary.

You can use the ALTER session Force PARALLEL DDL statement to overwrite the PARALLEL property of subsequent DDL statements in the same session.

• Determining the degree of parallelism

DOP is determined by the PARALLEL property of the object or index, unless overridden by the Alter SESSION Force PARALLEL DDL statement.

When a DDL statement contains an explicit parallel hint, it also overrides the parallel property.

[CREATE | REBUILD] INDEX or [MOVE | SPLIT] Partition statement rules

The rules for creating or modifying an index are as follows.

• Parallel CREATE INDEX or ALTER index ... REBUILD

CREATE index and ALTER index ... Rebuild statements can only be executed in parallel by PARALLEL clauses or alter SESSION FORCE PARALLEL DDL statements.

ALTER INDEX ... The rebuild statement can only operate on non-partitioned indexes in parallel.

ALTER INDEX ... REBUILD (non-partitioned), ALTER INDEX ... The REBUILD PARTITION, and the CREATE index, scan operations of these statements are performed in parallel, with the same degree of parallelism as the REBUILD and create operations. If the rebuild or create operation does not specify a degree of parallelism, the degree of parallelism defaults to the number of CPUs.

• Parallel move PARTITION or split PARTITION

ALTER INDEX ... MOVE PARTITION and ALTER INDEX ... The SPLIT partition statement can only be executed in parallel through the PARALLEL clause or the ALTER SESSION Force PARALLEL DDL statement. Their scanning operations are also performed in parallel, and the degree of parallelism is consistent with the corresponding move or split operation. If you do not specify a degree of parallelism, the degree of parallelism defaults to the number of CPUs.

CREATE TABLE AS SELECT statement rule

CREATE TABLE ... The AS SELECT statement contains two parts: the Create section (DDL) and the Select section (query). Both of these parts can be executed in parallel. The parallel rules of the DDL section are consistent with other DDL.

• Whether the query part is parallel

CREATE TABLE ... The query part of the AS SELECT statement can be parallelized only if the following conditions are met:

1) The query section contains the parallel or parallel_index hint, or the DDL section contains a parallel statement, or the Reference object in the query section explicitly specifies the parallel attribute.

2) At least one of the tables in the Query section is a full table scan or a multi-partition index range scan.

• Query partial DOP

CREATE TABLE ... The DOP in the query portion of the AS SELECT statement is determined by one of the following conditions:

1) The query section uses the value specified by the parallel clause in the DDL section.

2) If the DDL section does not specify a parallel clause, the default DOP is the number of CPUs.

3) If the DDL part is executed serially, the DOP is determined by the query itself.

Note: The values specified by the query section parallel hint are ignored.

• Whether the DDL part is parallel

CREATE TABLE ... The create operation of the AS SELECT statement can only be executed in parallel through the PARALLEL clause or alter SESSION Force PARALLEL DDL.

When Create TABLE ... When the create operation of the AS SELECT statement executes in parallel, the corresponding scan operation is executed in parallel as much as possible. The scan operation does not execute in parallel when the following conditions occur:

1) SELECT clause has no_parallel hint;

2) scan a non-partitioned index.

When the create operation is executed serially, the Select section executes in parallel if there is a parallel hint or if the reference object specifies the parallel property.

• DDL partial DOP

The DOP of the Create statement, depending on the PARALLEL clause of the CREATE statement, and the alter SESSION Force PARALLEL DDL statement. If the parallel clause does not specify DOP specifically, the default is the number of CPUs.

3. Parallel DML

Parallel DML (PARALLEL INSERT, UPDATE, DELETE, and MERGE) uses a parallel execution mechanism to speed up large DML operations on large tables or indexes.

Note: Although DML operations contain queries, this chapter's DML refers only to the INSERT, UPDATE, MERGE, and delete operations.

When to use parallel DML

In a DSS environment, parallel DML is important because it is often necessary to access large objects. Parallel DML complements the functionality of parallel queries, providing both query and update capabilities for your DSS environment.

For OLTP environments, it is not feasible to over-set parallel DML, but can be used to speed up the execution of bulk tasks.

1) Refresh large table data periodically in the Data Warehouse environment. You can consider using the merge statement.

2) Create an intermediate summary table.

3) scoring table.

4) Update the history table.

5) Run the bulk task.

Enable parallel DML

Parallel DML can only be explicitly enabled, because parallel DML and serial DML have different locks, transactions, and disk space requirements. Use the following statement to enable:

ALTER SESSION ENABLE PARALLEL DML;

Parallel DML in a session is disabled by default, and parallel DML is not enabled even if the parallel hint is specified.

When parallel DML is enabled, DML still does not parallelize if there is no parallel hint, or if the object does not have a parallel attribute, or if the parallel operation restriction is violated.

The parallel DML pattern of the session does not affect the parallelism of the SELECT statement, the DDL statement, and the query portion of the DML statement. Therefore, if parallel DML mode is not set, DML operations are not parallel, and the scan or join operations in DML statements can still operate in parallel.

UPDATE, MERGE, and delete parallel rules

For update, MERGE, and delete operations, you have two ways to specify parallel directives (of course, in cases where parallel DML is enabled):

1) The table that treats the operation uses the parallel clause to define its properties.

2) Use the parallel hint. This prompt will also be used for scanning operations on the table to be operated on.

You can use the Alter SESSION force PARALLEL DML statement to overwrite the PARALLEL property of the table to be manipulated. The PARALLEL hint can also override the parallel nature of the alter SESSION force PARALLEL DML statement.

• Determine if parallel

The following rules determine whether the update, MERGE, or delete are executed in parallel. An UPDATE or delete operation is performed in parallel only if it meets at least one of the following conditions:

1) The table to be manipulated has the parallel property.

2) There is a parallel hint in the DML statement.

3) The session executes alter session Force PARALLEL DML.

DML statements may contain subqueries or updatable views, these subqueries or updatable views may have their own Paralle hints or parallel properties. However, these parallel directives do not affect the parallelism of the update, MERGE, or delete operations.

Parallel the parallel property on the hint or table, and applies to the query, UPDATE, MERGE, or delete operation of the DML statement. However, the parallelism of the update, MERGE, or delete operations in a DML statement is independent of the query operation.

• Determining DOP

The same rules as the query section.

Priorities are: MERGE, UPDATE, prompt on Delete, session parallelism, parallel property of the target table.

INSERT ... Select parallel Rules

INSERT ... The SELECT statement can be executed in parallel with the insert and select operations, but the DOP requirements for the two operations are consistent.

You can specify the parallel hint immediately following the INSERT keyword to specify the parallelism of the insert operation (the table in the Select section is often different from the insert operation, and the parallel hint is only for the insert operation).

For insert ... Select statement, you have several ways to specify the PARALLEL directive (PARALLEL DML is enabled):

1) Select section specifies the parallel hint;

2) The table in the Select section specifies the parallel attribute;

3) Insert section specifies the parallel hint;

4) Specify the parallel property for the table you want to insert.

You can overwrite the PARALLEL property of the Insert Action object in the session with the alter session Force PARALLEL DML statement. The PARALLEL hint can also overwrite the alter SESSION force PARALLEL DML statement.

• Determine if parallel

The insert operation is executed in parallel only if there is at least one of the following conditions:

1) Insert operation specifies parallel hint;

2) The Insert object has the parallel property;

3) ALTER SESSION Force PARALLEL DML statement is executed.

Note: The parallelism of the insert operation is independent of the parallelism of the select operation and vice versa.

• Determining DOP

According to the following order to determine the dop:insert part of the Paralle hint, session parallel statement, to insert Table parallel property, select part of the maximum Dop.

Example 8–6, determine the DOP to be 2 according to the parallel hint in insert:

INSERT/*+ PARALLEL (tbl_ins,2) */into Tbl_ins

SELECT/*+ PARALLEL (tbl_sel,4) */* from Tbl_sel;

Parallel DML Throttling

The limitations of parallel DML are (Direct-path INSERT):

1) for parallel update, MERGE, and DELETE within the partition (Intra-partition), the compatible parameter is required to reach at least 9.2.

2) The INSERT values operation never parallels.

3) A transaction contains multiple parallel DML statements that modify different tables. When a parallel DML statement modifies a table, no subsequent serial or parallel statement (DML or query) of the transaction can access the table again.

This restriction also applies to the Direct-path INSERT statement: Any subsequent serial or parallel statement (DML or query) of this transaction cannot access the table again.

Parallel DML or Direct-path insert allows querying the same table before it is not allowed.

Denied access to a serial or parallel statement of the same table will cause an error.

4) A table with a trigger cannot parallel DML.

5) The replication feature does not support parallel DML.

6) Some constraint cases do not support parallel dml:self-referential integrity, delete cascade, and deferred integrity. Also, for Direct-path INSERT, parallel DML is not supported for all referential integrity constraints.

7) For tables that contain an object column, parallel DML is allowed if the object column is not stored.

8) Allow parallel DML for partitioned tables that contain LOB columns. However, intra-partition (intra-partition) parallelism is not allowed.

9) Distributed transactions do not allow parallel DML.

10) The cluster table does not support parallel DML.

11) Temporary tables do not support parallel update, DELETE, and merge operations.

Several examples of distributed transaction parallelism:

The Example 8–7,DML statement accesses a remote object. The query operation is executed serially because it references a remote object.

Example 8–7

INSERT/*+ APPEND PARALLEL (t3,2) */into T3 SELECT * from [email protected];

The Example 8–8,delete operation does not work in parallel because it references a remote object.

Example 8–8

DELETE/*+ PARALLEL (T1, 2) */from [email protected];

The Example 8–9,DML operation is a distributed transaction. The delete operation is not parallel because it occurs in a distributed transaction (this distributed transaction starts with a SELECT statement).

Example 8–9

SELECT * from [email protected];

DELETE/*+ PARALLEL (t2,2) */from T2;

COMMIT;

4. Parallel execution function

The SQL statement may contain user-defined functions. When SQL statements are executed in parallel, these functions are executed by executing the server in parallel on each row of data. A PL/SQL package variable or Java static property that is called by a function is completely private between each parallel server, and when each row of data is processed, they are not copied from the original session but are reinitialized. Because of this, some functions executing in parallel will return incorrect results.

User-defined table functions can be used in the FROM clause. They output rows of data just like the source table. These table functions are initialized at the beginning of each parallel server of the SQL statement. All of their variables are completely private between each parallel server.

Functions in a parallel query

In a subquery of a SELECT statement or DML/DDL statement, a user-defined function can run in parallel in the following cases:

1) Use parallel_enable keyword to declare;

2) Declare the use of pragma restrict_references clauses in package/type (Specify Wnds, Rnps, and Wnps);

3) Declare using the CREATE function, and the system discovers that it has no write data and does not read or modify variables in the package by analyzing its PL/SQL code content.

Note: Even if a function can only be executed serially, other parts of the query or subquery statement may be executed in parallel.

Functions in parallel DML and DDL

Parallel DML/DDL statements, like parallel query statements, user-defined functions can run in parallel under the following circumstances:

1) Use parallel_enable keyword to declare;

2) Declare the use of pragma restrict_references clauses in package/type (Specify Wnds, Rnps, and Wnps);

3) Declare using the CREATE function, and the system discovers that it has no write data and does not read or modify variables in the package by analyzing its PL/SQL code content.

For parallel DML statements, if a function call cannot be executed in parallel, the entire DML statement can only be executed serially. For insert ... SELECT or Create TABLE ... As SELECT statements, function calls in the query section can be executed in parallel (as described in the "functions in parallel Queries" section). The query part may execute in parallel, even if the other parts of the statement can only be executed serially, and vice versa.

5, other types of parallel

In addition to parallel SQL execution, the following parallel features are supported by the Oracle database:

Parallel recovery

Parallel replication

Parallel data loading (about external tables and SQL Loader tools)

As with parallel SQL execution, parallel recovery, parallel replication, and external table loading are done by parallel execution coordinators and parallel execution servers. Loading data in parallel through Sql*loader is another mechanism.

Parallel coordinators, parallel servers, behave differently depending on the type of parallelism (SQL, replication, external table loading). For example, when a server process in a parallel server pool is occupied and the number of parallel servers has reached the maximum system set, then:

1) In the case of parallel SQL execution and parallel external table loading, the parallel execution coordinator is executed serially instead.

2) in the case of parallel replication, the parallel coordinator will error.

For a given session, the parallel coordinator can only reconcile one type of parallel activity, for example, parallel SQL, parallel replication, and parallel external table loading cannot be coordinated at the same time.

6. Summary of parallel rules

Table 8–2 describes how different types of SQL statements are executed in parallel, and the prioritization of methods that specify parallelism.

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clipboard[2] "border=" 0 "alt=" clipboard[2] "src=" http://s3.51cto.com/wyfs02/M01/82/89/ Wkiol1dyj3vtp7iiaamcrdauadi736.png "" 644 "height=" 449 "/>

650) this.width=650; "Style=" background-image:none; border-bottom:0px; border-left:0px; padding-left:0px; padding-right:0px; border-top:0px; border-right:0px; padding-top:0px "title=" clipboard[3] "border=" 0 "alt=" clipboard[3] "src=" http://s3.51cto.com/wyfs02/M02/82/8A/ Wkiom1dyjnpglnumaaejf67l6ci945.png "" 644 "height=" 238 "/>

Oracle "Parallel Execution" of the second-parallel execution type