Parallel Partitioning Execution Plan
Two execution plans.
Disable optimizer_dynamic_sampling firstly.
alter session set optimizer_dynamic_sampling = 0;
Then, create two simple tables;
CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER ); CREATE TABLE t2 ( c1 NUMBER, c2 NUMBER );
hr@ORCL> SELECT /*+ parallel(t1,2) */ t1.c2,t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1; Execution Plan ---------------------------------------------------------- Plan hash value: 869483324 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 82 | 4264 | 5 (20)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 82 | 4264 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 82 | 4264 | 5 (20)| 00:00:01 | Q1,01 | PCWP | | | 4 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 5 | TABLE ACCESS FULL | T1 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST | | 9 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| T2 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."C1"="T2"."C1") hr@ORCL> SELECT /*+ parallel(t1,3) */ t1.c2,t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1; Execution Plan ---------------------------------------------------------- Plan hash value: 3683239666 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 82 | 4264 | 5 (20)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 82 | 4264 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 82 | 4264 | 5 (20)| 00:00:01 | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| T1 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| T2 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."C1"="T2"."C1")
Why the PQ distribution change from Broadcast to Hash when DOP(degree of parallel) changes from 2 to 3(or above)?
Note that TQ(Table Queue) also change from 2 to 3.
But let’s change to another principle questions, how many PQ distribute way exists? Can I change them by my own? Refer to my next article for answers.
Another principle question: how to monitor the parallel execution except the execution plan? how about the trace, V$ views, or other things? Refer to my next next article for answers!
So why the optimizer chose different execution plan according to different DOP? Simple to say, this is based on the statistics on the tables. And here, remember, the statistics for these tables here are all "default" statistics, which is not suitable for parallel execution actually. Where to use a specific PQ distribute method could be another deep topic.
Create two partitioned tables:
CREATE TABLE t1(c1 NUMBER, c2 NUMBER) PARTITION BY RANGE (c1) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30), PARTITION p4 VALUES LESS THAN (40) ); CREATE TABLE t2(c1 NUMBER, c2 NUMBER) PARTITION BY RANGE (c1) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30), PARTITION p4 VALUES LESS THAN (40) );
hr@ORCL> SELECT /*+ PARALLEL (t1,1) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1; Execution Plan ---------------------------------------------------------- Plan hash value: 1604022418 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 328 | 12792 | 8 (25)| 00:00:01 | | | | 1 | PARTITION RANGE ALL| | 328 | 12792 | 8 (25)| 00:00:01 | 1 | 4 | |* 2 | HASH JOIN | | 328 | 12792 | 8 (25)| 00:00:01 | | | | 3 | TABLE ACCESS FULL| T1 | 328 | 8528 | 3 (0)| 00:00:01 | 1 | 4 | | 4 | TABLE ACCESS FULL| T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C1"="T2"."C1") hr@ORCL> SELECT /*+ PARALLEL (t1,2) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1; Execution Plan ---------------------------------------------------------- Plan hash value: 2042620038 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | PX BLOCK ITERATOR | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | | | 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST LOCAL| :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | BCST LOCAL | | 9 | PARTITION RANGE ALL | | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | | | 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."C1"="T2"."C1") hr@ORCL> SELECT /*+ PARALLEL (t1,4) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1; Execution Plan ---------------------------------------------------------- Plan hash value: 2117927142 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | PX BLOCK ITERATOR | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | | | 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | BROADCAST | | 9 | PARTITION RANGE ALL| | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | | | 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | | ----------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."C1"="T2"."C1") hr@ORCL> SELECT /*+ PARALLEL (t1,5) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1; Execution Plan ---------------------------------------------------------- Plan hash value: 2042834191 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | PX PARTITION RANGE ALL | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | | | 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 8 | PX SEND PARTITION (KEY)| :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | PART (KEY) | | 9 | PARTITION RANGE ALL | | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | | | 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."C1"="T2"."C1") hr@ORCL>
Also the PQ distribution change from "Broadcast Local" to "Broadcast" to "Part(Key)" when DOP(degree of parallel) changes from 2 to 4 to 5(or above).
Why? Maybe, in the further, I will use an more realistic case to study and investigate why.