The hidden cost of workflow
1 / 26

The Hidden Cost of Workflow - PowerPoint PPT Presentation

  • Uploaded on

The Hidden Cost of Workflow. Gary Piper AUSOUG Sydney August 2005. Agenda…. Two issues Workflow load balancing (Cost) Purging obsolete workflow items Symptoms: Workflow background process runs every 5 min 24*7

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'The Hidden Cost of Workflow' - ollie

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
The hidden cost of workflow

The Hidden Cost of Workflow

Gary Piper



August 2005


  • 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

The theory



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.

What actually happens
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 )

What s in a cost
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

The basic cause
The Basic Cause…

Over the wall

Functional Team

Technical Team








Activity must be

process immediately

Cost is rarely

defined and set

Functional Changes

Cost Reviews

Assess the damage
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)

So what is being processed
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)

Review actual vs execution times
Review Actual Vs Execution Times…

SELECT wa.item_type "Item Type",

substr(witt.display_name,1,40) "Display Name",,


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 =

and witt.language = userenv('LANG')

and wa.cost > 50

and sysdate between wa.begin_date and nvl(wa.end_date, sysdate + 1)

GROUP BY wa.item_type,




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

Case study workflow
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

Case study workflow1
Case Study - Workflow…

15,000 Requests per day

Concurrent Manager Activity Profile

Charts reproduced with permission of PIPER-Rx

Case study workflow2
Case Study - Workflow…

Concurrent Request Activity (21 Days)

15,000 Requests per day

93.4% of all concurrent manager activity

Case study workflow3
Case Study - Workflow…

Actual Vs User Activity (estimated)

Graphs reproduced with permission of PIPER-Rx

Case study workflow4
Case Study - Workflow…

Actual User Activity by Hour of Day

Graphs reproduced with permission of PIPER-Rx

Case study workflow5
Case Study - Workflow…

Concurrent Manager Activity (Adjusted)

Graphs reproduced with permission of PIPER-Rx

Case study workflow6
Case Study - Workflow…

Revealing the “real” Activity profile

Graphs reproduced with permission of PIPER-Rx



Purging obsolete workflow runtime data
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

So what is purgable
So What Is Purgable…

Note: Column formatting not shown

SELECT witt.display_name A, B,

wit.persistence_type C,

wit.persistence_days D,

count(wi.item_key) E,

apps.wf_purge.getpurgeablecount( F  Secret weapon

FROM wf_item_types wit,

wf_item_types_tl witt,

wf_items wi


and witt.language = userenv('LANG')

and = wi.item_type

GROUP BY witt.display_name,,



ORDER BY count(wi.item_key) DESC;

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


So what is purgable1
So What Is Purgable…

Toad E-Business suite Plug-in (Beta)


  • Warning:

    • wf_item_attribute_values (up to 5:1)

    • wf_notifications

    • Gather Schema Statistics

    • Index Rebuilds

State of the indexes
State of the Indexes…

SELECT index_name "Index Name",

num_rows "index Row Count",



'WF_ITEM_ATTRIBUTE_VALUES', count_values.count, null) "Table Row Count",

last_analyzed "Last Analysed",

trunc(sysdate - last_analyzed) "last Analyzed (days)",


FROM dba_indexes,

(SELECT count(*) count


(SELECT count(*) count


WHERE table_owner = 'APPLSYS'

and table_name in ('WF_ITEM_ATTRIBUTE_VALUES',







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



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

The hidden cost of workflow1

The Hidden Cost of Workflow

Gary Piper



August 2005