SELECT /*+ PARALLEL (ORG,3,3)*/
ORG.X_EFX_SSN,
ORG.X_EFX_BIRTH_DT,
COUNT(*)
FROM
SIEBEL.S_ORG_EXT ORG
GROUP BY
X_EFX_SSN,
X_EFX_BIRTH_DT
HAVING COUNT(*) > 1
The tracefile showed the following callstack:
----- Call Stack Trace -----
kxfprigdb <- kxfpqrgdb <- kxfxgs <- kxfxcw <- qerpxFetch <- opifch2 <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- main <- start
The tracefile also showed the process statewas busy holding a child latch:
===================================================
PROCESS STATE
-------------
Process global information:
process: 7000004748eaa00, call: 700000443d33870, xact: 0, curses: 700000474e203c8, usrses: 700000474e203c8
----------------------------------------
SO: 7000004748eaa00, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=157, calls cur/top: 700000443d33870/700000443d33870, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 249
last post received-location: kxfprienq: QC
last process to post me: 700000474914f40 186 0
last post sent: 0 0 250
last post sent-location: kxfprienq: slave
last process posted by me: 700000474914f40 186 0
(latch info) wait_event=0 bits=10
holding (efd=7) 700000472e4a838 Child process queue reference level=4 child#=99
Location from where latch is held: kxfprigdb: KSLBEGIN: addr qref <---
Context saved from call: 504403177372952360
state=busy, wlstate=free <----
Process Group: DEFAULT, pseudo proc: 700000474a384f0
O/S info: user: oracle, term: UNKNOWN, ospid: 2351144
OSD pid info: Unix process pid: 2351144, image: oraclePPSOLTP1@psoldbap003
最终的解决方法：
Workaround:
Bounce all instances in the RAC cluster.
重启RAC 上的所有instance。 朋友在中午申请了停机时间， 重启之后， RAC 节点正常。