ETL9.2:Deadlocks Occurring During Timesheet Submission (real-time Rules)
Last updated on JUNE 03, 2016
Applies to:PeopleSoft Enterprise HCM Time and Labor - Version 9.2 and later
Information in this document applies to any platform.
When multiple users are submitting time we see extremely poor performance. What we can see in Grid Control is a row lock contention when trying to update the PS_WRK_ADHOC_TAO table:
UPDATE PS_WRK_ADHOC_TAO2 SET SCHED_HRS = ( SELECT S.SCHED_HRS FROM PS_WRK_SHIFT_TAO2 S WHERE S.PROCESS_INSTANCE = PS_WRK_ADHOC_TAO2.PROCESS_INSTANCE AND S.EMPLID = PS_WRK_ADHOC_TAO2.EMPLID AND S.EMPL_RCD = PS_WRK_ADHOC_TAO2.EMPL_RCD AND S.DUR = PS_WRK_ADHOC_TAO2.DUR AND S.SHIFTNUM = PS_WRK_ADHOC_TAO2.SEQ_NO) WHERE EXISTS ( SELECT 'X' FROM PS_WRK_SHIFT_TAO2 S1 WHERE S1.PROCESS_INSTANCE = PS_WRK_ADHOC_TAO2.PROCESS_INSTANCE AND S1.EMPLID = PS_WRK_ADHOC_TAO2.EMPLID AND S1.EMPL_RCD = PS_WRK_ADHOC_TAO2.EMPL_RCD AND S1.DUR = PS_WRK_ADHOC_TAO2.DUR AND S1.SHIFTNUM = PS_WRK_ADHOC_TAO2.SEQ_NO) AND PS_WRK_ADHOC_TAO2.PUNCH_TYPE='0' AND PS_WRK_ADHOC_TAO2.SHIFT_ID = ' '
We applied several bug patches and their dependencies including the bug id referenced in Doc ID 1943377.1. Based on this Doc ID the issue should have been resolved. However we are still encountering the same deadlock. See attached trace file 'PULICM_pnserp-snip.UTS.McMaster.CA.tracesql'.
This trace file was generated in our non-prod performance testing environment. In production this issue cause major service loss and time reporters sometimes wait upwards of 5 minutes for time to submit, while others ultimately time out, due to the deadlocks.
I've also attached the listing of dependent bug ids that were applied to our non-prod performance testing environment.
Can you please review asap.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms