Aparna.Byra

Long run od Post To Wip job

Discussion created by Aparna.Byra on Nov 26, 2013
Latest reply on Dec 2, 2013 by Aparna.Byra

Hello All,

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.

Outcomes