Recently I found at one customer site a strange bug on version 12.2. A parallel query just sits there and never finishes. When you further investigate the issue it becomes obvious that a sort of deadlock arises beween the query coordinator and one of the query slaves.
Both wait on table queue communication. The query coordinator will wait for PX Deq: Execute Reply and the blocking slave process is waiting for PX Deq: Table Q Normal. The rest of the slaves are waiting on PX Deq: Execution Msg.
For this to happen the query must include an analytic function.
The whole issue has a lot to do with the way Oracle parallelized the calculation of analytic functions. In particular it is about how the window sort is parallelized.
For a in depth explaination I refer to this blog post of Phythian’s Christo Kutrovsky: Oracle parallel query hints reference – part 5: PQ_DISTRIBUTE_WINDOW
For our purpose it is good enough to undstand that there are 3 methods of distributing the work to parallel slaves when calculating analytic functions in parallel. Method 3 is the traditionel method, method 1 and 2 are new in version 12. The bug can occur, if method 2 gets used.
Thankfully Andreas Schlögl analyzed the issue and found a workaround by applying the new PQ_DISTRIBUTE_WINDOW
hint. You find below his testcase, which should be self explaining.
rem ################################## rem # Objects # rem ################################## alter session set optimizer_adaptive_plans = false; alter system flush shared_pool; drop table asc_dmy1; drop table asc_dmy3; create table asc_dmy1 parallel 8 as select 'AAA' f001 from xmltable('1 to 300'); --note: this table has no parallel degree create table asc_dmy3 as select 'AAA' f001, 1 acc206 from dual; rem ############################################# rem # SORT then distribute by HASH (Bug) # rem ############################################# /* leads to a HASH JOIN in Line 7, which imo must be a HASH JOIN BUFFERED (due to 2 active PX SENDs at 9 and 13) This SQL hangs and never finishes https://oracle-randolf.blogspot.com/2012/12/hash-join-buffered.html "At most one data distribution can be active at the same time" "Since it doesn't seem to be supported to have two PX SEND operations active at the same time, some artificial blocking operation needs to be introduced, in this case the HASH JOIN BUFFERED, that first consumes the second row source completely before starting the actual probe phase" */ select /*+ pq_distribute_window(@"SEL$1" 2) */ max(v.acc206) over (partition by v.f001) max_bew from asc_dmy3 v, asc_dmy1 e where e.f001 = v.f001 and v.f001 = e.f001; /* ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 419 | 6 (17)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | P->S | QC (RAND) | | 3 | WINDOW CONSOLIDATOR BUFFER| | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | PCWP | | | 4 | PX RECEIVE | | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | PCWP | | | 5 | PX SEND HASH | :TQ10002 | 1 | 419 | 6 (17)| 00:00:01 | Q1,02 | P->P | HASH | | 6 | WINDOW SORT | | 1 | 419 | 6 (17)| 00:00:01 | Q1,02 | PCWP | | |* 7 | HASH JOIN | | 1 | 419 | 5 (0)| 00:00:01 | Q1,02 | PCWP | | | 8 | PX RECEIVE | | 1 | 415 | 3 (0)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10000 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH | | 10 | PX SELECTOR | | | | | | Q1,00 | SCWC | | | 11 | TABLE ACCESS FULL | ASC_DMY3 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | SCWP | | | 12 | PX RECEIVE | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 13 | PX SEND HASH | :TQ10001 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH | | 14 | PX BLOCK ITERATOR | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 15 | TABLE ACCESS FULL | ASC_DMY1 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------------- */ rem ############################################# rem # distribute by HASH then SORT (Success) # rem ############################################# /* leads to a HASH JOIN *BUFFERED* in Line 6, which is inevitably necessary imo This SQL finishes immediately */ select /*+ pq_distribute_window(@"SEL$1" 1) */ max(v.acc206) over (partition by v.f001) max_bew from asc_dmy3 v, asc_dmy1 e where e.f001 = v.f001 and v.f001 = e.f001; /* ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | OMem | 1Mem | O/1/M | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 6 (100)| | | | | | | | | 1 | PX COORDINATOR | | | | | | | | | 73728 | 73728 | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | P->S | QC (RAND) | | | | | 3 | WINDOW SORT | | 1 | 419 | 6 (17)| 00:00:01 | Q1,03 | PCWP | | 20480 | 20480 | 8/0/0| | 4 | PX RECEIVE | | 1 | 419 | 5 (0)| 00:00:01 | Q1,03 | PCWP | | | | | | 5 | PX SEND HASH | :TQ10002 | 1 | 419 | 5 (0)| 00:00:01 | Q1,02 | P->P | HASH | | | | |* 6 | HASH JOIN BUFFERED | | 1 | 419 | 5 (0)| 00:00:01 | Q1,02 | PCWP | | 3400K| 3091K| 8/0/0| | 7 | PX RECEIVE | | 1 | 415 | 3 (0)| 00:00:01 | Q1,02 | PCWP | | | | | | 8 | PX SEND HASH | :TQ10000 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH | | | | | 9 | PX SELECTOR | | | | | | Q1,00 | SCWC | | | | | | 10 | TABLE ACCESS FULL| ASC_DMY3 | 1 | 415 | 3 (0)| 00:00:01 | Q1,00 | SCWP | | | | | | 11 | PX RECEIVE | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | | | | 12 | PX SEND HASH | :TQ10001 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH | | | | | 13 | PX BLOCK ITERATOR | | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | | | |* 14 | TABLE ACCESS FULL| ASC_DMY1 | 300 | 1200 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ */