Kürzlich traf ich bei einem Kunden auf einen ungewöhnlichen Bug der Oracle Version 12.2.
Es handelt sich um eine parallele Abfrage, die scheinbar einfach anhält und ihre Arbeit einstellt. 🙁

Bei näherer Betrachtung erkennt man eine interne Verklemmung zwischen den Koorditinatorprozess und einem der Parallelprozesse. Das Ganze ähnelt einem Dead Lock!

Problem Analyse

Beide Prozesse warten auf „table queue“ Kommunikation.

Der Query Coordinator wartet mit „PX Deq: Execute Reply“ und der blockiernde Parallel Process wartet mit „PX Deq: Table Q Normal“.

Der Rest der Parallelprozesse warten mit dem event „PX Deq: Execution Msg“.

Damit es zum besagten Problem kommt, muß auch eine analytic_function beteiligt sein.

Im Kern geht es darum, wie Oracle den Window Sort parallelisiert, der mit einer analytic function zwangsweise verbunden ist.

In früheren Oracle Versionen war dieser Sort of weniger effizient als ein regulärer Sort und daher entsprechend langsamer.

In dem sehr gute Post von Phythian’s Christo Kutrovsky wird das Thema im Detail beschrieben: Oracle parallel query hints reference – part 5: PQ_DISTRIBUTE_WINDOW

Problem Lösung

Für unsere Zwecke genügt es zunächst festzuhalten, dass es drei Methoden gibt, wie ein Window Sort parallelisiert werden kann.

Methode 3 ist die bisher verwendete Methode, Methode 1 und 2 sind neu in Version 12. Wenn Methode 2 verwendet wird, kann es zum oben beschriebenen Bug kommen.

Mein Kollege Andreas Schlögl hat einen Testcase erstellt und gezeigt, dass man mittels des neuen  PQ_DISTRIBUTE_WINDOW Hint den Bug umgehen kann, in dem man auf Methode 1 umstellt.

Den Code des Testcases finden Sie hier. Viel Spass beim ausprobieren!

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 |            |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
*/