PPM Support Tip: When Timeout not triggered on time, check database performance
New KCS Article that was published:
It was discovered that Timeouts were not triggering at the time when they were expected to be processed.
The Service "Workflow Timeout Reaper" was seen to have an issue:
"The current service run is executing its expected run time. The scheudled run that has been delayed due to this event will resume as soon as the current service run finishes"
If the "Workflow Timeout Reaper" Service is having a problem, please following blow steps to narrow down the issue:
1) Run the SQL and save the result as "result1" in a Spreadsheet for easy reading:
SELECT INSTANCE_SOURCE_TYPE_CODE, INSTANCE_SOURCE_SET_ID, ENTITY_LAST_UPDATE_DATE, WORKFLOW_STEP_ID, STEP_TRANSACTION_ID, STEP_TYPE_CODE, APPROVALS_REQUIRED_CODE
FROM KWFL_PENDING_STEP_TIMEOUTS_V ORDER BY instance_source_set_id, workflow_step_id, step_transaction_id;
2) Stop Project and Porfolio Management (PPM) and restart the instance, wait 3 minute (the time the Service is scheduled to run), then run above SQL again, and save to "result2" Spreadsheet for easy reading.
3) Compare the result with the previous one to see if the records are changed to confirm whether the "Workflow Timeout Reaper" Service is working when it is restarted or if it is a performance issue.
For example, in one particular case, rows were being processed. It was discovered the Oracle Optimizer Mode was not set to ALL_ROWS. The Database Administrator (DBA) updated this. Things processed as expected after doing so.
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”