Chinar Aliyev`s blog

February 27, 2019

The Hash Join Buffered Operation

Filed under: CBO,Join,Parallel Execution — Chinar Aliyev @ 10:16 am

Several years ago Randolf Geist wrote an article about the HASH JOIN BUFFERED operation and precisely demonstrated that only the second row source is buffered (and only proper part of it). But why is it required in the execution plan?. At most a single PX SEND operation can be active concurrently was mentioned as a reason of it. I do not think it is the complete answer. That is right, only single PX SEND operation can be active concurrently, it means(depending on plan shape) only pair of DFO can be active at the time for per DFO Tree. So, it is a general restriction of producer/consumer model but not of Oracle database`s. Regardless of that fact in some cases execution plan contains BUFFERED variant of HASH JOIN but in other cases does not. Let`s see the following SQLs and explain plans. For simplicity we are going to use simple HASH and BROADCAST distribution only.

DB version : 11.2.0.1
create table t1 
as
select
        level as id
      , case when mod(level,2)=0 then TRUNC(DBMS_RANDOM.value(1,10)) 
        else TRUNC(DBMS_RANDOM.value(11,13)) end tp
      , rpad('x', level) as filter
from
        dual
connect by
        level <= 1000
;

create table t2 
as
select
        level as id
       ,trunc(sysdate) dat
       ,round(level/10,2) amount
       ,rpad('x', level) as filter
from
        dual
connect by
        level <= 1500
;

insert into t2 
select id,trunc(sysdate-1) dat,round(id/5,2) amount,filter   from t2;

commit;

execute dbms_stats.gather_table_stats(null,'t1');
execute dbms_stats.gather_table_stats(null,'t2');

explain plan for
select /*+ parallel(2) pq_distribute(t2 hash hash) */ * from t1 , t2
where t1.id=t2.id;

select * from table(dbms_xplan.display());
Plan hash value: 3683239666                                                                                      
                                                                                                                 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  2000 |  2494K|    67   (2)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002 |  2000 |  2494K|    67   (2)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          |  2000 |  2494K|    67   (2)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |          |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T1       |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |     PX RECEIVE          |          |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001 |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |          |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL| T2       |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------
                                                                                                                                                                                                                                  
Predicate Information (identified by operation id):                                                              
---------------------------------------------------                                                              
                                                                                                                 
   3 - access("T1"."ID"="T2"."ID")                                                                               
                                                                                                                 
Note                                                                                                             
-----                                                                                                            
   - Degree of Parallelism is 2 because of hint 
(EP1)          

As you see in the line of 3 the hash join is considered as BUFFERED. But if we change the query as below then explain plan will be:

explain plan for
select count(*) from (
select /*+ no_merge parallel(2) pq_distribute(t2 hash hash) */ * from t1 , t2
where t1.id=t2.id);

Plan hash value: 1086837495                                                                                         
                                                                                                                    
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |       |    67   (2)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE            |          |     1 |       |            |          |        |      |            |
|   2 |   PX COORDINATOR           |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10002 |     1 |       |            |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE         |          |     1 |       |            |          |  Q1,02 | PCWP |            |
|   5 |      VIEW                  |          |  2000 |       |    67   (2)| 00:00:01 |  Q1,02 | PCWP |            |
|*  6 |       HASH JOIN            |          |  2000 | 16000 |    67   (2)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE          |          |  1000 |  4000 |    12   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10000 |  1000 |  4000 |    12   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |          |  1000 |  4000 |    12   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL| T1       |  1000 |  4000 |    12   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE          |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10001 |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL| T2       |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
                                                                                                                    
Predicate Information (identified by operation id):                                                                 
---------------------------------------------------                                                                 
                                                                                                                    
   6 - access("T1"."ID"="T2"."ID")                                                                                  
                                                                                                                    
Note                                                                                                                
-----                                                                                                               
   - Degree of Parallelism is 2 because of hint                                                                     
(EP2)   

In this case HASH JOIN is not buffered. Also, the behavior of the HASH JOIN is not related to the HASH distribution only. If we use BROADCAST distribution for the Second Row Source then the buffering will be required:
11g Plan shape:

explain plan for
select /*+  parallel(2) pq_distribute(t2 none broadcast) */ * from t1 , t2
where t1.id=t2.id;

Plan hash value: 869483324                                                                                        
                                                                                                                  
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |  2000 |  2494K|    67   (2)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  2000 |  2494K|    67   (2)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN             |          |  2000 |  2494K|    67   (2)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX BLOCK ITERATOR    |          |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   5 |      TABLE ACCESS FULL   | T1       |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |     BUFFER SORT          |          |       |       |            |          |  Q1,01 | PCWC |            |
|   7 |      PX RECEIVE          |          |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |       PX SEND BROADCAST  | :TQ10000 |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   9 |        PX BLOCK ITERATOR |          |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  10 |         TABLE ACCESS FULL| T2       |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------
                                                                                                                  
Predicate Information (identified by operation id):                                                               
---------------------------------------------------                                                               
                                                                                                                  
   3 - access("T1"."ID"="T2"."ID")                                                                                
                                                                                                                  
Note                                                                                                              
-----                                                                                                             
   - Degree of Parallelism is 2 because of hint
(EP 3)                                                                   

But the explain plan form 12c (18c) displays exact HASH JOIN BUFFERED operation rather than separate BUFFER SORT and HASH JOIN operation in the plan shape.

EXPLAIN PLAN
    FOR
        SELECT /*+ no_pq_replicate(@"SEL$1" "T2"@"SEL$1") parallel(2) pq_distribute(t2 none broadcast) */
              *
          FROM   t1, t2
         WHERE   t1.id = t2.id;
(SQL4)
Plan hash value: 1233681586                                                                                              
                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |   197K|  1488M|       | 96556   (1)| 00:00:04 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |   197K|  1488M|       | 96556   (1)| 00:00:04 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |          |   197K|  1488M|   187M| 96556   (1)| 00:00:04 |  Q1,01 | PCWP |            |
|   4 |     PX BLOCK ITERATOR   |          |   100K|   374M|       | 14663   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   5 |      TABLE ACCESS FULL  | T1       |   100K|   374M|       | 14663   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |     PX RECEIVE          |          |   300K|  1134M|       | 44374   (1)| 00:00:02 |  Q1,01 | PCWP |            |
|   7 |      PX SEND BROADCAST  | :TQ10000 |   300K|  1134M|       | 44374   (1)| 00:00:02 |  Q1,00 | P->P | BROADCAST  |
|   8 |       PX BLOCK ITERATOR |          |   300K|  1134M|       | 44374   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|   9 |        TABLE ACCESS FULL| T2       |   300K|  1134M|       | 44374   (1)| 00:00:02 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------

(EP4)

It is fact that in both plans (EP3, EP4) the Second Row Source must be redistributed, and it forced to perform HASH JOIN in BUFFERED mode. Now, let`s see another query and its plan :

