If you happen to be using Oracle E-Business Suite Integrated SOA Gateway (ISG) you may at some point find yourself retaining way too much data. Did you know that there is a seeded purge program available that will delete data from the underlying tables? This is helpful to maintain the data at a reasonable size to help performance and minimize the storage footprint.
Observations
My SOA purging journey began due to a performance related issue. This occurred when I navigated to the Integrated SOA Gateway responsibility > SOA Monitor, it was taking upwards of 5 minutes to pull up that web page. When I checked the database I see that it’s sitting on this query:
SELECT *
FROM (SELECT REQUEST_TIMESTAMP AS REQDATETIME,
REQUEST_COMPLETED AS RESDATETIME,
WRQ.IREP_NAME AS WEB_SERVICE_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)))
I started investigating the underlying SOA related tables and found that two tables contain the bulk of the data. These are FND_SOA_REQUEST and FND_SOA_BODY_PIECE. The number of rows and size of the tables are quite large!
SQL> SELECT table_name,
num_rows,
ROUND ( (blocks * 8192) / 1024 / 1024, 2) "SIZE-MB"
FROM dba_tables
WHERE table_name IN ('FND_SOA_BODY_PIECE', 'FND_SOA_REQUEST')
ORDER BY table_name ASC;
TABLE_NAME NUM_ROWS SIZE-MB
------------------------------ ---------- ----------
FND_SOA_BODY_PIECE 13824413 27236.4
FND_SOA_REQUEST 7086595 2420.02
Begin the Purge!
Oracle EBS has a seeded purge program available that will take care of this for us. There is a section in the implementation guide that outlines the steps to launch this program. It’s found by navigating to the Integrated SOA Gateway responsibility > SOA Monitor.
The implementation guide walks you through the steps on how to initiate the program so I won’t do that here. Behind the scenes all it is doing is launching a concurrent request. In our environment we decided to retain only 90 days’ worth of data so we had about 2+ years of data to purge. For our environment this took approximately 4 hours.
Once the purge completed I ran a script to take care of the following tasks:
- Rebuild the tables and indexes to recoup our storage
- Collect the latest table/index statistics
- Recompile any invalids caused by the rebuild
Here is the script that I used.
ALTER TABLE FND_SOA_REQUEST MOVE; ALTER INDEX APPLSYS.FND_SOA_REQUEST_U1 REBUILD; ALTER INDEX APPLSYS.FND_SOA_REQUEST_N1 REBUILD; ALTER INDEX APPLSYS.FND_SOA_REQUEST_N2 REBUILD; EXEC fnd_stats.gather_table_stats(ownname=>'APPLSYS',tabname=>'FND_SOA_REQUEST', percent=>'100', degree=>'4'); ALTER TABLE fnd_soa_body_piece MOVE; ALTER INDEX APPLSYS.FND_SOA_BODY_PIECE_N1 REBUILD; EXEC fnd_stats.gather_table_stats(ownname=>'APPLSYS',tabname=>'FND_SOA_BODY_PIECE', percent=>'100', degree=>'4'); EXEC sys.utl_recomp.recomp_parallel(8);
After the purge the number of rows and size of the tables were much smaller!
TABLE_NAME NUM_ROWS SIZE-MB ------------------------------ ---------- ---------- FND_SOA_BODY_PIECE 1288908 3008.3 FND_SOA_REQUEST 644456 263.97
Summary
If you are using ISG you should consider adding the SOA Purge program to your regularly scheduled maintenance tasks. By running the purge and rebuilding the objects, we reduced our overall storage footprint. Now we’ve scheduled this to run on a periodic basis to maintain that reduced footprint. Now when we navigate to the SOA Monitor web page it comes up within a few seconds. 🙂
Word of caution. For us purging and only keeping 90 days’ worth of data was not an issue as we had no need for older data. Some organizations may have compliance policies that mandate retaining this data for some reason or another. In that case you could still purge the data but perhaps you may need to offload it to some custom historical tables first.
