Bug 27223075 - Wait for 'PX Deq: Join Ack' when no active QC but PPA* slaves sho

今天遇到了一個問題，應用並行SQL報錯ORA-12805 Parallel Query Server Died Unexpectedly Error

等待事件為PX Deq: Join Ack

Bug 27223075  Wait for 'PX Deq: Join Ack' when no active QC but PPA* slaves show as busy

 This note gives a brief overview of bug 27223075.
 The content was last updated on: 10-APR-2020
  Click  here for details of each of the sections below.

Affects:

Product ( Component)	Oracle Server (Rdbms)
Range of versions  believed to be affected	Versions BELOW 19.1
Versions  confirmed as being affected	12.1.0.2 (Server Patch Set) 11.2.0.4
Platforms affected	Generic (all / most platforms affected)

Fixed:

The fix for 27223075 is first included in

19.1.0 Database Release Update (DB RU) 18.5.0.0.190115 (Jan 2019) 12.2.0.1.190115 (Jan 2019) Database Jan 2019 Release Update (DB RU) 12.1.0.2.190115 (Jan 2019) Database Patch Set Update (DB PSU) 12.1.0.2.190115 (Jan 2019) Database Proactive Bundle Patch 12.2.0.1.181016 (Oct 2018) Bundle Patch for Windows Platforms 12.1.0.2.181130 (Nov 2018) Bundle Patch for Windows Platforms

Interim patches may be available for earlier versions - click   to check.

Symptoms:	Related To:
Hang (Process Hang) Performance Of Query/ies Affected Unstable Execution Plan ORA-12805 /  ORA-12850 Waits for "PX DEQ: JOIN ACK"	Parallel Query (PQO)

Description

This bug is only relevant when using Real Application Clusters (RAC) and Parallel Query (PQO)

Hang issue during slave join period and have to kill slave process to end the
hang. 
 
Rediscovery:
 A PPA* slave that is busy is thought to be available by a QC and
 is asked to join, but the slave cannot report back to the QC, so
 it hangs and has to be killed.
 
 You may run the following queries and see no rows returned from 
 the first one, and PPA* slaves returned from the second one:
 
1. No Query slaves running.
 select
   decode(px.qcinst_id,NULL,username,
         ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
   decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
   to_char( px.server_set) "Slave Set",
   to_char(s.sid) "SID",
   decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
   px.req_degree "Requested DOP",
   px.degree "Actual DOP"
 from
   v$px_session px,
   v$session s
 where
   px.sid=s.sid (+)
  and
   px.serial#=s.serial#
  order by 5 , 1 desc
 /
 
 no rows selected
 ...
 2. But PPA4/PPA5/PPA6/PPA7 are busy.
 SQL> select SLAVE_NAME from V$PQ_SLAVE where STATUS='BUSY';
 
 SLAV
 ----
 PPA4
 PPA5
 PPA6
 PPA7
 
Additional Info:
Any parallel slave process (p00*) can be affected by this bug and is not constrained to PPA* slaves. 
Workaround
 None.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article