BPM_ERRORS table filling up impacting database performance

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The DBA noticed that the BPM_ERRORS table is doing a FULL TABLE SCAN every time it runs, which impacts the database performance.

The table is currently very large in size. Every time that table is queried, Oracle will have to flush all its buffer data to disk in order to load BPM_ERRORS data for read. Can the records from this table be deleted or truncated?

Environment

Release: Any

Resolution

We recommend to run Delete Process Instances job on regular basis:

1. Take a DB backup before running it.
2. Run the Delete Process Instance Job. If there are many records to delete, the job will take long time.
3. Make sure that you enter date parameters (eg. run month by month). Aim to have not more than 5000 removed at each run
4. Delete process instance job removes the respective entries from the following tables:

. CAL_ACTION_ITEM_ASSIGNEES   
. BPM_RUN_PROCESSES   
. CAL_ACTION_ITEMS   
. BPM_RUN_ASSIGNEE_NOTES   
. BPM_RUN_REPLACE_ASSIGNEES   
. BPM_RUN_ASSIGNEES   
. BPM_RUN_STEP_TRANSITIONS   
. BPM_RUN_STEP_COND_RESULTS   
. BPM_RUN_STEP_COND_OBJECTS   
. BPM_RUN_STEP_ACTION_RESULTS     
. BPM_RUN_OBJECTS   
. BPM_RUN_STEPS   
. ESC_RUN_USERS   
. ESC_RUN_LEVELS   
. ESC_RUN_UNREG_OBJECTS   
. BPM_RUN_OBJECTS   
. BPM_RUN_THREADS   
. BPM_ERRORS   
. BPM_ERROR_ARGUMENTS   

Additional Information

Note:

• The Delete Process Instance job will delete Aborted or Completed processes. If you have many processes in Error/Failed, those have to be aborted first before deleting them.
• If a specific process throws too many errors, this is something that has to be worked on with the process developer.