The so-called parallel execution refers to the ability to physically divide a large serial task (any DML, or general DDL) into several smaller parts, which can be processed at the same time.
When to use parallel execution
Parallel execution is essentially a non scalable solution designed to allow a single user or each specific SQL statement to occupy all resources of the database. If a feature allows one person to use all available resources, and two more people to use it, there will be obvious competition.
Before the application is executed in parallel, the following two points need to be guaranteed:
There must be a very large task, such as a comprehensive scan of 50GB data.
There must be enough resources available. Before scanning 50GB data in parallel, you need to make sure that you have enough free CPU (to accommodate parallel processes), enough I/O channels, and so on.
2 parallel query
scott@ORCL>explain plan for 2 select count(status) from big_table; //Explained. scott@ORCL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------- Plan hash value: 599409829 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 47422 (1)| 00:09:30 | | 1 | SORT AGGREGATE | | 1 | 7 | | | | 2 | TABLE ACCESS FULL| BIG_TABLE | 11M| 76M| 47422 (1)| 00:09:30 | -------------------------------------------------------------------------------- //9 rows selected.
This is a typical serial plan. Parallelization is not involved here, because we did not request to enable parallel queries, which are not enabled by default.
There are many ways to enable parallel query, you can use a prompt directly in the query, or modify the table, which requires considering parallel execution path.
You can specify the parallelism to be considered in the execution path of this table. For example, you can tell Oracle, "we want you to use parallelism 4 when creating an execution plan for this table":
scott@ORCL>alter table big_table parallel 4; Table changed.
But I prefer to tell Oracle, "consider parallel execution, but you need to determine the appropriate degree of parallelism based on the current system workload and the query itself.". In other words, the degree of parallelism changes with the increase or decrease of workload on the system. If there are enough free resources, the parallelism will increase; if the available resources are limited, the parallelism will decrease. This will not impose a fixed degree of parallelism on the machine. With this method, Oracle is allowed to dynamically increase or reduce the amount of concurrent resources required for queries.
Therefore, we only use the following ALTER TABLE command to enable parallel queries on this table:
scott@ORCL>alter table big_table parallel; Table changed.
That's it. Now, the operation on this table will consider parallel queries. Rerun the explain plan and you may see the following results:
scott@ORCL>explain plan for 2 select count(status) from big_table; //Explained. scott@ORCL>select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------- Plan hash value: 2894119656 -------------------------------------------------------------------------------- --------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------- --------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 6574 (1)| 00:01: 19 | | | | | 1 | SORT AGGREGATE | | 1 | 7 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 7 | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | 7 | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 11M| 76M| 6574 (1)| 00:01: 19 | Q1,00 | PCWC | | | 6 | TABLE ACCESS FULL| BIG_TABLE | 11M| 76M| 6574 (1)| 00:01: 19 | Q1,00 | PCWP | | -------------------------------------------------------------------------------- --------------------------------- //13 rows selected.
To be continued P750