1 / 68

Making Workflow Flow

Making Workflow Flow. Jerry Ireland Principal, RIGHTSIZING, INC. jerryi@rsiz.com. Introduction. About the Speaker Oracle Ace –Application Development Over 30 years Oracle Consulting Over 20 years E-Business Suite support 15 years Oracle Workflow Over 100 presentations

archie
Download Presentation

Making Workflow Flow

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Making Workflow Flow Jerry Ireland Principal,RIGHTSIZING, INC. jerryi@rsiz.com

  2. Introduction • About the Speaker • Oracle Ace –Application Development • Over 30 years Oracle Consulting • Over 20 years E-Business Suite support • 15 years Oracle Workflow • Over 100 presentations • Over 20 year member of ODTUG and OAUG • Currently ODTUG Board of Directors

  3. About the Audience • EBS Version • 11i • 12.0 • 12.1 • Database Version • 10gR2 • 11gR1 • 11gR2 • Job Role • System Admin • Workflow Admin • DBA • Functional • OAUG member • OAUG Workflow SIG member

  4. Agenda Introduction Patch Current Performance Data Footprint Error Handling Notifications Order Management

  5. Patch Current • WF part of ATG group • Effect most products • Pseudo Product • AME and HR products integral parts • Very time consuming and complicated • 1-Offs, Family Packs, Mini Packs • More recent versions in other patches • Prerequisites • prerequisites of prerequisites ---

  6. Patch Current Sample Spreadsheet • R12.1.3 Upgrade • Compliled by • Karen Brownfield, John Peters, and Me • To be considered on top of 12.1.3 • Starting Point • Do your own verification and research • Sample Spreadsheet

  7. Patch Current • Recommended Patches • 125452.1 How to find Latest Recommended Patches for E-business suite R12 or 11i • PatchsetLevels • Application Name, Product Version, Current Patch Level, Status (Installed, Shared Product, Inactive) • AD_TOP/sql/adutconf.sqlor Workflow Analyzer • Or Log into OAM • Support Cart  Applications Signature Collect "Product Information“  View

  8. Patch Current • Patch Comparison • 139684.1 Oracle Applications Current Patchset Comparison Utility – patchsets.sh • Checks against internal tables for applied patches • Depends on file from Oracle ftp site for nightly new list of most recent patchset/family packs • List of missing patches • Workflow Analyzer

  9. Patch Current • Patch Wizard 976188.1 • Downloads info bundles with meta data and code levels • Creates recommendations • Does an impact analysis • Files effected • Recommended or code level update • Flag files that you want alert when changed ( customized?) • For a more detailed look at the Patch Wizard look for • ORACLE E-BUSINESS SUITE PATCH WIZARD PATH TO LESS ERRORS by John Stouffer

  10. Patch Current • Patch Wizard • R12 - Prereqs are stated as Code levels to make it easier to identify which patches satisfy the prereq • Workflow Analyzer

  11. Workflow Analyzer • 1369938.1 “Workflow Analyzer script for E-Business Suite Workflow Monitoring and Maintenance” • This is a must!!!! • Can be run by concurrent manager 1425053.1 • It is updated frequently so download new script often • SQL used to produce results are available on report

  12. Workflow Analyzer Workflow Administration Workflow Footprint Workflow Concurrent Programs Workflow Notification Mailer Workflow Patch Levels Workflow Analyzer Sample

  13. Patch Current • None of the tools do a complete job with : • Prerequisites of prerequisite patches • 1-Off patches • Conditional patches • Workflow Analyzer does the best • Must read documentation on all patches

  14. Performance • Patches • Init.ora • Advanced Queuing • Notification Mailers • Database • Profile Options • Data Footprint • Workflow Analyzer • History Loopers • Notifications • New Features

  15. Performance Patches • Recommended Performance Patches • 244040.1 – Section 7: Applications Technology Products and Components • Download and read ALL documentation • Bug 7476877 – purge performance if earlier than 11.5.10 RUP 7 • Some but not all included in Workflow Analyzer

  16. Performance - init.ora • AQ_TM_PROCESSES • Must be at least 1 for background process to run for databases prior to 10g • Auto tuning (don’t set) gets better performance for 10g on • JOB_QUEUE_PROCESSES • 5 for 11.5.9 • 10 for 11.5.10 and 12 • Oracle seeds value as 2

  17. Performance Advanced Queuing • Ensure Advanced Queuing not set for statement or debug logging • FND: Debug Log Enabled – No • FND: Debug Log Level – Unexpected • FND: Debug Log Modules – NULL • Make sure log level on all listeners is set to Error

  18. Performance Advanced Queuing • Rebuild/coelesce Indexes/IOTS regularly • Reduce QMON CPU usage and Redo generation • Database < 11.2 • Garbage collection on dequeue indexes/IOTS already performed • No space management for time-management, history, spillover • Run at least once a day during quiet period • Can be executed while enqueue/dequeue running • 1336354.1 describes a way to automate

  19. Performance Advanced Queuing • Rebuild/coelesce Indexes/IOTS regularly • Database >= 11.2 • Runs automatically as needed • Can still be run manually if necessary • Cleanup Control Queue • 469045.1 Troubleshooting WF_CONTROL Agent Issues” • Controls all queues • Run every 12 hours

  20. Performance Advanced Queuing • 469009.1 “Troubleshooting Workflow Agent Listener’s failure to start” • Business Event System not enabled • WF_CONTROL queue related issues • Workflow Agents have an invalid SYSTEM_GUID in WF_AGENTS and WF_RESOURCES tables • Queues are missing • Queues are invalid • Queue Handlers are missing or are invalid • Technology stack setup issues

  21. Performance Advanced Queuing • 469009.1 “Troubleshooting Workflow Agent Listener’s failure to start” • Start investigating by setting Container log level at STATEMENT and reproducing the issue. • Follow detailed instructions in the MOS to determine if any of the above problems exist

  22. Performance Notification Mailers • Define dedicated mailers for high volume item types and WFERROR • Maintaining mailer parameters • Autoconfig Context File • Username • Inbound server name • Outbound server name • Reply-to address

  23. Performance Notification Mailers • Maintaining mailer parameters • Changing Username, Inbound server name, Reply-to address in Workflow Manager automatically make changes in context file • Changing Outbound server name does not • Have to manually change it in context file

  24. Performance Database • Pin Top Apps PL/SQL packages by executions • Partition Tables • 260884.1 How to Partition tables in OWF.G • $FND_TOP/sql/wffngen.sql • Translates activity function calls into static calls • Add high volume item types to itemtypeList_t • Can not customize items in the list

  25. Performance Profile Options • Profile Options • Account Generator: Run in Debug Mode • Yes – status information is saved to the Workflow history tables. • No –no status information is saved • increase performance • Reduce wait time

  26. Performance Profile Options • Profile Options • Purchasing: Workflow Processing Mode • Online - run Purchase Order and Requisition workflows online when transactions are created. • Background - deferred to the Background Engine. • can increase throughput • Make sure you have a Background Engine to run these deferred processes

  27. Performance - Other • As needed Attributes • Many attributes never get a value • 11,114,390 APCCARD • 2,757,722 HRSSA • 2,621,399WFERROR • 2,601,654OTWF • 1,577,750PAXWFHRU • 1,346,090IRC_NTF • 1,090,962OEOL • Put a special attribute #ONDEMANDATTR at top-level runnable process activity • Attribute record created only when populated • Save over 22 million rows

  28. Performance DataFootprint Runtime Data

  29. PerformanceData FootprintRuntime Data Runtime History

  30. Performance Data Footprint • PURGE • 132254.1 Speeding Up And Purging Workflow • Yet another list of patches to be included • Gather Histogram Stats • Use wfretry.sql script to clear individual errors • Use bde_wf_retry.sqlbased on item type and process

  31. Performance Data Footprint • Why are things not purging? • Purge processes not covering all item types • Background Engines not runing • Error workflow called and not complete • Notification not responded to • Parent or child workflow not complete • Proper listeners not started

  32. Purge processes not covering all item types • Schedule Nightly or at minimum Weekly • Parameters • Leave Item Type/Item Key blank – All types • Age – between 7 and 14 • Persistence Type • One Temporaryand one Permanent • Core Workflow Only – Set to Y • Run with value set to N monthly • Commit Frequency – leave at default – 500

  33. Background Engines not running • Run separate Engines for Stuck • Parameters NULL, NULL, NULL, No, No, Yes • Once/Week or more • And Timed Out • Parameters NULL, NULL, NULL, No, Yes, No • Base timing on average timeout times

  34. Background Engines not running • Run Engine for Deferred activities separately • More often than 15 minutes is unusual • Run targeted engines for high volume item types or item types that need faster responses (OEOL?) • Parameters: Order Line, NULL, NULL, Yes, No, No • Run generic every 15-60 minutes • Parameters: NULL, NULL, NULL, Yes, No, No

  35. Error Handling • Start with error workflows • Basic process • Query for items with open error workflows • Start with largest volumes • Query Details • Track down the cause • Fix the problems

  36. Error Handling • Query open errors • WFERROR not the only error (thanksKaren Brownfield) SELECT item_type, parent_item_type, DECODE (end_date, NULL, 'OPEN', 'CLOSED') error_type_status, COUNT (*) FROM wf_items WHERE parent_item_type is not null AND item_type in ('CUNNLWF','DOSFLOW','DOSFLOWE', 'ECXERROR', 'HRSSA', 'HRSTAND', 'HXCEMP', 'IBUHPSUB', 'OKLAMERR', 'OMERROR', 'PARMAAP', 'PARMATRX', 'POERROR', 'WFSTD', 'XDPWFSTD', 'ZPBWFERR', 'WFERROR') GROUP BY item_type,parent_item_type, DECODE (end_date, NULL, 'OPEN', 'CLOSED') ORDER BY 4 desc, item_type, parent_item_type; Error output

  37. Error Handling select ias.item_type ,ap.NAME process ,ac.name Activity ,ias.activity_result_code Result ,Count(*) Errcount from wf_item_activity_statusesias, wf_process_activities pa, wf_activities ac, wf_activitiesap, wf_itemsi where ias.activity_status = 'ERROR' and ias.process_activity = pa.instance_id and pa.activity_name = ac.name and pa.activity_item_type = ac.item_type and pa.process_name = ap.name and pa.process_item_type = ap.item_type and pa.process_version = ap.version and i.item_type = ias.item_type and i.item_key = ias.item_key and i.begin_date >= ac.begin_date and i.begin_date < nvl(ac.end_date, i.begin_date+1) group by ias.item_type, ias.error_name, ap.NAME, ac.name, ias.activity_result_code having Count(*) > 100 order by ErrCountdesc;

  38. Error HandlingError Detail select * from (select ias.item_type ,ap.NAME process ,ac.name Activity ,ias.activity_result_code Result ,ias.error_nameERROR_NAME ,ias.error_messageERROR_MESSAGE ,ias.error_stack ERROR_STACK ,Count(*) Errcount from wf_item_activity_statusesias, wf_process_activities pa, wf_activitiesac, wf_activitiesap, wf_itemsi where ias.activity_status = 'ERROR' and ias.process_activity = pa.instance_idError Details and pa.activity_name = ac.name and pa.activity_item_type = ac.item_type and pa.process_name = ap.name and pa.process_item_type = ap.item_type and pa.process_version = ap.version and i.item_type = ias.item_type and i.item_key = ias.item_key and i.begin_date >= ac.begin_date and i.begin_date < nvl(ac.end_date, i.begin_date+1) GROUP BY ias.item_type, ias.error_name, ap.NAME, ac.name, ias.activity_result_code , ias.error_message , ias.error_stack order by ErrCountdesc) where rownum < 25

  39. Error Handling bde_wf_item.sql • Output Workflow data for a single workflow item to research causes. • Provides details of everything that happens to an item • Output data for: • WF_ITEMS • WF_ITEM_ATTRIBUTE_VALUES • WF_ITEM_ACTIVITY_STATUSES • WF_ITEM_ACTIVITY_STATUSES_H (Summary) • WF_NOTIFICATIONS

  40. bde_wf_item.sql Item Activity Statuses - Internal Names Select ap.name||':'||ap.version||'/'||pa.instance_label||'/'||ac.name||':'||ac.version Activity, ias.process_activityinstance_id, ias.activity_status Status, ias.activity_result_code Result, ias.assigned_user ASGND_USER, ias.notification_id NID, ntf.status "Status", to_char(ias.begin_date,'DD-MON-YY HH24:MI:SS') begin_date, to_char(ias.end_date,'DD-MON-YY HH24:MI:SS') end_date, ac.function_type, ac.function from wf_item_activity_statusesias, wf_process_activities pa, wf_activities ac, wf_activitiesap, wf_itemsi, wf_notificationsntf select ap.name||'/'||pa.instance_label Activity, ias.process_activityinstance_id, ias.activity_status Status, ias.activity_result_code Result, ias.assigned_user ASGND_USER, ias.notification_id NID, ntf.status "Status", to_char(ias.begin_date,'DD-MON HH24:MI:SS') begin_date, to_char(ias.end_date,'DD-MON HH24:MI:SS') end_date from wf_item_activity_statusesias, wf_process_activities pa, wf_activities ac, wf_activitiesap, wf_itemsi, wf_notificationsntf

  41. bde_wf_item.sql Item Activity Statuses - Internal Names where ias.item_type = '&item_type_selected' and ias.item_key = '&item_key_selected' and ias.process_activity = pa.instance_id and pa.activity_name = ac.name and pa.activity_item_type = ac.item_type and pa.process_name = ap.name and pa.process_item_type = ap.item_type and pa.process_version = ap.version and i.item_type = '&item_type_selected' and i.item_key = ias.item_key and i.begin_date >= ac.begin_date and i.begin_date < nvl(ac.end_date, i.begin_date+1) and ntf.notification_id(+) = ias.notification_id order by ias.begin_date, ias.execution_time; where ias.item_type = '&item_type_selected' and ias.item_key = '&item_key_selected' and ias.process_activity = pa.instance_id and pa.activity_name = ac.name and pa.activity_item_type = ac.item_type and pa.process_name = ap.name and pa.process_item_type = ap.item_type and pa.process_version = ap.version and i.item_type = '&item_type_selected' and i.item_key = ias.item_key and i.begin_date >= ac.begin_date and i.begin_date < nvl(ac.end_date, i.begin_date+1) and ntf.notification_id(+) = ias.notification_id order by ias.begin_date, ias.execution_time;

  42. bde_wf_item.sqlItem Activity Status History Summary select count(*) COUNT, STH.PROCESS_ACTIVITY||':'||PRA.PROCESS_VERSION PROCESS_ACTIVITY, PRA.INSTANCE_LABEL ||':'||act.version||'|'||ACT.BEGIN_DATE ACTIVITY_LABEL, STH.ACTIVITY_RESULT_CODE ACTIVITY_RESULT_CODE from wf_item_activity_statuses_h STH, wf_process_activities PRA, wf_activities ACT where STH.item_type = '&item_type_selected' and STH.item_key = '&item_key_selected' and PRA.instance_id(+) = STH.process_activity and act.item_type = pra.activity_item_type and act.name = pra.activity_name group by STH.process_activity||':'||PRA.PROCESS_version, PRA.INSTANCE_LABEL||':'||act.version||'|'||ACT.BEGIN_DATE, STH.ACTIVITY_RESULT_CODE order by 2, 3; bde_wf_item_OEOH_132846.rtf bde_wf_item_OEOL_9418151.rtf bde_wf_item_SERVICE_181572.rtf select count(*) COUNT, STH.PROCESS_ACTIVITY PROCESS_ACTIVITY, PRA.INSTANCE_LABEL ACTIVITY_LABEL, STH.ACTIVITY_RESULT_CODE ACTIVITY_RESULT_CODE from wf_item_activity_statuses_h STH, wf_process_activities PRA where STH.item_type = '&item_type_selected' and STH.item_key = '&item_key_selected' and PRA.instance_id(+) = STH.process_activity group by STH.process_activity, PRA.INSTANCE_LABEL, STH.ACTIVITY_RESULT_CODE;

  43. Error Handling • bde_wf_clean_worklist • worklistitems that are open • tied to errant activities no longer in an ERROR state. • retry them to close them out

  44. Events SELECT COUNT (*),v.text_value ,min(i.begin_date) ,max(i.begin_date) FROM wf_item_attribute_values v ,wf_items ievent counts WHERE v.item_key=i.item_key AND v.item_type = i.item_type AND v.item_type = 'WFERROR' AND v.NAME = 'EVENT_NAME' AND v.text_value IS NOT NULL GROUP BY text_value ORDER BY 4 DESC,1 DESC,text_value;

  45. Clean up Event Errors accept nodays prompt “Enter days of open history to keep:” accept type prompt “Enter WFERROR event to purge:” update wf_itemswi set wi.end_date = SYSDATE where wi.item_type =‘WFERROR’ and wi.end_date is null and trunc(wi.begin_date) < trunc(sysdate - &nodays) and exists ( select null from wf_item_attribute_valueswiav where waiv.item_key = wi.item_key and waiv.item_type = ‘WFERROR’ and waiv.name = ‘EVENT_NAME’ and waiv.text_value = ‘&type’); update wf_item_activity_statuses set end_date = SYSDATE where item_type = ‘WFERROR’ and end_date is null and item_key in ( select item_key from wf_items where item_type = ‘WFERROR’ and trunc(end_date) = trunc(SYSDATE); Karen Brownfield

  46. History Loopers • History records • Created whenever a workflow passes through an activity it has already done before • Most often bad code • Example • Workflow checks on available resource (inventory..) • When it finds what it needs it goes on • Otherwise it WAITs for some time and checks again • Sometimes as often as every 5 minutes • Like the million second Quiz!!!

  47. History Loopers • How to fix these • If you have to do it this way make sure the interval is reasonable. Do you really have to respond in 5 minutes? • Put the workflow in a deferred state instead • Another program runs against the deferred list and does the checking and tells the workflow to go on • No history created

  48. History Loopers • There is a gotcha!!!!! • When you change the workflow it creates a new version • Workflow deals with multiple versions of a process by always running the same version it started with. • So all of the millions of processes that have been generating all the history will go right on doing it • New ones will do the right thing

  49. History Loopers • There is a gotcha!!!!! • I don’t know of a supported way of fixing this problem • SQL to the rescue • Modify the end dates of the loopers Start a new workflow to deal with these Purge/delete the old ones • Change the old versions by replacing the actual activities of the old versions with new ones • Certainly can change the wait time easily

  50. There is a gotcha!!!!! • OK there is one way of at least getting rid of the history footprint – probably without Oracle getting their shorts in a twist • SQL to the rescue • Create a concurrent process that periodically deletes the history for these loopers • No ill effects of removing them • Depending on your database version you may want to be sure the high water mark is adjusted after initial run • You will be unnecessarily creating and deleting millions of rows per month

More Related