0 Replies Latest reply on May 5, 2013 8:47 AM by silver lee

    getTasksAssignedAsPotentialOwner is very slow, How to optimize it?

    silver lee Newbie

      My database is oracle 11g, I use JBPM 5.4, There are 9654 records in TASK table, But getTasksAssignedAsPotentialOwner is very slow, every execute will cost more than 3 seconds.

      Anybody has idea to optimize it? thanks.

       

       

      Sql:

      select task0_.id as col_0_0_, task0_.processInstanceId as col_1_0_,

      names5_.text as col_2_0_, subjects3_.text as col_3_0_,

      descriptio4_.text as col_4_0_, task0_.status as col_5_0_,

      task0_.priority as col_6_0_, task0_.skipable as col_7_0_,

      task0_.actualOwner_id as col_8_0_, task0_.createdBy_id as col_9_0_,

      task0_.createdOn as col_10_0_, task0_.activationTime as col_11_0_,

      task0_.expirationTime as col_12_0_, task0_.processId as col_13_0_,

      task0_.processSessionId as col_14_0_ from Task task0_

      left outer join OrganizationalEntity user1_ on task0_.createdBy_id=user1_.id

      left outer join OrganizationalEntity user2_ on task0_.actualOwner_id=user2_.id

      left outer join I18NText subjects3_ on task0_.id=subjects3_.Task_Subjects_Id

      left outer join I18NText descriptio4_ on task0_.id=descriptio4_.Task_Descriptions_Id

      left outer join I18NText names5_ on task0_.id=names5_.Task_Names_Id, OrganizationalEntity organizati6_

      where task0_.archived=0 and (organizati6_.id='ff8080813962106e0139628fcd28000e' or organizati6_.id in ('chm.teamLeader', 'chm.approver', 'chm.operator')) and

      (organizati6_.id in (select potentialo9_.entity_id from PeopleAssignments_PotOwners potentialo9_

      where task0_.id=potentialo9_.task_id)) and (names5_.language='en-UK' or (select count(names10_.Task_Names_Id)

      from I18NText names10_ where task0_.id=names10_.Task_Names_Id)=0) and (subjects3_.language='en-UK' or

      (select count(subjects11_.Task_Subjects_Id) from I18NText subjects11_ where task0_.id=subjects11_.Task_Subjects_Id)=0)

      and (descriptio4_.language='en-UK' or (select count(descriptio12_.Task_Descriptions_Id) from I18NText descriptio12_

      where task0_.id=descriptio12_.Task_Descriptions_Id)=0) and (task0_.status in ('Created' , 'Ready' , 'Reserved' , 'InProgress' , 'Suspended'))

      and (task0_.expirationTime is null) order by task0_.id desc;

       

      query explain, not contain images, full version in attachment:

      OPERATION OBJECT_NAME OPTIONS COST
      SELECT STATEMENT


      417

      SORT

      ORDER BY 417


      FILTER






      过滤谓词




      AND





      OR






      NAMES5_.LANGUAGE='en-UK'






      (SELECT COUNT(NAMES10_.TASK_NAMES_ID) FROM I18NTEXT NAMES10_ WHERE NAMES10_.TASK_NAMES_ID=:B1)=0





      OR






      SUBJECTS3_.LANGUAGE='en-UK'






      (SELECT COUNT(SUBJECTS11_.TASK_SUBJECTS_ID) FROM I18NTEXT SUBJECTS11_ WHERE SUBJECTS11_.TASK_SUBJECTS_ID=:B2)=0





      OR






      DESCRIPTIO4_.LANGUAGE='en-UK'






      (SELECT COUNT(DESCRIPTIO12_.TASK_DESCRIPTIONS_ID) FROM I18NTEXT DESCRIPTIO12_ WHERE DESCRIPTIO12_.TASK_DESCRIPTIONS_ID=:B3)=0



      HASH JOIN

      OUTER 416




      访问谓词





      TASK0_.ID=DESCRIPTIO4_.TASK_DESCRIPTIONS_ID(+)




      HASH JOIN

      OUTER 313





      访问谓词






      TASK0_.ID=SUBJECTS3_.TASK_SUBJECTS_ID(+)





      HASH JOIN

      OUTER 210






      访问谓词







      TASK0_.ID=NAMES5_.TASK_NAMES_ID(+)






      HASH JOIN


      107







      访问谓词








      TASK0_.ID=POTENTIALO9_.TASK_ID







      NESTED LOOPS


      61








      INLIST ITERATOR












      INDEX
      SYS_C0021801 UNIQUE SCAN 1










      访问谓词











      OR












      ORGANIZATI6_.ID='chm.approver'












      ORGANIZATI6_.ID='chm.operator'












      ORGANIZATI6_.ID='chm.teamLeader'












      ORGANIZATI6_.ID='ff8080813962106e0139628fcd28000e'








      SORT

      UNIQUE 19









      TABLE ACCESS
      PEOPLEASSIGNMENTS_POTOWNERS BY INDEX ROWID 19










      INDEX
      FK1EE418D2C122ED2 RANGE SCAN 3











      访问谓词












      ORGANIZATI6_.ID=POTENTIALO9_.ENTITY_ID











      过滤谓词












      OR













      POTENTIALO9_.ENTITY_ID='chm.approver'













      POTENTIALO9_.ENTITY_ID='chm.operator'













      POTENTIALO9_.ENTITY_ID='chm.teamLeader'













      POTENTIALO9_.ENTITY_ID='ff8080813962106e0139628fcd28000e'







      INLIST ITERATOR











      TABLE ACCESS
      TASK BY INDEX ROWID 46









      过滤谓词










      AND











      TASK0_.ARCHIVED=0











      TASK0_.EXPIRATIONTIME IS NULL









      INDEX
      TASK_STATUS RANGE SCAN 4










      访问谓词











      OR












      TASK0_.STATUS='Created'












      TASK0_.STATUS='InProgress'












      TASK0_.STATUS='Ready'












      TASK0_.STATUS='Reserved'












      TASK0_.STATUS='Suspended'






      TABLE ACCESS
      I18NTEXT FULL 102







      过滤谓词








      NAMES5_.TASK_NAMES_ID(+) IS NOT NULL





      TABLE ACCESS
      I18NTEXT FULL 102






      过滤谓词







      SUBJECTS3_.TASK_SUBJECTS_ID(+) IS NOT NULL




      TABLE ACCESS
      I18NTEXT FULL 102





      过滤谓词






      DESCRIPTIO4_.TASK_DESCRIPTIONS_ID(+) IS NOT NULL



      SORT

      AGGREGATE




      INDEX
      FK2349686B98B62B RANGE SCAN 1





      访问谓词






      NAMES10_.TASK_NAMES_ID=:B1



      SORT

      AGGREGATE




      INDEX
      FK2349686BB2FA6B18 RANGE SCAN 1





      访问谓词






      SUBJECTS11_.TASK_SUBJECTS_ID=:B1



      SORT

      AGGREGATE




      INDEX
      FK2349686B69B21EE8 RANGE SCAN 1





      访问谓词






      DESCRIPTIO12_.TASK_DESCRIPTIONS_ID=:B1