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:Oracle E-Business Suite Integrated SOA Gateway - Version 12.1.3 and later
Oracle Application Object Library - Version 12.1.3 and later
Information in this document applies to any platform.
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:
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.
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