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

Historic Process Instance Query with authorization checks is slow

    Details

      Description

      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')
      

        Activity

        There are no comments yet on this issue.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: