The Hidden Cost of Workflow

1
The Hidden Cost of Workflow

• Gary Piper
• AUSOUG
• Sydney
• August 2005

2
Agenda

• Two issues
• Workflow load balancing (Cost)
• Purging obsolete workflow items
• Symptoms
• Workflow background process runs every 5 min 247
• Poor performance from workflow and workflow
  background engine performance degrading over time
• Actions
• Establish Cause
• Purging - What and How

3
The Theory

• Real-time Workflow load is managed by
  identifying costly activities / processes, so
  that when they are encountered by the workflow
  engine they are immediately given a deferred
  status and the workflow engine continues
  processing the next activity. The deferred
  processes are then processed by a Workflow
  Background Process which can be run at an
  appropriate time.

4
What Actually Happens

• The background process is run more frequently
  than is required
• Every 3 5 min 24 7
• Every 15 min 24 7
• What is actually being deferred?
• Should the activity be deferred ( Business
  decision )
• If an activity is required to be processed
  immediately
• Assess why the activity is so urgent
• Dont run a regular background process to clear
  the item ( fixes the symptom )
• Dont allow the items to become deferred in the
  first place ( fix the cause )

5
What's In a Cost

• Each workflow activity has an associated cost.
• The Cost value represents the number of seconds
  it should take to execute the activity.
• Generally when workflows are created, the cost is
  estimated or ignored.
• Cost is both entered and displayed in seconds.
  The value stored in the database
  wf_activities.cost is in hundredths of a second
• The default threshold for the Workflow Engine is
  50 hundredths of a second. Activities with a cost
  higher than this are deferred and are run by the
  Workflow Background process
• In a perfect world when sufficient runtime
  information is available, a Cost Vs Actual review
  should be completed

This rarely happens
6
How can this occur
7
How can this occur
8
The Basic Cause
Over the wall
Functional Team
Technical Team
Workflow Background Process Definition
Workflow Definition (Clone)
Activity must be process immediately
Cost is rarely defined and set
Functional Changes
Cost Reviews
9
Assess the Damage

• Frequency of execution
• Purging Obsolete Workflow Runtime Data
• Is it running into itself
• Frequency of execution Vs run time
• Restart from beginning of prior run?
• Workflow Background Process Example (15 min 24
  7)

10
So What is Being Processed

• How much is being processed?
• State change is overwritten, so there for little
  audibility
• Monitor by Select count of Deferred items type
  every n minutes
• Look at the runtime of the BG process to identify
  peaks

Toad E-Business suite Plug-in (Beta)
11
Review Actual Vs Execution Times
SELECT wa.item_type "Item Type",
substr(witt.display_name,1,40) "Display Name",
wa.name, wa.version, count()
"Executions", wa.cost "Intenal",
wa.cost / 100 "Cost (Sec)", round(min((
wias.end_date - wias.begin_date )86400),0) "Min
(Sec)", round(avg(( wias.end_date -
wias.begin_date )86400),0) "Avg (Sec)",
round(max(( wias.end_date - wias.begin_date
)86400),0) "Max (Sec)" FROM wf_activities wa,
wf_item_types_tl witt,
wf_item_activity_statuses wias WHERE
wa.item_type wias.item_type() and
wa.item_type witt.name and witt.language
userenv('LANG') and wa.cost gt 50 and
sysdate between wa.begin_date and
nvl(wa.end_date, sysdate 1) GROUP BY
wa.item_type, witt.display_name,
wa.name, wa.version,
wa.cost ORDER BY wa.cost DESC
Note Paper attendance required to interpret this
output
Item Typ Display Name NAME
VERSION Executions
Intenal Cost (Sec) Min (Sec) Avg (Sec)
Max(Sec) -------- ------------------------------
------------------------------ ----------
---------- ---------- ---------- ----------
---------- --------- MSCEXPWF MSC ASCP Exception
Messages PROCESS RESPONSE
10 5028 10000 100 0
0 1 POAPPRV PO Approval
MASS_UPDATE_RELEASES 1
232013 10000 100 0
144265 8204232 POAPPRV PO Approval
NOOP 24
232013 10000 100 0
144265 8204232 POAPPRV PO Approval
PLACE_SOURCING_INFO_ON_REQ 1
232013 10000 100 0
144265 8204232 REQAPPRV PO Requisition Approval
NOOP 29
51560 10000 100 0
450 3130878
12
Case Study - Workflow

• Issue
• Client site in a constant state of performance
  issues for past year
• Technical DBA constantly monitoring CPU and I/O
  usage and looking for resource hogs
• Current Activity
• Technical DBA reviewing tools to identify CPU and
  I/O bottlenecks
• A Capex is in place for hardware, fewer faster
  CPUs (4 2)
• Background
• Technical DBAs has limited E-Business Suite
  experience running the application

