Transcript Slide 1

PIPER-Rx
The Hidden Cost of Workflow
Gary Piper
AUSOUG
Sydney
August 2005
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
Agenda…
 Two issues

Workflow load balancing (Cost)
 Purging obsolete workflow items
 Symptoms:

Workflow background process runs every 5 min 24*7
 Poor performance from workflow and workflow &
background engine performance degrading over time
 Actions:

Establish Cause
 Purging - What and How
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
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.
X
X
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
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
 Don’t run a regular background process to clear the
item ( fixes the symptom )
 Don’t allow the items to become deferred in the first
place ( fix the cause )
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
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
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
How can this occur…
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
How can this occur…
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
The Basic Cause…
Over the wall
Functional Team
Technical Team
Workflow
Background
Process
Definition
Workflow
Definition
(Clone)
Cost is rarely
defined and set
Activity must be
process immediately
Functional Changes
Cost Reviews
A New Approach to Managing Oracle E-Business Suite
A New Approach to Managing Oracle E-Business Suite
22-04-2004 : 9:55
22-04-2004 : 6:1
22-04-2004 : 2:42
21-04-2004 : 23:46
21-04-2004 : 20:16
21-04-2004 : 16:53
21-04-2004 : 12:31
21-04-2004 : 7:51
21-04-2004 : 4:45
21-04-2004 : 1:26
20-04-2004 : 22:6
20-04-2004 : 18:40
20-04-2004 : 12:58
20-04-2004 : 6:44
20-04-2004 : 3:25
20-04-2004 : 0:24
19-04-2004 : 21:4
19-04-2004 : 17:41
19-04-2004 : 12:49
19-04-2004 : 6:40
19-04-2004 : 3:24

19-04-2004 : 0:6
PIPER-Rx
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)
30
25
20
15
10
5
0
PIPER-Rx
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)
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
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
round(avg(( wias.end_date - wias.begin_date )*86400),0) "Avg
round(max(( wias.end_date - wias.begin_date )*86400),0) "Max
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 > 50
and sysdate between wa.begin_date and nvl(wa.end_date, sysdate +
GROUP BY wa.item_type,
witt.display_name,
wa.name,
wa.version,
wa.cost
ORDER BY wa.cost DESC;
(Sec)",
(Sec)",
(Sec)"
1)
Note: Paper attendance required to interpret this output
Item Typ
-------MSCEXPWF
POAPPRV
POAPPRV
POAPPRV
REQAPPRV
Display Name
-----------------------------MSC: ASCP Exception Messages
PO Approval
PO Approval
PO Approval
PO Requisition Approval
NAME
VERSION Executions
Intenal Cost (Sec) Min (Sec) Avg (Sec) Max(Sec)
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- --------PROCESS RESPONSE
10
5028
10000
100
0
0
1
MASS_UPDATE_RELEASES
1
232013
10000
100
0
144265
8204232
NOOP
24
232013
10000
100
0
144265
8204232
PLACE_SOURCING_INFO_ON_REQ
1
232013
10000
100
0
144265
8204232
NOOP
29
51560
10000
100
0
450
3130878
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
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 CPU’s (4 – 2)
 Background:
 Technical DBA’s has limited E-Business Suite experience
