APPMSGARCH Process - Performance Tuning

I recently worked on a requirement to tune the performance of the delivered APPMSGARCH process (batch approach used to archive service operation data). The process was taking longer to run everyday and got to a point where it would run for over 12 hours.

While investigating the problem in production, it was found that the following SQL was the main cause for our performance issue.

SqlExec is located in APPMSGARCH.MAIN.GBL.default.1900-01-01.ARCHASYN.OnExecute:

DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE (PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  OR PSAPMSGPUBDATA.IBTRANSACTIONID     = C.IBTRANSACTIONID)
  AND B.IBTRANSACTIONID                 = C.IBPUBTRANSACTID
  );

Note: This is not to imply that all performance problems with this process is directly related to this SQL. There could be other issues depending on each individual environment. But I do find that the structure of all the SQLs particularly the DELETEs follow a similar theme (with the usage of EXISTS clause). So it could be a common problem for which the following solution could be applied.

Once it was identified that this SQL was the main issue in our environment, I tried to look in My Oracle Support for potential solutions (the first place I would look to research a problem with anything delivered). I found this document E-IB: APPMSGARCH Performance Issue (Doc ID 754437.1).

Amongst other things in the document, it was recommended to replace the SQL (mentioned above) with two different SQL statements to separate the OR clause.

SQL 1:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.CANONICALTRSFRMID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

SQL 2:
DELETE
FROM PSAPMSGPUBDATA
WHERE EXISTS
  (SELECT '*'
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE PSAPMSGPUBDATA.IBTRANSACTIONID = C.IBTRANSACTIONID
  AND B.IBTRANSACTIONID                = C.IBPUBTRANSACTID
  );

Since it was a recommendation of potential value (and one that was pertinent to our problem), I went ahead and applied this change and tested again. Unfortunately, it did not help with the performance at all.

At this point, I started looking into the data in the tables as well as the SQL statements to identify any tuning opportunities. I found that PSAPMSGPUBDATA had around 800,000 rows of data in it. The way the SQL statements are written, it appears that the process would go over each and every row in the table (PSAPMSGPUBDATA) and check for the EXISTS clause before deleting.

Here is how I re-wrote the SQL statements which helped considerably.

SQL1 (Re-write):
  DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.CANONICALTRSFRMID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

SQL2 (Re-write):
DELETE
FROM PSAPMSGPUBDATA
WHERE IBTRANSACTIONID IN
  (SELECT C.IBTRANSACTIONID
  FROM PSAPMSGARCHTMP B,
    PSAPMSGSUBCON C
  WHERE B.IBTRANSACTIONID = C.IBPUBTRANSACTID
  );

If you notice the new SQL statements, you will find that the main difference was the replacement of the EXISTS clause with a IN clause.

Now, instead of looping through each row in PSAPMSGPUBDATA and checking the EXISTS clause, the SQL would just look for the rows that are in the results of the IN clause.

Here is a good article that details the usage and difference between the EXISTS and IN clause:
Usage of EXISTS and IN clause

If you are having issues with other DELETE sql statements (using EXISTS) in the APPMSGARCH app engine then you could try a similar approach. As always test the changes in your environment to see if you achieve the desired performance gains.

Note: Alternate solutions that I know have helped others in improving performance of APPMSGARCH :

1. Indexing affected tables appropriately.
2. Staging transaction ids that need to be deleted in a custom table (temporarily) to avoid complex where clauses in the DELETE statements.