-
Task
-
Resolution: Fixed
-
L3 - Default
-
None
-
None
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
This is the controller panel for Smart Panels app
- is related to
-
-
- Closed
-