running the application
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
Case Study - Workflow…
Concurrent Manager Activity Profile
22:00 - 23:00
23:00 - 24:00
20:00 - 21:00
21:00 - 22:00
18:00 - 19:00
19:00 - 20:00
17:00 - 18:00
15:00 - 16:00
16:00 - 17:00
13:00 - 14:00
14:00 - 15:00
11:00 - 12:00
12:00 - 13:00
09:00 - 10:00
10:00 - 11:00
07:00 - 08:00
08:00 - 09:00
06:00 - 07:00
04:00 - 05:00
05:00 - 06:00
02:00 - 03:00
03:00 - 04:00
00:00 - 01:00
01:00 - 02:00
03-Mar-05 (Thu)
02-Mar-05 (Wed)
01-Mar-05 (Tue)
28-Feb-05 (Mon)
27-Feb-05 (Sun)
26-Feb-05 (Sat)
25-Feb-05 (Fri)
24-Feb-05 (Thu)
23-Feb-05 (Wed)
22-Feb-05 (Tue)
21-Feb-05 (Mon)
20-Feb-05 (Sun)
19-Feb-05 (Sat)
18-Feb-05 (Fri)
17-Feb-05 (Thu)
16-Feb-05 (wed)
15-Feb-05 (Tue)
14-Feb-05 (Mon)
13-Feb-05 (Sun)
12-Feb-05 (Sat)
11-Feb-05 (Fri)
10-Feb-05 (Thu)
09-Feb-05 (wed)
08-Feb-05 (Tue)
07-Feb-05 (Mon)
06-Feb-05 (Sun)
05-Feb-05 (Fri)
04-Feb-05 (Thu)
Charts reproduced with permission of PIPER-Rx
A New Approach to Managing Oracle E-Business Suite
15,000 Requests per day
900-1000
800-900
700-800
600-700
500-600
400-500
300-400
200-300
100-200
0-100
PIPER-Rx
Case Study - Workflow…
Concurrent Request Activity (21 Days)
Concurrent Program
Workflow Background Process
Total
Per day
Per Hr
Per Min
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
904
43.0
1.8
0.0
Material cost transaction worker
15,000 Requests per day
93.4% of all concurrent manager activity
A New Approach to Managing Oracle E-Business Suite
Graphs reproduced with permission of PIPER-Rx
A New Approach to Managing Oracle E-Business Suite
03-Mar-05 (Thu)
02-Mar-05 (Wed)
01-Mar-05 (Tue)
28-Feb-05 (Mon)
27-Feb-05 (Sun)
26-Feb-05 (Sat)
25-Feb-05 (Fri)
24-Feb-05 (Thu)
23-Feb-05 (Wed)
22-Feb-05 (Tue)
21-Feb-05 (Mon)
20-Feb-05 (Sun)
19-Feb-05 (Sat)
18-Feb-05 (Fri)
17-Feb-05 (Thu)
16-Feb-05 (wed)
15-Feb-05 (Tue)
14-Feb-05 (Mon)
13-Feb-05 (Sun)
12-Feb-05 (Sat)
11-Feb-05 (Fri)
10-Feb-05 (Thu)
09-Feb-05 (wed)
08-Feb-05 (Tue)
07-Feb-05 (Mon)
06-Feb-05 (Sun)
05-Feb-05 (Fri)
04-Feb-05 (Thu)
PIPER-Rx
Case Study - Workflow…
Actual Vs User Activity (estimated)
18,000
16,000
14,000
12,000
10,000
8,000
6,000
4,000
2,000
0
Graphs reproduced with permission of PIPER-Rx
A New Approach to Managing Oracle E-Business Suite
23:00 - 24:00
22:00 - 23:00
21:00 - 22:00
20:00 - 21:00
19:00 - 20:00
18:00 - 19:00
17:00 - 18:00
16:00 - 17:00
15:00 - 16:00
14:00 - 15:00
13:00 - 14:00
12:00 - 13:00
11:00 - 12:00
10:00 - 11:00
09:00 - 10:00
08:00 - 09:00
07:00 - 08:00
06:00 - 07:00
05:00 - 06:00
04:00 - 05:00
03:00 - 04:00
02:00 - 03:00
01:00 - 02:00
00:00 - 01:00
PIPER-Rx
Case Study - Workflow…
Actual User Activity by Hour of Day
3,000
2,500
2,000
1,500
1,000
500
0
PIPER-Rx
Case Study - Workflow…
Concurrent Manager Activity (Adjusted)
23:00 - 24:00
21:00 - 22:00
22:00 - 23:00
20:00 - 21:00
18:00 - 19:00
19:00 - 20:00
16:00 - 17:00
17:00 - 18:00
15:00 - 16:00
13:00 - 14:00
14:00 - 15:00
11:00 - 12:00
12:00 - 13:00
09:00 - 10:00
10:00 - 11:00
08:00 - 09:00
06:00 - 07:00
07:00 - 08:00
04:00 - 05:00
05:00 - 06:00
03:00 - 04:00
01:00 - 02:00
02:00 - 03:00
00:00 - 01:00
03-Mar-05 (Thu)
02-Mar-05 (Wed)
01-Mar-05 (Tue)
28-Feb-05 (Mon)
27-Feb-05 (Sun)
26-Feb-05 (Sat)
25-Feb-05 (Fri)
24-Feb-05 (Thu)
23-Feb-05 (Wed)
22-Feb-05 (Tue)
21-Feb-05 (Mon)
20-Feb-05 (Sun)
19-Feb-05 (Sat)
18-Feb-05 (Fri)
17-Feb-05 (Thu)
16-Feb-05 (wed)
15-Feb-05 (Tue)
14-Feb-05 (Mon)
13-Feb-05 (Sun)
12-Feb-05 (Sat)
11-Feb-05 (Fri)
10-Feb-05 (Thu)
09-Feb-05 (wed)
08-Feb-05 (Tue)
07-Feb-05 (Mon)
06-Feb-05 (Sun)
05-Feb-05 (Fri)
04-Feb-05 (Thu)
Graphs reproduced with permission of PIPER-Rx
A New Approach to Managing Oracle E-Business Suite
180-200
160-180
140-160
120-140
100-120
80-100
60-80
40-60
20-40
0-20
PIPER-Rx
Case Study - Workflow…
Revealing the “real” Activity profile
03-Mar-05 (Thu)
02-Mar-05 (Wed)
01-Mar-05 (Tue)
28-Feb-05 (Mon)
27-Feb-05 (Sun)
26-Feb-05 (Sat)
25-Feb-05 (Fri)
24-Feb-05 (Thu)
23-Feb-05 (Wed)
22-Feb-05 (Tue)
21-Feb-05 (Mon)
20-Feb-05 (Sun)
19-Feb-05 (Sat)
18-Feb-05 (Fri)
17-Feb-05 (Thu)
16-Feb-05 (wed)
15-Feb-05 (Tue)
14-Feb-05 (Mon)
13-Feb-05 (Sun)
12-Feb-05 (Sat)
11-Feb-05 (Fri)
10-Feb-05 (Thu)
09-Feb-05 (wed)
08-Feb-05 (Tue)
07-Feb-05 (Mon)
06-Feb-05 (Sun)
05-Feb-05 (Fri)
04-Feb-05 (Thu)
900-1000
800-900
700-800
600-700
500-600
Graphs reproduced with permission of PIPER-Rx
A New Approach to Managing Oracle E-Business Suite
23:00 - 24:00
21:00 - 22:00
22:00 - 23:00
20:00 - 21:00
18:00 - 19:00
19:00 - 20:00
16:00 - 17:00
17:00 - 18:00
15:00 - 16:00
13:00 - 14:00
14:00 - 15:00
11:00 - 12:00
12:00 - 13:00
09:00 - 10:00
10:00 - 11:00
08:00 - 09:00
06:00 - 07:00
07:00 - 08:00
04:00 - 05:00
05:00 - 06:00
03:00 - 04:00
01:00 - 02:00
02:00 - 03:00
400-500
300-400
200-300
100-200
0-100
00:00 - 01:00
22:00 - 23:00
23:00 - 24:00
20:00 - 21:00
21:00 - 22:00
18:00 - 19:00
19:00 - 20:00
17:00 - 18:00
15:00 - 16:00
16:00 - 17:00
13:00 - 14:00
14:00 - 15:00
11:00 - 12:00
12:00 - 13:00
09:00 - 10:00
10:00 - 11:00
07:00 - 08:00
08:00 - 09:00
06:00 - 07:00
04:00 - 05:00
05:00 - 06:00
02:00 - 03:00
03:00 - 04:00
00:00 - 01:00
01:00 - 02:00
03-Mar-05 (Thu)
02-Mar-05 (Wed)
01-Mar-05 (Tue)
28-Feb-05 (Mon)
27-Feb-05 (Sun)
26-Feb-05 (Sat)
25-Feb-05 (Fri)
24-Feb-05 (Thu)
23-Feb-05 (Wed)
22-Feb-05 (Tue)
21-Feb-05 (Mon)
20-Feb-05 (Sun)
19-Feb-05 (Sat)
18-Feb-05 (Fri)
17-Feb-05 (Thu)
16-Feb-05 (wed)
15-Feb-05 (Tue)
14-Feb-05 (Mon)
13-Feb-05 (Sun)
12-Feb-05 (Sat)
11-Feb-05 (Fri)
10-Feb-05 (Thu)
09-Feb-05 (wed)
08-Feb-05 (Tue)
07-Feb-05 (Mon)
06-Feb-05 (Sun)
05-Feb-05 (Fri)
04-Feb-05 (Thu)
180-200
160-180
140-160
120-140
100-120
80-100
60-80
40-60
20-40
0-20
PIPER-Rx
Purging…
PURGING
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
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
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
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,
Note: Column formatting not shown
apps.wf_purge.getpurgeablecount(wit.name) F
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;
Display Name
---------------------------------------CSM Type 3
FA Account Generator
PA: HR Related Updates Workflow
AR: Substitute Balancing Segment
OM Order Line
PO Approval
Planning Exception Messages
System: Error
……………………..
WF Item Type
-------------------CSMTYPE3
FAFLEXWF
PAXWFHRU
ARSBALSG
OEOL
POAPPRV
MRPEXPWF
WFERROR
 Secret weapon
