The Post Timesheets job is hanging in processing state and never completes on Oracle after upgrade on 15.9.3. This predominantly happens on a large datasets but can occur on smaller datasets as well. The issue affects both supported versions: Oracle 12.2 and Oracle 19
STEPS TO REPRODUCE:
Expected Results: The job to run as expected within reasonable time
Actual Results: The job is stuck for hours and never completes, with multiple statements hanging on PRTIMEENTRY table, such as "update PRTimeEntry set PRRMEXPORTED= :v0 where PRID= :v1"
Release : 15.9.3, 16.0
Component : CLARITY TIME MANAGEMENT
DB: Oracle 12.2 and Oracle 19
The issue is caused by a change to table PRTIMEENTRY in PRID column, it was changed from PRID NUMBER (10,0) to PRID (NUMBER) datatype to extend the field as part of a user story. This causes Oracle to mix up when it deals with long/integer values, and not use the index, which causes a Full Scan of the PRTIMEENTRY table
Fixed in 16.0.1, patches 15.9.3.2 and 16.0.0.1 as DE63208
To correct this, revert the data type with the following steps: