Parallel Query Distribution Combinations and Hints
The PQ_DISTRIBUTE hint instructs the optimizer how to distribute rows of joined tables among producer and consumer query servers. Such distribution can improve the performance of parallel join operations.
tablespec is the name or alias of a table to be used as the inner table of a join.
outer_distribution is the distribution for the outer table.
inner_distribution is the distribution for the inner table.
The values of the distributions are HASH, BROADCAST, PARTITION, and NONE. Only six combinations table distributions are valid, as described in the table below:
| Distribution | Description |
| HASH, HASH |
The rows of each table are mapped to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This distribution is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join. |
| BROADCAST, NONE |
All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This distribution is recommended when the outer table is very small compared with the inner table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is greater than the outer table size. |
| NONE, BROADCAST |
All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This distribution is recommended when the inner table is very small compared with the outer table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is less than the outer table size. |
| PARTITION, NONE |
The rows of the outer table are mapped using the partitioning of the inner table. The inner table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. |
| NONE, PARTITION |
The rows of the inner table are mapped using the partitioning of the outer table. The outer table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. |
| NONE, NONE |
Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equipartitioned on the join keys. |
For example, given two tables r and s that are joined using a hash join, the following query contains a hint to use hash distribution:
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
FROM r,s
WHERE r.c=s.c;
To broadcast the outer table r, the query is:
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
FROM r,s
WHERE r.c=s.c;
This is just reference from Oracle 10g online document.
My examples to demonstrate these concepts
system@ORCL> create table products as select rownum prod_id from all_objects; Table created. system@ORCL> create table costs as select mod(rownum,2)+1 prod_id, rownum unit_price from all_tables; Table created. system@ORCL> set autot trace expl; system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id; Execution Plan ---------------------------------------------------------- Plan hash value: 1177066807 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("C"."PROD_ID"="P"."PROD_ID") Note ----- - dynamic sampling used for this statement system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c broadcast, none)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id; Execution Plan ---------------------------------------------------------- Plan hash value: 1844745828 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWP | | | 5 | PX SEND BROADCAST | :TQ10000 | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | P->P | BROADCAST | | 6 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWP | | | 8 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 9 | TABLE ACCESS FULL | COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("C"."PROD_ID"="P"."PROD_ID") Note ----- - dynamic sampling used for this statement system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c none broadcast)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id; Execution Plan ---------------------------------------------------------- Plan hash value: 2231521328 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | PCWP | | | 4 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWC | | | 5 | TABLE ACCESS FULL | PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWP | | | 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | | | 7 | PX RECEIVE | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | | 8 | PX SEND BROADCAST | :TQ10000 | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST | | 9 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("C"."PROD_ID"="P"."PROD_ID") Note ----- - dynamic sampling used for this statement
[...] 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. [...]