| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | Select /*+ no_merge parallel(2) pq_distribute(t2 hash hash) */ * from t1, t2 But if we change the query as below then explain plan will be: Degree of Parallelism is 2 because of hintĪs you see in the line of 3 the hash join is considered as BUFFERED. Predicate Information (identified by operation id): , case when mod(level,2)=0 then TRUNC(DBMS_RANDOM.value(1,10))Įlse TRUNC(DBMS_RANDOM.value(11,13)) end tp For simplicity we are going to use simple HASH and BROADCAST distribution only. ![]() Let`s see the following SQLs and explain plans. Regardless of that fact in some cases execution plan contains BUFFERED variant of HASH JOIN but in other cases does not. So, it is a general restriction of producer/consumer model but not of Oracle database`s. 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. I do not think it is the complete answer. ![]() At most a single PX SEND operation can be active concurrently was mentioned as a reason of it. ![]() But why is it required in the execution plan?. 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).
0 Comments
Leave a Reply. |