The so-called parallel execution refers to the physical division of a large serial task (any DML, General DDL) into multiple small parts, which can be processed at the same time.
The so-called parallel execution refers to the physical division of a large serial task (any DML, General DDL) into multiple small parts, which can be processed at the same time.
Parallel Oracle execution
Parallel Execution refers to the physical division of a large serial task (any DML, General DDL) into multiple small parts, these smaller parts can be processed at the same time.
When to use parallel execution:
1. There must be a very large task
2. Sufficient resources (CPU, I/O, and MEMORY) are required)
Parallel Query
Parallel query allows you to divide an SQL SELECT statement into multiple smaller queries, and each query runs concurrently. Finally, combine the results of each small query to obtain the final result.
Start parallel query in several ways:
1. When a hint is used in a query, the following error occurs: select/* + parallel (4) */count (*) from test_a; --- specify a parallel query with a degree of parallelism of 4.
2. Use alter table to modify a table:
Alter table test_a parallel 4; -- tells oracle to use parallelism Level 4 when creating the execution plan for this table
Alter table test_a parallel; -- tell objective El, "Please consider parallel execution ." Oracle determines the proper degree of Parallelism Based on the system workload and query itself. Resources are sufficient, concurrency increases, resources are scarce, and concurrency decreases. Oracle can dynamically increase or decrease the amount of concurrent resources required for queries. --- You prefer to use this method to start parallel queries.
Observe the parallel query through the execution plan of a parallel query
SQL> alter table test_a parallel;
Table altered.
Elapsed: 00:00:00. 05
SQL> select count (*) from test_a;
COUNT (*)
----------
602704
Elapsed: 00:00:00. 21
Execution Plan
----------------------------------------------------------
Plan hash value: 2663325361
Bytes --------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time | TQ |IN-OUT| PQ Distrib |
Bytes --------------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 1290 (1) | 00:00:16 |
| 1 | sort aggregate | 1 |
| 2 | px coordinator |
| 3 | px send qc (RANDOM) |: TQ10000 | 1 | Q1, 00 | P-> S | QC (RAND) |
| 4 | sort aggregate | 1 | Q1, 00 | PCWP |
| 5 | px block iterator | 602K | 1290 (1) | 00:00:16 | Q1, 00 | PCWC |
| 6 | table access full | TEST_A | 602K | 1290 (1) | 00:00:16 | Q1, 00 | PCWP |
Bytes --------------------------------------------------------------------------------------------------------
Next, let's take a look at the highlights of page 2nd:
Recommended reading:
Oracle DML Process
PL/SQL ORA-14551: cannot perform DML operations in queries solved
Common MySQL DDL, DML, and DCL languages (example)
Execute batch DML exercises for Oracle basic transactions and ForAll
Oracle DML Statement (insert, update, delete) rollback Estimation