We are on Clarity V13.1 Recently we started observing that Post To Wip job is taking lot of time on couple day & less times on couple days.
From the AWR Reports found that one of the Delete query to take 90% of the total time. We tried checking for SQL recommendations but nothing was found. Also the # of records deleting from the table is also very less just close to 1000.
DELETE PPA_TRANSCONTROLAPINFO WHERE ROWID IN (SELECT A.ROWID FROM PPA_TRANSCONTROLAPINFO A WHERE A.ID IN ( SELECT DISTINCT W.PPA_WIPAPINFO_ID FROM PPA_WIP W, PPA_WIPPOSTWORK WP WHERE WP.PROCESSID = :B1 AND W.TRANSNO = WP.WIPTRANSNO ) AND A.ID NOT IN (SELECT PPA_TRANSCONTROLAPINFO_ID FROM PPA_TRANSCONTROL) )
Please suggest if there is any workaround/fix for this issue.