Purging Workflow Adhoc Roles

Recently, on one of the Production environments, users started reporting of PO Approvals (through Workflow notifications or through Oracle Purchasing) slowing down. Transaction trace revealed that the bottleneck was one of the queries querying WF_LOCAL_USER_ROLES.

Investigation revealed that workflow purge was not being executed since some time, and as a part of one of the bespoke components (workflows), adhoc roles were being created frequently. Though these were in an "expired" state, the number of rows in WF_LOCAL_USER_ROLES amounted to a whopping 15 million plus !

The solution was obviously to execute "Purge Obsolete Workflow Runtime Data" concurrent program. However, with this amount of data, it was proving to be excessively taxing on undo segments, despite giving chopping off the purgeable data using the "Age" parameter.

With the concurrent program not helpful thus, we resorted to manually executing the following API calls on SQL*Plus prompt to get rid off all the expired roles in the database (replacing XX-YYY-ZZZZ with an oldest date beyond with to purge workflow roles, and then incrementing this date, a day at a time, programmatically, until current date)

SQL> EXEC WF_PURGE.DIRECTORY(to_date('XX-YYY-ZZZZ','DD-MON-RRRR'));

Note: Though the above was executed manually to address a particular scenario, it is strongly advisable to use the seeded concurrent program to purge workflow data, as it purges most of the workflow tables storing non-persistent data.