Today we had session locking issues reported in EBS PROD via OEM incident manager.
Total db time 1,039 seconds is consumed by 1 sessions blocked by session SID,SERIAL:916,57869
SID SERIAL# SQL_ID MODULE EVENT
Total db time 1,039 seconds is consumed by 1 sessions blocked by session SID,SERIAL:916,57869
After monitoring , we identified that SID 916 was holding the SID 1544.
INST_ID SESS
---------- ----------------
1 Holder: 916
1 Waiter: 1544
The session from SID 916 was workflow notification session and it was holding another workflow notification session.
------- ---------- ------------ ---------------------------------------- ----------------
916 57869 976jzgdb06abj e:ICX:bes:oracle.apps.wf.notification.se latch: shared pool
1544 23035 3c4awu4jgqy2u e:ICX:bes:oracle.apps.wf.notification.se enq: TX - row lock contention
Found the sql that was associated with sql_id : 976jzgdb06abj and then found out the bind variable values for that sql statement. Actually the sessions were going in a infinite loop and it had done 786703676 executions since 08/04/2017 Fri 12:09:05.
I took development team's help to find out if there are some workflow approval requisitions that were stuck based on below sql statement. Then we found an issue with HR record table where our Company CEO ( XX ) has a supervisor as YY and strangely YY has supervisor as XX in system. So the WF session was unable to find out who was the supervisor to assign the requisition and it was going in continuous loop.
Development team removed the super visor details of CEO ( XX) in EBS system and the loop was closed. The session were cleared in PROD.
SQL statement:
SELECT SUPERVISOR_ID FROM PER_ALL_ASSIGNMENTS_F WHERE PER_ALL_ASSIGNMENTS_F.PERSON_