explain plan for
select /*+  parallel(2) pq_distribute(t2 hash hash) */t1.tp,count(*) from t1 , t2
where t1.id=t2.id
group by t1.tp;
(SQL 5)

Plan hash value: 2482649283                                                                                          
                                                                                                                     
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    11 |   121 |    68   (3)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR             |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)       | :TQ10003 |    11 |   121 |    68   (3)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY            |          |    11 |   121 |    68   (3)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE              |          |    11 |   121 |    68   (3)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH           | :TQ10002 |    11 |   121 |    68   (3)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY         |          |    11 |   121 |    68   (3)| 00:00:01 |  Q1,02 | PCWP |            |
|*  7 |        HASH JOIN            |          |  2000 | 22000 |    67   (2)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE          |          |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND HASH       | :TQ10000 |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  10 |           PX BLOCK ITERATOR |          |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |            TABLE ACCESS FULL| T1       |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  12 |         PX RECEIVE          |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  13 |          PX SEND HASH       | :TQ10001 |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  14 |           PX BLOCK ITERATOR |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  15 |            TABLE ACCESS FULL| T2       |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
(EP5)

But if we add the following hint to the same SQL then explain plan will contain again HASH JOIN BUFFERED operation:

explain plan for
select /*+ parallel(2) no_gby_pushdown(@"SEL$1")  pq_distribute(t2 hash hash) */t1.tp,count(*) from t1 , t2
where t1.id=t2.id
group by t1.tp;

Plan hash value: 548441744                                                                                          
                                                                                                                    
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |    11 |   121 |    68   (3)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10003 |    11 |   121 |    68   (3)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY           |          |    11 |   121 |    68   (3)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE             |          |  2000 | 22000 |    67   (2)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH          | :TQ10002 |  2000 | 22000 |    67   (2)| 00:00:01 |  Q1,02 | P->P | HASH       |
|*  6 |       HASH JOIN BUFFERED   |          |  2000 | 22000 |    67   (2)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE          |          |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10000 |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |          |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL| T1       |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE          |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10001 |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL| T2       |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
(EP6)

So, the SQL is same, same PX slaves and same PX distributions, we just added the no_gby_pushdown hint to prevent group by push down optimization and demonstrate that when the BUFFERED variant of HASH JOIN is required.
All above examples clearly indicate that the appearing of HASH JOIN BUFFERED operation in execution plan is depending on parent operation of parallel HASH JOIN. It means, if the result of parallel HASH JOIN is required to be produced directly in this case BUFFERED is needed, otherwise if the result of PARALLEL HASH JOIN will be processed by other parent operation before producing (redistribute) in this case no BUFFERED is needed. Question is that what is the reason? Before answer the question let`s change the (SQL4) and force optimizer to use BROADCAST distribution for the second row source.

explain plan for
select /*+  parallel(2) pq_distribute(t2 none broadcast) */ t1.tp,count(*) from t1 , t2
where t1.id=t2.id
group by t1.tp;

Plan hash value: 1458162008                                                                                          
                                                                                                                     
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    11 |   121 |    68   (3)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR             |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)       | :TQ10002 |    11 |   121 |    68   (3)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY            |          |    11 |   121 |    68   (3)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE              |          |    11 |   121 |    68   (3)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH           | :TQ10001 |    11 |   121 |    68   (3)| 00:00:01 |  Q1,01 | P->P | HASH       |
|   6 |       HASH GROUP BY         |          |    11 |   121 |    68   (3)| 00:00:01 |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN            |          |  2000 | 22000 |    67   (2)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX BLOCK ITERATOR   |          |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   9 |          TABLE ACCESS FULL  | T1       |  1000 |  7000 |    12   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  10 |         PX RECEIVE          |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |          PX SEND BROADCAST  | :TQ10000 |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|  12 |           PX BLOCK ITERATOR |          |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  13 |            TABLE ACCESS FULL| T2       |  3000 | 12000 |    54   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

As it is seen, even the second row source is distributed via BROADCAST the buffered variant is not required. Because, the result of the parallel hash join is not directly sent to the parent operation. There is HASH GROUP BY and after that the rows are distributed via HASH distribution.
If you look at the EP1, there are two PX Server set (PX SS). One is producer set (PX SS2) and another one is consumer set (PX SS1). The Parallel HASH JOIN operation in the line 3 is performed by PX SS1 so by consumer slaves. At the same time the parent operation (in the line 2 – PX SEND QC (RANDOM)) is required to produce the result of the HASH JOIN. So, this is consumer salve set and the same time it can not produce rows (it is consumer slave set, consumer salves can not produce rows unless become again producers), in other words they can not play as “PRODUCERS” at the same time. That is why, firstly, the second row source is consumed and buffered then the consumer slaves can become producers in order to produce rows. But, the line 4 in explain plan EP2 allows consumers to change their status and become producers. So, it means, PX SS1 will complete its work as consumer in the line 6 in EP2 and then becomes producer starting from the line 4. So, additional operation after parallel hash join allows consumers to become producers and that is why buffered is not required.
Finally let` see the below simple plan:

explain plan for
select /*+  parallel(2) pq_distribute(t2 broadcast none) */ * from t1 , t2
where t1.id=t2.id;
Plan hash value: 3350059367                                                                                      
                                                                                                                 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  2000 |  2494K|    67   (2)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  2000 |  2494K|    67   (2)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |          |  2000 |  2494K|    67   (2)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND BROADCAST  | :TQ10000 |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR |          |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T1       |  1000 |   497K|    12   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |     PX BLOCK ITERATOR   |          |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL  | T2       |  3000 |  2250K|    54   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

As you see, in this case the PX SS1 will perform its work as consumer at once (in the line 4) when creating in memory hash cluster (build table) and becomes producer slave set (does not need to consume the second row source) then performs parallel hash join and can produce the result.

Summary

We saw that HASH JOIN BUFFERED is needed when result of parallel hash join is required to be produced directly and the second row source is placed in different DFO than HASH JOIN itself. It depends on the parent operation. If the result of HASH JOIN is followed by other operator (like aggregating, grouping, joining) then this operation gives a chance to the consumer slave set to become producer slave set. The mentioned notes are also true for Oracle database 12c afterward.

5 Comments »

  1. […] about writing the blog note until a couple of days ago when Chinar Aliyev produced a note on the hash join buffered operation in which he had included an execution plan similar to the broadcast plan from my earlier […]

    Pingback by Hash Optimisation- | Oracle Scratchpad — March 1, 2019 @ 4:40 pm | Reply

  2. […] performance of PSHJ. First of all, in this case parallel hash join operation will not suffer its BUFFERED variant. Secondly, there will not be skew problem (my initial thought) even there several tables […]

    Pingback by Parallel Shared Hash Join | Chinar Aliyev`s blog — April 29, 2019 @ 5:36 am | Reply

  3. Hi Chinar,
    From your blog post: If you look at the EP1, there are two PX Server set (PX SS). One is producer set (PX SS2) and another one is consumer set (PX SS1).
    My question is: How can you determine which one is producer and which one is consumer?

    Comment by rashidshahveledov — August 21, 2019 @ 9:13 am | Reply

  4. Hi Rashid,

    The name of slave set is not important (I just labeled them as PX SS1/PX SS2). You also can refer Real Time SQL monitor report and V$PQ_TQSTAT to get more information
    about the salve sets (names, types, send/rec rows etc.). In fact, firstly producer salves start to work and send their rows to another slaves which is called consumer slave set.
    But consumer salve set can become producer slave set again as this blog post explains.

    Best Regards.

    Comment by Chinar Aliyev — August 25, 2019 @ 8:56 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: