[CAM-8447] Improve performance of query to select process instances to delete during historic cleanup Created: 16/Nov/17  Updated: 02/Mar/18  Resolved: 15/Feb/18

Status: Closed
Project: camunda BPM
Component/s: engine
Affects Version/s: None
Fix Version/s: 7.9.0, 7.8.2, 7.7.7, 7.9.0-alpha2

Type: Task Priority: L3 - Default
Reporter: Smirnov Roman Assignee: Smirnov Roman
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: 0 minutes
Time Spent: Not Specified
Original Estimate: 0 minutes

Issue Links:
Related
is related to CAM-8160 Benchmark performance of History Cleanup Closed

 Description   

The following query is used to select the process instances to delete on Oracle

SELECT
	*
FROM
	(
		SELECT
			a.*,
			ROWNUM rnum
		FROM
			(
				SELECT
					DISTINCT RES.*
				FROM
					(
						SELECT
							pi.PROC_INST_ID_,
							pi.END_TIME_
						FROM
							ACT_HI_PROCINST PI,
							ACT_RE_PROCDEF pd
						WHERE
							pi.PROC_DEF_ID_ = pd.ID_
							AND NOT pi.END_TIME_ IS NULL
							AND NOT pd.HISTORY_TTL_ IS NULL
							AND pi.END_TIME_ + pd.HISTORY_TTL_ <= ${currentTimestamp}
					) RES
				ORDER BY
					RES.END_TIME_ ASC
			) a
		WHERE
			ROWNUM < 100
	)
WHERE
	rnum >= 1

To execute the query, in the most of case (see [1]) the Oracle optimizer performs a "Table Access (Full)" (i.e. full table scan) to get the process instances from ACT_HI_PROCINST and cannot use the index ACT_IDX_HI_PRO_INST_END to perform a "Index (Range Scan)".

Possible Solutions:

  • Get rid of the sub selects, since pagination is not used here.
  • Substract HISTORY_TTL_ from current timestamp, i.e. pi.END_TIME_ <= ${currentTimestamp} - pd.HISTORY_TTL_
  • create a composite index column for the columns END_TIME_, PROC_DEF_ID_, PROC_INST_ID_ (the result - the value endtime and process instance id - can be retrieved from the index, so that a table access is not necessary anymore)
  • add option to add DB hints
  • First fetch all process definitions where a history time to live is used, then select for each process definition the instances to cleanup

[1]: Of course it depends on the produces statistics of Oracle


Generated at Thu Aug 22 11:17:54 CEST 2019 using JIRA 6.4.6#64021-sha1:33e5b454af4594f54560ac233c30a6e00459507e.