Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-8447

Improve performance of query to select process instances to delete during historic cleanup

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 7.9.0, 7.8.2, 7.7.7, 7.9.0-alpha2
    • Component/s: engine
    • Labels:
      None

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              roman.smirnov Smirnov Roman
              Reporter:
              roman.smirnov Smirnov Roman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: