R12 E-Business Suite Performance Issue - Administrator Is Unable To Purge FND/ SOA Tables Via A Concurrent Requests 'Purge Logs and Closed System Alerts' or 'SOA Purge Audit Data'

Last updated on MAY 14, 2018

Applies to:

Symptoms

E-Business Suite R12 Integrated SOA Gateway, Performance related issues

The administrator is unable to successfully purge some data via concurrent requests 'Purge Logs and Closed System Alerts' or 'SOA Purge Audit Data'.


The SOA Administration tab in E-Business Suite hangs before failing with the following error:

'404 page not found'

After review of the underlying SOA data the below tables contain the bulk of the data:

FND_SOA_REQUEST

FND_SOA_BODY_PIECE

FND_LOG_MESSAGES

The number of rows and size of the tables suggest they are not being purged.


TABLE_NAME                              NUM_ROWS              SIZE-MB
----------------------                ----------                ----------
FND_LOG_MESSAGES                   0                            15.12
FND_SOA_BODY_PIECE                1228113                   54.66
FND_SOA_REQUEST                    901497                     4.74


The issue can be reproduced at will with the following steps:

1. Run concurrent request SOA Purge Audit Data

2. Confirm the request hangs and the trace show the below is statement running in the database:

  SELECT * FROM (SELECT REQUEST_TIMESTAMP AS REQDATETIME, R  EQUEST_COMPLETED AS RESDATETIME, WRQ.IREP_NAME AS WEB_SER  VICE_NAME, FIC.CLASS_ID as CLASS_ID,

  METHOD AS METHOD_NAME, USER_NAME, WRQ.USER_ID, IP_ADDRESS, REQUEST_STATUS as REQUEST_STATUS, RESPONSE_STATUS as RESPONSE_STATUS, MESSAGE_ID, REQUEST_PATTERN,

  (select meaning from fnd_lookup_values_vl where lookup_type = 'FND_SOA_RESPONSE_STATUS' and lookup_code=WRQ.RESPONSE_STATUS) as res_status,

  (select meaning from fnd_lookup_values_vl where lookup_type = 'FND_SOA_REQUEST_STATUS' and lookup_code=WRQ.REQUEST_STATUS) as req_status,

  (select meaning from fnd_lookup_values_vl where lookup_type = 'FND_SOA_MESSAGE_PATTERN' and lookup_code=WRQ.REQUEST_PATTERN) as interaction_architecture,
  (select decode(count(1), 0, 'LOG_ABSENT', 'LOG_PRESENT') from fnd_log_messages where transaction_context_id in (select transaction_context_id from fnd_log_transaction_context where

  transaction_type='SOA_INSTANCE' and transaction_id = WRQ.MESSAGE_ID and regexp_like(WRQ.MESSAGE_ID, '^-?[[:digit:],.]*$'))) as ENABLE_LOG FROM FND_SOA_REQUEST WRQ, FND_USER FU,

  FND_IREP_CLASSES FIC WHERE WRQ.USER_ID = FU.USER_ID(+) AND FIC.IREP_NAME = WRQ.IREP_NAME AND FIC.CLASS_TYPE NOT IN ('SOAPSERVICEDOC', 'WEBSERVICEDOC')) QRSLT

  WHERE (( ( TO_DATE(TO_CHAR(REQDATETIME,'dd-mm-yyyy'),'dd-mm-yyyy')) >=trunc(SYSDATE-7))) ORDER BY REQDATETIME DESC;


The issue has the following business impact:
The administrator is not able to purge SOA data from tables.

Cause