SOA Purge Performance in Fusion Applications
Last updated on SEPTEMBER 09, 2016
Applies to:Oracle Fusion Application Toolkit - Version 18.104.22.168.1 and later
Information in this document applies to any platform.
Checked for relevance on 16-May-2014
The customer is trying to purge old SOA instances. CRM has 220K instances and HCM has 300K instances.
Fusion B18 is used to connect to Oracle 22.214.171.124 database.
Users purged one day's data at a time. The purge executed quickly through the end of February's data.
For March's data onwards the purge is slow and never actually completes. The DBA eventually had to kill the process.
The customer has followed the steps in the manual Oracle Fusion Middleware Administrator’s Guide for Oracle SOA Suite and Oracle Business Process Management Suite 11g Release 1 (126.96.36.199.0). This is similar to Doc ID How To Purge Oracle SOA Suite 11g Composites Instances Using Purge Scripts (Doc ID 1332337.1)
No error message is produced.
The process gets stuck running the following INSERT "INSERT INTO TEMP_DOCUMENT_DLV_MSG_REF SELECT M.MESSAGE_GUID, DDMR.DOCUMENT_ID,T.CIKEY,T.ECID FROM DOCUMENT_DLV_MSG_REF DDMR, DLV_MESSAGE M , TEMP_CUBE_INSTANCE T WHERE DDMR.MESSAGE_GUID = M.MESSAGE_GUID AND M.STATE IN (2,3) AND ( M.CIKEY = T.CIKEY OR M.CIKEY = 0 )"
Customer has run this several times in single and parallel mode but it always halts at above point. They have waited up to to 5 hrs and the process is still running.
The following is the SQL been run.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms