ETL9.2:Deadlocks Occurring During Timesheet Submission (real-time Rules) (Doc ID 2146286.1)

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.

Goal

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.
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms