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

    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

        Issue Links

          Activity

          roman.smirnov Smirnov Roman created issue -
          roman.smirnov Smirnov Roman made changes -
          Field Original Value New Value
          Link This issue is related to CAM-8160 [ CAM-8160 ]
          roman.smirnov Smirnov Roman made changes -
          Rank Ranked higher
          roman.smirnov Smirnov Roman made changes -
          Assignee Svetlana Dorokhova [ svetlana.dorokhova ]
          roman.smirnov Smirnov Roman made changes -
          Rank Ranked higher
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Link This issue is related to SUPPORT-4010 [ SUPPORT-4010 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          roman.smirnov Smirnov Roman made changes -
          Summary Improve history cleanup performance Improve performance of query to select process instance to delete during historic cleanup
          roman.smirnov Smirnov Roman made changes -
          Summary Improve performance of query to select process instance to delete during historic cleanup Improve performance of query to select process instances to delete during historic cleanup
          roman.smirnov Smirnov Roman made changes -
          Description The following query is used to select the process instances to delete on Oracle

          {code}
          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
          {code}

          To execute the query, the Oracle optimizer performs "Table Access (Full)" (i.e. full table scan) to get the process instances from {{ACT_HI_PROCINST}}...
          roman.smirnov Smirnov Roman made changes -
          Description The following query is used to select the process instances to delete on Oracle

          {code}
          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
          {code}

          To execute the query, the Oracle optimizer performs "Table Access (Full)" (i.e. full table scan) to get the process instances from {{ACT_HI_PROCINST}}...
          The following query is used to select the process instances to delete on Oracle

          {code}
          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
          {code}

          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)
          - 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
          roman.smirnov Smirnov Roman made changes -
          Description The following query is used to select the process instances to delete on Oracle

          {code}
          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
          {code}

          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)
          - 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
          The following query is used to select the process instances to delete on Oracle

          {code}
          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
          {code}

          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)
          - 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
          roman.smirnov Smirnov Roman made changes -
          Description The following query is used to select the process instances to delete on Oracle

          {code}
          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
          {code}

          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)
          - 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
          The following query is used to select the process instances to delete on Oracle

          {code}
          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
          {code}

          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
          roman.smirnov Smirnov Roman made changes -
          Link This issue is related to SUPPORT-3541 [ SUPPORT-3541 ]
          roman.smirnov Smirnov Roman made changes -
          Link This issue is depended on by SUPPORT-4009 [ SUPPORT-4009 ]
          roman.smirnov Smirnov Roman made changes -
          Link This issue is depended on by SUPPORT-4009 [ SUPPORT-4009 ]
          roman.smirnov Smirnov Roman made changes -
          Link This issue is related to SUPPORT-4030 [ SUPPORT-4030 ]
          roman.smirnov Smirnov Roman made changes -
          Link This issue is related to SUPPORT-4011 [ SUPPORT-4011 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Fix Version/s 7.7.x [ 14699 ]
          Fix Version/s 7.8.x [ 14900 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Fix Version/s 7.8.2 [ 15192 ]
          Fix Version/s 7.7.7 [ 15193 ]
          Fix Version/s 7.7.x [ 14699 ]
          Fix Version/s 7.8.x [ 14900 ]
          svetlana.dorokhova Svetlana Dorokhova made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Original Estimate 0 minutes [ 0 ]
          Remaining Estimate 0 minutes [ 0 ]
          Assignee Svetlana Dorokhova [ svetlana.dorokhova ] Smirnov Roman [ roman.smirnov ]
          Resolution Fixed [ 1 ]
          roman.smirnov Smirnov Roman made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          fabian.hinsenkamp Fabian Hinsenkamp made changes -
          Affects Version/s 7.9.0-alpha2 [ 15291 ]
          roman.smirnov Smirnov Roman made changes -
          Affects Version/s 7.9.0-alpha2 [ 15291 ]
          Fix Version/s 7.9.0-alpha2 [ 15291 ]
          thorben.lindhauer Thorben Lindhauer made changes -
          Workflow camunda BPM [ 44581 ] Backup_camunda BPM [ 63355 ]

            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:

                Development