Preface
From Note 402144.1 -- FAQ: Best Practices For Custom Order Entry Workflow Design and Note 405275.1 -- How to Detect Data Corruption and Purge More Eligible OEOH/OEOL Workflow Items for Order Management Workflow, one of the most recommended practices from the Order Management product team is to utilize the Retry Activities in Error" Concurrent Program which is delivered in Patch 5248424 - Order Management Exception Management (11.5.9) and Patch 4420026 (11.5.10).The need for it?
Order Management Workflow for order lines fails all the time, due to a variety of different reasons. While the Standard Order Entry form and Quick Sales Order form does allow the user to manually progress the Order line or Retry workflow from Workflow Monitor self service screen, this is SIMPLY not a scalable way or retrying Errored workflow activities.Hack it!
So then customers come up with their own custom concurrent program for retrying errored workflow activities for OEOH/OEOL workflow items. What they do not realize is that using homegrown OM workflow retry scripts causes huge volume of OM workflow data to remain stuck in the WF_ITEM_ACTIVITY_STATUSES, WF_ITEM_ACTIVITY_STATUSES_H, WF_ITEM_ATTRIBUTE_VALUES tables.Over time, this volume grows to millions of rows, which further results in all sorts of arcane workflow performance issues.
Is there a different way?
Could we have done something differently? Well, newsflash. OM product team has come up with a "Retry Activities in Error" Concurrent Program which does all this AND a whole lot of other good things like making the existing OM workflow data more purge eligible.What does it do?
This concurrent program delivers new functionality. It also helps purge WFERROR/OMERROR child workflows of OEOH/OEOL which are still ACTIVE and shouldn't be (if parent OEOH/OEOL doesn't have any open errors currently). Therefore, it makes more OEOH/OEOL workflow items eligible for purge by "Purge Obsolete Runtime Workflow Data" concurrent program.This "Retry Activities in Error" concurrent program is is delivered in Patch 5248424 - Order Management Exception Management (11.5.9) and Patch 4420026 (11.5.10).
The caveat in 11.5.9
If you are running Oracle Applications 11.5.9, this program has a severe performance problem. Let's try to understand how this performance issue can be bridged.
OEOL :
Date Submitted : 16-FEB-2007 15:28:57
Date Started : 16-FEB-2007 15:29:16
Date Completed : 19-FEB-2007 06:34:38
OEOH :
Date Submitted : 16-FEB-2007 15:29:41
Date Started : 19-FEB-2007 06:34:40
Date Completed : 19-FEB-2007 07:49:36
Did you notice it? It took more than 2 days for the first run to finish off!!
This was totally unacceptable. TAR 6162173.993 was opened with Oracle Support on this issue.
It really seemed that if there were an index on OE_PROCESSING_MSGS.TRANSACTION_ID would be a lot better. http://etrm.oracle.com is an authoritative source on what indexes are available on any table for any release. A simple comparision of indexes available on OE_PROCESSING_MSGS in 11.5.9 and 11.5.10 revealed that the OE_PROCESSING_MSGS_U1 index was acutally missing in 11.5.9!
CREATE UNIQUE INDEX ONT.OE_PROCESSING_MSGS_U1 ON ONT.OE_PROCESSING_MSGS (TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 3M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS;
Patch 5895614 -- To improve the performance of concurrent program 'Retry Activities in Error' for 11.5.9 customers, an index is created on the table OE_PROCESSING_MSGS -- was provided on 27th March 2007.
Best Practices For Custom Order Entry Workflow Design
How to Detect Data Corruption and Purge More Eligible OEOH/OEOL Workflow Items for Order Management Workflow.
First blood??
The first time the customer ran this concurrent program in a test environment, the timings blew the daylights out of everyone:OEOL :
Date Submitted : 16-FEB-2007 15:28:57
Date Started : 16-FEB-2007 15:29:16
Date Completed : 19-FEB-2007 06:34:38
OEOH :
Date Submitted : 16-FEB-2007 15:29:41
Date Started : 19-FEB-2007 06:34:40
Date Completed : 19-FEB-2007 07:49:36
Did you notice it? It took more than 2 days for the first run to finish off!!
This was totally unacceptable. TAR 6162173.993 was opened with Oracle Support on this issue.
Getting to the bottom of it
TKPROF utility was used to find out the SQL which was using the maximum parse, execute and fetch times. This sql came at the top:It really seemed that if there were an index on OE_PROCESSING_MSGS.TRANSACTION_ID would be a lot better. http://etrm.oracle.com is an authoritative source on what indexes are available on any table for any release. A simple comparision of indexes available on OE_PROCESSING_MSGS in 11.5.9 and 11.5.10 revealed that the OE_PROCESSING_MSGS_U1 index was acutally missing in 11.5.9!
The fix
Bug 5895614 was logged with OM development for providing the patch for missing index. The resolution was simply to provide the following index in 11.5.9:CREATE UNIQUE INDEX ONT.OE_PROCESSING_MSGS_U1 ON ONT.OE_PROCESSING_MSGS (TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 3M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS;
Patch 5895614 -- To improve the performance of concurrent program 'Retry Activities in Error' for 11.5.9 customers, an index is created on the table OE_PROCESSING_MSGS -- was provided on 27th March 2007.
Suggested Followup Readings
Please followup on the discussion on implementing good Order Management workflow best practices:Best Practices For Custom Order Entry Workflow Design
How to Detect Data Corruption and Purge More Eligible OEOH/OEOL Workflow Items for Order Management Workflow.
Comments (1)
Very insightful article. Will use this info to help diagnose Workflow Perf Issues we are observing in our system.
Posted by Sabyasachi Banerjee | November 12, 2008 12:40 PM
Posted on November 12, 2008 12:40