Delete Investments and Time Reporting Periods job hung while deleting large investments

Products

• Clarity PPM On Premise

Issue/Introduction

Delete Investments and Time Reporting Periods job hung while deleting large investments and using large undo table space and temp usage

Steps to Reproduce: 

1. Login to clarity as administrator
2. Close an investment and mark as inactive
3. The investment (project) should be of moderately large in size like 4k tasks, 100 team members 
4. The same investment board should have several picklists
5. Check that investment (project) and mark for deletion 
6. Run the Delete Investments and Time Reporting Periods job

Expected Results: The investment should be deleted within reasonable amount of time 

Actual Results: During the deleting we saw large undo table space utilization, high amount of archive generation. Also the below query was executing multiple times and took longer to execute 

MERGE /*+ NOPARALLEL */
INTO ODF_ITEM_POSITIONS S
USING ( SELECT CONTEXT_ID, PICKLIST_DEF_ID, OBJECT_CODE, INSTANCE_ID, LOOKUP_ID, ROW_NUMBER() OVER (PARTITION BY PICKLIST_DEF_ID, LOOKUP_ID ORDER BY POSITION) AS ROW_NUM
FROM ODF_ITEM_POSITIONS
WHERE 1 = 1   AND CONTEXT_ID = :1   AND PICKLIST_DEF_ID = :2   AND LOOKUP_ID = :3  ) C
ON ( S.CONTEXT_ID = C.CONTEXT_ID AND S.PICKLIST_DEF_ID = C.PICKLIST_DEF_ID AND S.OBJECT_CODE = C.OBJECT_CODE AND S.INSTANCE_ID = C.INSTANCE_ID AND S.LOOKUP_ID = C.LOOKUP_ID)
WHEN MATCHED THEN
UPDATE SET S.POSITION = C.ROW_NUM, S.LAST_UPDATED_DATE = :4 , S.LAST_UPDATED_BY = :5 
WHERE S.POSITION <> C.ROW_NUM

Environment

Release : 15.9.3, 16.x 

Cause

DE67496

Resolution

The DE67496 is fixed in upcoming release targeted during February 2023.