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