Relationship between DOP and Number of Hash Partitions (1)
In 10gR2 or 11gR1, when doing partition wise join, the number of hash partitions should be equal to DOP. Otherwise, the execution plan must become worse.
First, I will create two tables, fact_a and fact_b, both are partitioned by hash, and have hash partitions 4.
SQL> CREATE TABLE fact_a( 2 date_key date, 3 part_col1 NUMBER 4 ) 5 PARTITION BY HASH (part_col1) PARTITIONS 4 6 parallel 7 ; Table created. SQL> CREATE TABLE fact_b( 2 date_key date, 3 part_col1 NUMBER 4 ) 5 PARTITION BY HASH (part_col1) PARTITIONS 4 6 parallel 7 ; Table created.
Then, insert some data into both tables, and gather statistics.
SQL> insert into fact_a select null,rownum from dba_objects, dba_objects where rownum<1000000; 999999 rows created. SQL> insert into fact_b select null,rownum from dba_objects, dba_objects where rownum<100000; 99999 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(user,'FACT_A'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(user,'FACT_B'); PL/SQL procedure successfully completed.
Then I will execute the SQL statement below, with different DOPs, using the hint such as “parallel(a 4)”.
select * from fact_a a, fact_b b where a.part_col1 = b.part_col1;
1. Default DOP
SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes FROM v$pq_tqstat ORDER BY dfo_number, tq_id, server_type DESC, process; DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES ---------- ---------- --------------- ---------- ---------- ---------- 1 0 Producer P000 50099 798746 1 0 Producer P001 49900 795506 1 0 Consumer QC 99999 1594252 Execution Plan ---------------------------------------------------------- Plan hash value: 196040303 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 976K| 128 (10)| 00:00:02 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 99999 | 976K| 128 (10)| 00:00:02 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION HASH ALL| | 99999 | 976K| 128 (10)| 00:00:02 | 1 | 4 | Q1,00 | PCWC | | |* 4 | HASH JOIN | | 99999 | 976K| 128 (10)| 00:00:02 | | | Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | FACT_B | 99999 | 488K| 12 (9)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | | | 6 | TABLE ACCESS FULL | FACT_A | 995K| 4860K| 112 (7)| 00:00:02 | 1 | 4 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."PART_COL1"="B"."PART_COL1")
On my system, the cpu count is 2, so the default DOP will be 4. But the actual DOP is 2, as the query above indicated, this is governed by the Database Resource Manager, as I have limited the maximum parallelism degree of user peta to be 2.
This execution plan is what we want to get.
2. paralllelism is 2 ( < Number of Partitions)
SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes FROM v$pq_tqstat ORDER BY dfo_number, tq_id, server_type DESC, process; DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES ---------- ---------- --------------- ---------- ---------- ---------- 1 0 Producer P002 500024 4031706 1 0 Producer P003 499975 4031490 1 0 Consumer P000 500024 4031706 1 0 Consumer P001 499975 4031490 1 1 Producer P000 50164 799700 1 1 Producer P001 49835 794572 1 1 Consumer QC 99999 1594272 Execution Plan ---------------------------------------------------------- Plan hash value: 2613174272 -------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 976K| 256 (10)| 00:00:04 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 99999 | 976K| 256 (10)| 00:00:04 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 99999 | 976K| 256 (10)| 00:00:04 | | | Q1,01 | PCWP | | | 4 | PX BLOCK ITERATOR | | 99999 | 488K| 23 (5)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | | | 5 | TABLE ACCESS FULL | FACT_B | 99999 | 488K| 23 (5)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | 995K| 4860K| 224 (6)| 00:00:03 | | | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST LOCAL| :TQ10000 | 995K| 4860K| 224 (6)| 00:00:03 | | | Q1,00 | P->P | BCST LOCAL | | 9 | PX BLOCK ITERATOR | | 995K| 4860K| 224 (6)| 00:00:03 | 1 | 4 | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL | FACT_A | 995K| 4860K| 224 (6)| 00:00:03 | 1 | 4 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."PART_COL1"="B"."PART_COL1")
The execution plan begin to change worse.
3. paralllelism is 4 ( = Number of Partitions)
SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes FROM v$pq_tqstat ORDER BY dfo_number, tq_id, server_type DESC, process; DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES ---------- ---------- --------------- ---------- ---------- ---------- 1 0 Producer P000 50099 798746 1 0 Producer P001 49900 795506 1 0 Consumer QC 99999 1594252 Execution Plan ---------------------------------------------------------- Plan hash value: 196040303 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 976K| 128 (10)| 00:00:02 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 99999 | 976K| 128 (10)| 00:00:02 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION HASH ALL| | 99999 | 976K| 128 (10)| 00:00:02 | 1 | 4 | Q1,00 | PCWC | | |* 4 | HASH JOIN | | 99999 | 976K| 128 (10)| 00:00:02 | | | Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | FACT_B | 99999 | 488K| 12 (9)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | | | 6 | TABLE ACCESS FULL | FACT_A | 995K| 4860K| 112 (7)| 00:00:02 | 1 | 4 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."PART_COL1"="B"."PART_COL1")
The output is the same as the default degree. which is what we expected.
4. paralllelism is 8 ( > Number of Partitions)
SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes FROM v$pq_tqstat ORDER BY dfo_number, tq_id, server_type DESC, process; DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES ---------- ---------- --------------- ---------- ---------- ---------- 1 0 Producer P002 50164 399870 1 0 Producer P003 49835 397316 1 0 Consumer P000 50164 399870 1 0 Consumer P001 49835 397316 1 1 Producer P000 50164 799700 1 1 Producer P001 49835 794572 1 1 Consumer QC 99999 1594272 Execution Plan ---------------------------------------------------------- Plan hash value: 378788929 ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 976K| 64 (10)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 99999 | 976K| 64 (10)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 99999 | 976K| 64 (10)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 99999 | 488K| 6 (17)| 00:00:01 | | | Q1,01 | PCWP | | | 5 | PX SEND BROADCAST LOCAL| :TQ10000 | 99999 | 488K| 6 (17)| 00:00:01 | | | Q1,00 | P->P | BCST LOCAL | | 6 | PX BLOCK ITERATOR | | 99999 | 488K| 6 (17)| 00:00:01 | 1 | 4 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL | FACT_B | 99999 | 488K| 6 (17)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | | | 8 | PX BLOCK ITERATOR | | 995K| 4860K| 56 (6)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | | | 9 | TABLE ACCESS FULL | FACT_A | 995K| 4860K| 56 (6)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."PART_COL1"="B"."PART_COL1")
Also, the execution plan change worse.
I agree with most of your points, but a few need to be discussed further.