L3 - Default Investigate on how to improve the performance of historic process instance query on oralce.
For the following count query
select count(distinct RES.ID_)
from
ACT_HI_PROCINST RES
LEFT JOIN (
SELECT
DISTINCT SELF.PROC_DEF_KEY_ AS KEY_
FROM
(SELECT DISTINCT DKEY.PROC_DEF_KEY_ FROM ACT_HI_PROCINST DKEY) SELF
WHERE
(
SELECT
CASE
WHEN
SELF.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.USER_ID_ = 'camunda'
AND
BITAND(A.PERMS_,4096) = 4096
AND
A.RESOURCE_TYPE_ = 6
AND
A.RESOURCE_ID_ = SELF.PROC_DEF_KEY_ )
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.USER_ID_ = 'camunda'
AND
BITAND(A.PERMS_,4096) = 4096
AND
A.RESOURCE_TYPE_ = 6
AND
A.RESOURCE_ID_ = '*')
THEN 1
ELSE
(
SELECT
CASE
WHEN
SELF.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ IN ( 'camunda-admin' )
AND
BITAND(A.PERMS_,4096) = 4096
AND
A.RESOURCE_TYPE_ = 6
AND
A.RESOURCE_ID_ = SELF.PROC_DEF_KEY_ )
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ IN ( 'camunda-admin' )
AND
BITAND(A.PERMS_,4096) = 4096
AND
A.RESOURCE_TYPE_ = 6
AND
A.RESOURCE_ID_ = '*')
THEN 1
ELSE (
SELECT
CASE
WHEN
SELF.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
BITAND(A.PERMS_,4096) = 4096
AND
A.RESOURCE_TYPE_ = 6
AND
A.RESOURCE_ID_ = SELF.PROC_DEF_KEY_ )
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
BITAND(A.PERMS_,4096) = 4096
AND
A.RESOURCE_TYPE_ = 6
AND
A.RESOURCE_ID_ = '*')
THEN 1
ELSE
null
END FROM DUAL
)
END FROM DUAL
)
END FROM DUAL
) = 1
) AUTH
ON AUTH.KEY_ = RES.PROC_DEF_KEY_
WHERE PROC_DEF_ID_ = 'f1481671-e6d3-11e6-b474-0242ac110002'
and ((RES.PROC_DEF_KEY_ is not null and AUTH.KEY_ is not null) or RES.PROC_DEF_KEY_ is null)
the following query plan is used
Plan hash value: 2369524221 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 66 | | 5557 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 66 | | | | | 2 | VIEW | VM_NWVW_1 | 26288 | 1694K| | 5557 (1)| 00:00:01 | | 3 | HASH GROUP BY | | 26288 | 6880K| 11M| 5557 (1)| 00:00:01 | |* 4 | FILTER | | | | | | | |* 5 | HASH JOIN RIGHT OUTER | | 42985 | 10M| | 3699 (1)| 00:00:01 | | 6 | VIEW | | 177 | 10443 | | 1921 (1)| 00:00:01 | |* 7 | FILTER | | | | | | | | 8 | TABLE ACCESS FULL | ACT_HI_PROCINST | 106K| 6110K| | 1777 (1)| 00:00:01 | | 9 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | |* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | ACT_IDX_AUTH_RESOURCE_ID | 6 | | | 1 (0)| 00:00:01 | |* 12 | TABLE ACCESS FULL | ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | |* 13 | TABLE ACCESS BY INDEX ROWID BATCHED | ACT_RU_AUTHORIZATION | 1 | 45 | | 3 (0)| 00:00:01 | |* 14 | INDEX RANGE SCAN | ACT_IDX_AUTH_RESOURCE_ID | 6 | | | 1 (0)| 00:00:01 | |* 15 | TABLE ACCESS FULL | ACT_RU_AUTHORIZATION | 1 | 45 | | 3 (0)| 00:00:01 | |* 16 | TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | ACT_IDX_AUTH_RESOURCE_ID | 6 | | | 1 (0)| 00:00:01 | |* 18 | TABLE ACCESS FULL | ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | | 19 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | 20 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | |* 21 | TABLE ACCESS FULL | ACT_HI_PROCINST | 26288 | 5365K| | 1777 (1)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("RES"."PROC_DEF_KEY_" IS NOT NULL AND "AUTH"."KEY_" IS NOT NULL OR "RES"."PROC_DEF_KEY_" IS NULL) 5 - access("AUTH"."KEY_"(+)="RES"."PROC_DEF_KEY_") 7 - filter( (SELECT CASE WHEN EXISTS (SELECT 0 FROM "ACT_RU_AUTHORIZATION" "A" WHERE "A"."RESOURCE_ID_"=:B1 AND "A"."RESOURCE_TYPE_"=6 AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096 AND "A"."USER_ID_"=U'camunda') THEN 1 WHEN EXISTS (SELECT 0 FROM "ACT_RU_AUTHORIZATION" "A" WHERE "A"."RESOURCE_TYPE_"=6 AND "A"."RESOURCE_ID_"=U'*' AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096 AND "A"."USER_ID_"=U'camunda') THEN 1 ELSE (SELECT CASE WHEN EXISTS (SELECT 0 FROM "ACT_RU_AUTHORIZATION" "A" WHERE "A"."RESOURCE_ID_"=:B2 AND "A"."RESOURCE_TYPE_"=6 AND "A"."GROUP_ID_"=U'camunda-admin' AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096) THEN 1 WHEN EXISTS (SELECT 0 FROM "ACT_RU_AUTHORIZATION" "A" WHERE "A"."RESOURCE_TYPE_"=6 AND "A"."RESOURCE_ID_"=U'*' AND "A"."GROUP_ID_"=U'camunda-admin' AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096) THEN 1 ELSE (SELECT CASE WHEN EXISTS (SELECT 0 FROM "ACT_RU_AUTHORIZATION" "A" WHERE "A"."RESOURCE_ID_"=:B3 AND "A"."TYPE_"=0 AND "A"."USER_ID_"=U'*' AND "A"."RESOURCE_TYPE_"=6 AND BITAND("A"."PERMS_",4096)=4096) THEN 1 WHEN EXISTS (SELECT 0 FROM "ACT_RU_AUTHORIZATION" "A" WHERE "A"."TYPE_"=0 AND "A"."USER_ID_"=U'*' AND "A"."RESOURCE_TYPE_"=6 AND "A"."RESOURCE_ID_"=U'*' AND BITAND("A"."PERMS_",4096)=4096) THEN 1 ELSE NULL END FROM "SYS"."DUAL" "DUAL") END FROM "SYS"."DUAL" "DUAL") END FROM "SYS"."DUAL" "DUAL")=1) 10 - filter("A"."RESOURCE_TYPE_"=6 AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096 AND "A"."USER_ID_"=U'camunda') 11 - access("A"."RESOURCE_ID_"=:B1) 12 - filter("A"."RESOURCE_TYPE_"=6 AND "A"."RESOURCE_ID_"=U'*' AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096 AND "A"."USER_ID_"=U'camunda') 13 - filter("A"."RESOURCE_TYPE_"=6 AND "A"."GROUP_ID_"=U'camunda-admin' AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096) 14 - access("A"."RESOURCE_ID_"=:B1) 15 - filter("A"."RESOURCE_TYPE_"=6 AND "A"."RESOURCE_ID_"=U'*' AND "A"."GROUP_ID_"=U'camunda-admin' AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096) 16 - filter("A"."TYPE_"=0 AND "A"."USER_ID_"=U'*' AND "A"."RESOURCE_TYPE_"=6 AND BITAND("A"."PERMS_",4096)=4096) 17 - access("A"."RESOURCE_ID_"=:B1) 18 - filter("A"."TYPE_"=0 AND "A"."USER_ID_"=U'*' AND "A"."RESOURCE_TYPE_"=6 AND "A"."RESOURCE_ID_"=U'*' AND BITAND("A"."PERMS_",4096)=4096) 21 - filter("RES"."PROC_DEF_ID_"=U'f1481671-e6d3-11e6-b474-0242ac110002')