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')
This is the controller panel for Smart Panels app
[CAM-7445] Historic Process Instance Query with authorization checks is slow
| Link | New: This issue depends on SUPPORT-3046 [ SUPPORT-3046 ] |
| Description |
Original:
Investigate on how to improve the performance of historic process instance query on oralce. For the following count query {code} 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' {code} the following query plan is used {code} Plan hash value: 825149101 ---------------------------------------------------------------------------------------------------------------------------------- | 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 | HASH JOIN RIGHT OUTER | | 42985 | 10M| | 3699 (1)| 00:00:01 | | 5 | VIEW | | 177 | 10443 | | 1921 (1)| 00:00:01 | |* 6 | FILTER | | | | | | | | 7 | TABLE ACCESS FULL | ACT_HI_PROCINST | 106K| 6110K| | 1777 (1)| 00:00:01 | | 8 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID BATCHED | ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | ACT_IDX_AUTH_RESOURCE_ID | 6 | | | 1 (0)| 00:00:01 | |* 11 | TABLE ACCESS FULL | ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | |* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | ACT_RU_AUTHORIZATION | 1 | 45 | | 3 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | ACT_IDX_AUTH_RESOURCE_ID | 6 | | | 1 (0)| 00:00:01 | |* 14 | TABLE ACCESS FULL | ACT_RU_AUTHORIZATION | 1 | 45 | | 3 (0)| 00:00:01 | |* 15 | TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | ACT_IDX_AUTH_RESOURCE_ID | 6 | | | 1 (0)| 00:00:01 | |* 17 | TABLE ACCESS FULL | ACT_RU_AUTHORIZATION | 1 | 21 | | 3 (0)| 00:00:01 | | 18 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | 19 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | |* 20 | TABLE ACCESS FULL | ACT_HI_PROCINST | 26288 | 5365K| | 1777 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("AUTH"."KEY_"(+)="RES"."PROC_DEF_KEY_") 6 - 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) 9 - filter("A"."RESOURCE_TYPE_"=6 AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096 AND "A"."USER_ID_"=U'camunda') 10 - access("A"."RESOURCE_ID_"=:B1) 11 - 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') 12 - filter("A"."RESOURCE_TYPE_"=6 AND "A"."GROUP_ID_"=U'camunda-admin' AND "A"."TYPE_"=1 AND BITAND("A"."PERMS_",4096)=4096) 13 - access("A"."RESOURCE_ID_"=:B1) 14 - 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) 15 - filter("A"."TYPE_"=0 AND "A"."USER_ID_"=U'*' AND "A"."RESOURCE_TYPE_"=6 AND BITAND("A"."PERMS_",4096)=4096) 16 - access("A"."RESOURCE_ID_"=:B1) 17 - 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) 20 - filter("RES"."PROC_DEF_ID_"=U'f1481671-e6d3-11e6-b474-0242ac110002') {code} |
New:
Investigate on how to improve the performance of historic process instance query on oralce. For the following count query {code} 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) {code} the following query plan is used {code} 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') {code} |
| Description |
Original:
Investigate on how to improve the performance of historic process instance query on oralce. For the following count query {code} 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) {code} the following query plan is used {code} 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') {code} |
New:
Investigate on how to improve the performance of historic process instance query on oralce. For the following count query {code} 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) {code} the following query plan is used {code} 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') {code} |
| Fix Version/s | New: 7.7.0 [ 14607 ] | |
| Fix Version/s | New: 7.6.3 [ 14703 ] | |
| Fix Version/s | Original: 7.6.x [ 14597 ] | |
| Fix Version/s | Original: 7.7.x [ 14699 ] |
| Assignee | New: Svetlana Dorokhova [ svetlana.dorokhova ] |
| Status | Original: Open [ 1 ] | New: In Progress [ 3 ] |
| Assignee | Original: Svetlana Dorokhova [ svetlana.dorokhova ] | New: Roman Smirnov [ roman.smirnov ] |
| Resolution | New: Fixed [ 1 ] | |
| Status | Original: In Progress [ 3 ] | New: Resolved [ 5 ] |
| Remaining Estimate | New: 0 minutes [ 0 ] | |
| Original Estimate | New: 0 minutes [ 0 ] |
| Assignee | Original: Roman Smirnov [ roman.smirnov ] | New: Svetlana Dorokhova [ svetlana.dorokhova ] |
| Resolution | Original: Fixed [ 1 ] | |
| Status | Original: Resolved [ 5 ] | New: Reopened [ 4 ] |
| Status | Original: Reopened [ 4 ] | New: In Progress [ 3 ] |