Delete Investments and Time Reporting Periods job hung while deleting large investments and using large undo table space and temp usage
Steps to Reproduce:
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
Release : 15.9.3, 16.x
DE67496
The DE67496 is fixed in upcoming release targeted during February 2023.