Persistence Persistence Work Flow Work Flow
Type
Days
Count
Purgable
----------- ----------- ---------- ---------TEMP
0
19415
0
PERM
8651
8651
TEMP
0
8591
8480
PERM
4823
4823
TEMP
0
4802
3547
TEMP
20
3324
2321
TEMP
0
3124
0
TEMP
0
2287
49
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
So What Is Purgable…
Toad E-Business suite Plug-in (Beta)
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
Purging…
 Warning:

wf_item_attribute_values (up to 5:1)
 wf_notifications
 Gather Schema Statistics
 Index Rebuilds
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
State of the Indexes…
SELECT index_name "Index Name",
num_rows "index Row Count",
decode(table_name,
'WF_ITEM_ACTIVITY_STATUSES',count_statuses.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_TRANSITIONS',
'WF_ITEM_ACTIVITY_STATUSES',
'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
------------------------------ --------------- --------------WF_ITEM_ACTIVITY_STATUSES_N1
708324
740035
WF_ITEM_ACTIVITY_STATUSES_N2
13823
740035
WF_ITEM_ACTIVITY_STATUSES_N3
725979
740035
WF_ITEM_ACTIVITY_STATUSES_N4
725979
740035
WF_ITEM_ACTIVITY_STATUSES_PK
725979
740035
WF_ITEM_ATTRIBUTE_VALUES_PK
4194640
4178092
Last Anal last Analyzed (days)
--------- -------------------01-APR-04
375
17-JUN-03
663
17-JUN-03
663
17-JUN-03
663
17-JUN-03
663
17-JUN-03
663
A New Approach to Managing Oracle E-Business Suite
PAR
--NO
NO
NO
NO
YES 
NO
PIPER-Rx
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
A New Approach to Managing Oracle E-Business Suite
PIPER-Rx
The Hidden Cost of Workflow
Gary Piper
AUSOUG
Sydney
August 2005
A New Approach to Managing Oracle E-Business Suite