13
Case Study - Workflow
15,000 Requests per day
Concurrent Manager Activity Profile
Charts reproduced with permission of PIPER-Rx
14
Case Study - Workflow
Concurrent Request Activity (21 Days)
Concurrent Program Total Per day Per Hr Per Min
Workflow Background Process 210,091 10,004.3 416.8 6.9
Process transaction interface 23,940 1,140.0 47.5 0.8
Cost Manager ( BOM ) 41,539 1,978.0 82.4 1.4
Event Log 4,687 223.2 9.3 0.2
Data Load 3,530 168.1 7.0 0.1
Interface Cleanup ( Custom ) 2,954 140.7 5.9 0.1
Interface trip Stop 4,022 191.5 8.0 0.1
WIP Move Transaction Manager 4,387 208.9 8.7 0.1
Import Items 3,012 143.4 6.0 0.1
Set 2,095 99.8 4.2 0.1
Set Stage 4,506 214.6 8.9 0.1
Receiving Transaction Processor 1,055 50.2 2.1 0.0
Material cost transaction worker 904 43.0 1.8 0.0
15,000 Requests per day 93.4 of all concurrent
manager activity
15
Case Study - Workflow
Actual Vs User Activity (estimated)
Graphs reproduced with permission of PIPER-Rx
16
Case Study - Workflow
Actual User Activity by Hour of Day
Graphs reproduced with permission of PIPER-Rx
17
Case Study - Workflow
Concurrent Manager Activity (Adjusted)
Graphs reproduced with permission of PIPER-Rx
18
Case Study - Workflow
Revealing the real Activity profile
Graphs reproduced with permission of PIPER-Rx
19
Purging
PURGING
20
Purging Obsolete Workflow Runtime Data

• Argument set
• Item Type - leave this value blank to purge all
  item types
• Beware Insufficient resources to purge all
• Warning
• Workflow Purge will only purge items that have
  been defined with a persistence of TEMPORARY and
  the number of persistence days have expired. A
  workflow with a persistence of Permanent will
  never be purged by the default purge settings

21
So What Is Purgable
SELECT witt.display_name A, wit.name B,
wit.persistence_type C,
wit.persistence_days D, count(wi.item_key)
E, apps.wf_purge.getpurgeablecount(wit.name
) F ? Secret weapon FROM wf_item_types wit,
wf_item_types_tl witt, wf_items wi
WHERE wit.name witt.name and witt.language
userenv('LANG') and wit.name wi.item_type
GROUP BY witt.display_name, wit.name,
wit.persistence_type,
wit.persistence_days ORDER BY
count(wi.item_key) DESC
Note Column formatting not shown

Persistence Persistence Work Flow
Work Flow Display Name
WF Item Type Type Days
Count Purgable -------------------------------
--------- -------------------- -----------
----------- ---------- ---------- CSM Type 3
CSMTYPE3
TEMP 0 19415 0 FA
Account Generator FAFLEXWF
PERM 8651
8651 PA HR Related Updates Workflow
PAXWFHRU TEMP 0
8591 8480 AR Substitute Balancing Segment
ARSBALSG PERM
4823 4823 OM Order Line
OEOL TEMP
0 4802 3547 PO Approval
POAPPRV TEMP
20 3324 2321 Planning
Exception Messages MRPEXPWF
TEMP 0 3124
0 System Error
WFERROR TEMP 0
2287 49 ..
22
So What Is Purgable
Toad E-Business suite Plug-in (Beta)
23
Purging

• Warning
• wf_item_attribute_values (up to 51)
• wf_notifications
• Gather Schema Statistics
• Index Rebuilds

24
State of the Indexes
SELECT index_name "Index Name", num_rows
"index Row Count", decode(table_name,
'WF_ITEM_ACTIVITY_STATUSES',count_statuse
s.count, 'WF_ITEM_ATTRIBUTE_VALUES',
count_values.count, null) "Table Row Count",
last_analyzed "Last Analysed",
trunc(sysdate - last_analyzed) "last Analyzed
(days)", partitioned FROM
dba_indexes, (SELECT count() count
FROM WF_ITEM_ACTIVITY_STATUSES )
count_statuses, (SELECT count() count
FROM WF_ITEM_ATTRIBUTE_VALUES )
count_values WHERE table_owner 'APPLSYS'
and table_name in ('WF_ITEM_ATTRIBUTE_VALUES',
'WF_ACTIVITY_ATTR_VALUES',
'WF_ACTIVITY_TRANSITION
S', 'WF_ITEM_ACTIVITY_ST
ATUSES',
'WF_ITEM_ACTIVITY_STATUSES_H',
'WF_PROCESS_ACTIVITIES',
'WF_NOTIFICATIONS') and index_type
'NORMAL' ORDER BY table_name, index_name
Index Name index Row Count
Table Row Count Last Anal last Analyzed (days)
PAR ------------------------------
--------------- --------------- ---------
-------------------- --- WF_ITEM_ACTIVITY_STATUSES
_N1 708324 740035 01-APR-04
375 NO WF_ITEM_ACTIVITY_STATUSES_N2
13823 740035 17-JUN-03
663 NO WF_ITEM_ACTIVITY_STATUSES_N3
725979 740035 17-JUN-03
663 NO WF_ITEM_ACTIVITY_STATUSES_N4
725979 740035 17-JUN-03
663 NO WF_ITEM_ACTIVITY_STATUSES_PK
725979 740035 17-JUN-03
663 YES ? WF_ITEM_ATTRIBUTE_VALUES_PK
4194640 4178092 17-JUN-03
663 NO
25
Questions
Questions? Disclaimer All material contained in
this document is provided by the author "as is"
and any express or implied warranties, including,
but not limited to, any implied warranties of
merchantability and fitness for a particular
purpose are disclaimed. In no event shall the
author be liable for any direct, indirect,
incidental, special, exemplary, or consequential
damages (including, but not limited to, loss of
use, data, or profits or business interruption)
however caused and on any theory of liability,
whether in contract, strict liability, or tort
(including negligence or otherwise) arising in
any way out of the use of any content or
information, even if advised of the possibility
of such damage. It is always recommended that you
seek independent, professional advice before
implementing any ideas or changes to ensure that
they are appropriate
26
The Hidden Cost of Workflow

• Gary Piper
• AUSOUG
• Sydney
• August 2005