Post Timesheets slow after upgrade to 15.9.3 (Oracle only) - PRTIMEENTRY.PRID problem

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

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:

1. Upgrade a large dataset with 9M of time entries in PRTIMEENTRY and 2.3M timesheets to 15.9.3
2. Run Post Timesheets job with many hundreds of Approved timesheets  (i.e. >1000)

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"

Environment

Release : 15.9.3, 16.0

Component : CLARITY TIME MANAGEMENT

DB: Oracle 12.2 and Oracle 19

Cause

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

Resolution

Fixed in 16.0.1, patches 15.9.3.2 and 16.0.0.1 as DE63208

Workaround: The below steps should be applied only when customers haven't applied the patch. Applying the patch is the recommended action

To correct this, revert the data type with the following steps:

1. Stop and cancel all processing jobs
2. Pause all jobs and stop the services
3. Ensure all sessions are removed from the database
4. Now backup the PRTIMEENTRY table (Attention! Ensure proper backup or your data might be lost!)
5. Truncate PRTIMEENTRY table (This will remove all the timeentries data)
6. Modify the PRID column length with the following statement:
   alter table prtimeentry modify PRID NUMBER(10,0)
   Ensure it's applied correctly
7. Now insert the data from the PRTIMEENTRY backup back into PRTIMEENTRY
8. Check the data is the same amount and matches what was there previously
9.  Run the query to avoid recalculating of all Time entry timeslices (will be triggered on insert by trigger PRJ_PRTIMEENTRY_T1):
   update PRTIMEENTRY
   set slice_status =null
   commit
10. Bring the services up
11. Run the Time Slicing job. Note it may take longer than usual on Time entries, this is why we apply step 9. Allow some time for it to complete
12. Now run Post Timesheets job to completion
13. Verify the Invalid transactions to ensure no duplicates and everything looks good before running Post Transactions to Financials and Post to WIP