Relationship between DOP and Number of Hash Partitions (2)
Another relationship between DOP and number of hash partitions is that when doing partition wise sort, the number of partitions should be twice greater than DOP.
Similarly, firstly we create the table for testing. then insert data, then gather statistics.
SQL> CREATE TABLE fact_a( 2 date_key date, 3 part_col1 NUMBER 4 ) 5 PARTITION BY HASH (part_col1) PARTITIONS 8 6 parallel 7 ; Table created. SQL> SQL> insert into fact_a select null,rownum from dba_objects, dba_objects where rownum<1000000; 999999 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(user,'FACT_A'); PL/SQL procedure successfully completed.
1. parallelism is default
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 472794 2854042 1 0 Producer P003 527205 3188256 1 0 Consumer P000 500212 3022499 1 0 Consumer P001 499787 3019799 1 1 Producer P000 500212 2021748 1 1 Producer P001 499787 2020028 1 1 Consumer QC 999999 4041776 Execution Plan ---------------------------------------------------------- Plan hash value: 3448586717 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1006K| 4913K| | 159 (26)| 00:00:02 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1006K| 4913K| | 159 (26)| 00:00:02 | | | Q1,01 | P->S | QC (RAND) | | 3 | HASH GROUP BY | | 1006K| 4913K| 23M| 159 (26)| 00:00:02 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1006K| 4913K| | 126 (6)| 00:00:02 | | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 1006K| 4913K| | 126 (6)| 00:00:02 | | | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 1006K| 4913K| | 126 (6)| 00:00:02 | 1 | 8 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| FACT_A | 1006K| 4913K| | 126 (6)| 00:00:02 | 1 | 8 | Q1,00 | PCWP | | -----------------------------------------------------------------------------------------------------------------------------------------
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 is the bad execution plan.
2. parallelism is 2
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 499463 2018712 1 0 Producer P001 500536 2023044 1 0 Consumer QC 999999 4041756 Execution Plan ---------------------------------------------------------- Plan hash value: 2505098799 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1006K| 4913K| | 320 (26)| 00:00:04 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1006K| 4913K| | 320 (26)| 00:00:04 | | | Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION HASH ALL| | 1006K| 4913K| | 320 (26)| 00:00:04 | 1 | 8 | Q1,00 | PCWC | | | 4 | HASH GROUP BY | | 1006K| 4913K| 23M| 320 (26)| 00:00:04 | | | Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | FACT_A | 1006K| 4913K| | 251 (6)| 00:00:04 | 1 | 8 | Q1,00 | PCWP | | -----------------------------------------------------------------------------------------------------------------------------------------
This execution plan is what we expected, in which the DOP is less than half of number of hash partitions.
3. parallelism is 4
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 502709 3037531 1 0 Producer P003 497290 3004747 1 0 Consumer P000 500212 3022499 1 0 Consumer P001 499787 3019779 1 1 Producer P000 500212 2021748 1 1 Producer P001 499787 2020028 1 1 Consumer QC 999999 4041776 Execution Plan ---------------------------------------------------------- Plan hash value: 3448586717 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1006K| 4913K| | 159 (26)| 00:00:02 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1006K| 4913K| | 159 (26)| 00:00:02 | | | Q1,01 | P->S | QC (RAND) | | 3 | HASH GROUP BY | | 1006K| 4913K| 23M| 159 (26)| 00:00:02 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1006K| 4913K| | 126 (6)| 00:00:02 | | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 1006K| 4913K| | 126 (6)| 00:00:02 | | | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 1006K| 4913K| | 126 (6)| 00:00:02 | 1 | 8 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| FACT_A | 1006K| 4913K| | 126 (6)| 00:00:02 | 1 | 8 | Q1,00 | PCWP | | -----------------------------------------------------------------------------------------------------------------------------------------
The output should be the same as default parallelism.
4. parallelism is 8
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 519278 3136430 1 0 Producer P003 480721 2905868 1 0 Consumer P000 500212 3022499 1 0 Consumer P001 499787 3019799 1 1 Producer P000 500212 2021748 1 1 Producer P001 499787 2020028 1 1 Consumer QC 999999 4041776 Execution Plan ---------------------------------------------------------- Plan hash value: 3448586717 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1006K| 4913K| | 79 (26)| 00:00:01 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1006K| 4913K| | 79 (26)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) | | 3 | HASH GROUP BY | | 1006K| 4913K| 23M| 79 (26)| 00:00:01 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1006K| 4913K| | 63 (7)| 00:00:01 | | | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 1006K| 4913K| | 63 (7)| 00:00:01 | | | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 1006K| 4913K| | 63 (7)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| FACT_A | 1006K| 4913K| | 63 (7)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | | -----------------------------------------------------------------------------------------------------------------------------------------
Also a bad execution plan.