5 a.m.
silver lee [https://community.jboss.org/people/lijiangt] created the discussion
"getTasksAssignedAsPotentialOwner is very slow, How to optimize it?"
To view the discussion, visit: https://community.jboss.org/message/815455#815455
--------------------------------------------------------------
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:
| | OPERATION | OBJECT_NAME | OPTIONS | COST |
| | | images/minus.gif images/gray.png (images/gray.png) |
|
| SELECT STATEMENT |
| | | 417 |
| | | | images/minus.gif images/sortascending_ena.png
(images/sortascending_ena.png) |
|
| SORT |
| | ORDER BY | 417 |
| | | | | images/minus.gif images/gray.png (images/gray.png) |
|
| FILTER |
| | | |
| %1,4% | | | | | images/minus.gif images/sigmafilter.png
(images/sigmafilter.png) |
|
| 过滤谓词 |
|
| %1,4% | | | | | | images/minus.gif images/and.png
(images/and.png) |
|
| AND |
|
| %1,4% | | | | | | | images/minus.gif images/or.png
(images/or.png) |
|
| OR |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| NAMES5_.LANGUAGE='en-UK' |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| (SELECT COUNT(NAMES10_.TASK_NAMES_ID) FROM I18NTEXT NAMES10_ WHERE
NAMES10_.TASK_NAMES_ID=:B1)=0 |
|
| %1,4% | | | | | | | images/minus.gif images/or.png
(images/or.png) |
|
| OR |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| SUBJECTS3_.LANGUAGE='en-UK' |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| (SELECT COUNT(SUBJECTS11_.TASK_SUBJECTS_ID) FROM I18NTEXT SUBJECTS11_ WHERE
SUBJECTS11_.TASK_SUBJECTS_ID=:B2)=0 |
|
| %1,4% | | | | | | | images/minus.gif images/or.png
(images/or.png) |
|
| OR |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| DESCRIPTIO4_.LANGUAGE='en-UK' |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| (SELECT COUNT(DESCRIPTIO12_.TASK_DESCRIPTIONS_ID) FROM I18NTEXT DESCRIPTIO12_ WHERE
DESCRIPTIO12_.TASK_DESCRIPTIONS_ID=:B3)=0 |
|
| | | | | | images/minus.gif images/join.png (images/join.png) |
|
| HASH JOIN |
| | OUTER | 416 |
| %1,4% | | | | | | images/minus.gif images/sigmakeys.png
(images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| TASK0_.ID=DESCRIPTIO4_.TASK_DESCRIPTIONS_ID(+) |
|
| | | | | | | images/minus.gif images/join.png (images/join.png) |
|
| HASH JOIN |
| | OUTER | 313 |
| %1,4% | | | | | | | images/minus.gif images/sigmakeys.png
(images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| TASK0_.ID=SUBJECTS3_.TASK_SUBJECTS_ID(+) |
|
| | | | | | | | images/minus.gif images/join.png
(images/join.png) |
|
| HASH JOIN |
| | OUTER | 210 |
| %1,4% | | | | | | | | images/minus.gif
images/sigmakeys.png (images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | | images/minus.gif
images/empty.png (images/empty.png) |
|
| TASK0_.ID=NAMES5_.TASK_NAMES_ID(+) |
|
| | | | | | | | | images/minus.gif images/join.png
(images/join.png) |
|
| HASH JOIN |
| | | 107 |
| %1,4% | | | | | | | | | images/minus.gif
images/sigmakeys.png (images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | | | images/minus.gif
images/empty.png (images/empty.png) |
|
| TASK0_.ID=POTENTIALO9_.TASK_ID |
|
| | | | | | | | | | images/minus.gif images/join.png
(images/join.png) |
|
| NESTED LOOPS |
| | | 61 |
| | | | | | | | | | | images/minus.gif
images/gray.png (images/gray.png) |
|
| INLIST ITERATOR |
| | | |
| | | | | | | | | | | | images/minus.gif
images/index.png (images/index.png) |
|
| INDEX |
| SYS_C0021801 | UNIQUE SCAN | 1 |
| %1,4% | | | | | | | | | | | |
images/minus.gif images/sigmakeys.png (images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | | | | | |
images/minus.gif images/or.png (images/or.png) |
|
| OR |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| ORGANIZATI6_.ID='chm.approver' |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| ORGANIZATI6_.ID='chm.operator' |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| ORGANIZATI6_.ID='chm.teamLeader' |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| ORGANIZATI6_.ID='ff8080813962106e0139628fcd28000e' |
|
| | | | | | | | | | | images/minus.gif
images/sortascending_ena.png (images/sortascending_ena.png) |
|
| SORT |
| | UNIQUE | 19 |
| | | | | | | | | | | | images/minus.gif
images/table.png (images/table.png) |
|
| TABLE ACCESS |
| PEOPLEASSIGNMENTS_POTOWNERS | BY INDEX ROWID | 19 |
| | | | | | | | | | | | |
images/minus.gif images/index.png (images/index.png) |
|
| INDEX |
| FK1EE418D2C122ED2 | RANGE SCAN | 3 |
| %1,4% | | | | | | | | | | | | |
images/minus.gif images/sigmakeys.png (images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| ORGANIZATI6_.ID=POTENTIALO9_.ENTITY_ID |
|
| %1,4% | | | | | | | | | | | | |
images/minus.gif images/sigmafilter.png (images/sigmafilter.png) |
|
| 过滤谓词 |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/or.png (images/or.png) |
|
| OR |
|
| %1,4% | | | | | | | | | | | | |
| | images/minus.gif images/empty.png (images/empty.png) |
|
| POTENTIALO9_.ENTITY_ID='chm.approver' |
|
| %1,4% | | | | | | | | | | | | |
| | images/minus.gif images/empty.png (images/empty.png) |
|
| POTENTIALO9_.ENTITY_ID='chm.operator' |
|
| %1,4% | | | | | | | | | | | | |
| | images/minus.gif images/empty.png (images/empty.png) |
|
| POTENTIALO9_.ENTITY_ID='chm.teamLeader' |
|
| %1,4% | | | | | | | | | | | | |
| | images/minus.gif images/empty.png (images/empty.png) |
|
| POTENTIALO9_.ENTITY_ID='ff8080813962106e0139628fcd28000e' |
|
| | | | | | | | | | images/minus.gif images/gray.png
(images/gray.png) |
|
| INLIST ITERATOR |
| | | |
| | | | | | | | | | | images/minus.gif
images/table.png (images/table.png) |
|
| TABLE ACCESS |
| TASK | BY INDEX ROWID | 46 |
| %1,4% | | | | | | | | | | |
images/minus.gif images/sigmafilter.png (images/sigmafilter.png) |
|
| 过滤谓词 |
|
| %1,4% | | | | | | | | | | | |
images/minus.gif images/and.png (images/and.png) |
|
| AND |
|
| %1,4% | | | | | | | | | | | | |
images/minus.gif images/empty.png (images/empty.png) |
|
| TASK0_.ARCHIVED=0 |
|
| %1,4% | | | | | | | | | | | | |
images/minus.gif images/empty.png (images/empty.png) |
|
| TASK0_.EXPIRATIONTIME IS NULL |
|
| | | | | | | | | | | | images/minus.gif
images/index.png (images/index.png) |
|
| INDEX |
| TASK_STATUS | RANGE SCAN | 4 |
| %1,4% | | | | | | | | | | | |
images/minus.gif images/sigmakeys.png (images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | | | | | |
images/minus.gif images/or.png (images/or.png) |
|
| OR |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| TASK0_.STATUS='Created' |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| TASK0_.STATUS='InProgress' |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| TASK0_.STATUS='Ready' |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| TASK0_.STATUS='Reserved' |
|
| %1,4% | | | | | | | | | | | | |
| images/minus.gif images/empty.png (images/empty.png) |
|
| TASK0_.STATUS='Suspended' |
|
| | | | | | | | | images/minus.gif images/table.png
(images/table.png) |
|
| TABLE ACCESS |
| I18NTEXT | FULL | 102 |
| %1,4% | | | | | | | | | images/minus.gif
images/sigmafilter.png (images/sigmafilter.png) |
|
| 过滤谓词 |
|
| %1,4% | | | | | | | | | | images/minus.gif
images/empty.png (images/empty.png) |
|
| NAMES5_.TASK_NAMES_ID(+) IS NOT NULL |
|
| | | | | | | | images/minus.gif images/table.png
(images/table.png) |
|
| TABLE ACCESS |
| I18NTEXT | FULL | 102 |
| %1,4% | | | | | | | | images/minus.gif
images/sigmafilter.png (images/sigmafilter.png) |
|
| 过滤谓词 |
|
| %1,4% | | | | | | | | | images/minus.gif
images/empty.png (images/empty.png) |
|
| SUBJECTS3_.TASK_SUBJECTS_ID(+) IS NOT NULL |
|
| | | | | | | images/minus.gif images/table.png (images/table.png)
|
|
| TABLE ACCESS |
| I18NTEXT | FULL | 102 |
| %1,4% | | | | | | | images/minus.gif images/sigmafilter.png
(images/sigmafilter.png) |
|
| 过滤谓词 |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| DESCRIPTIO4_.TASK_DESCRIPTIONS_ID(+) IS NOT NULL |
|
| | | | | | images/minus.gif images/sortascending_ena.png
(images/sortascending_ena.png) |
|
| SORT |
| | AGGREGATE | |
| | | | | | | images/minus.gif images/index.png (images/index.png)
|
|
| INDEX |
| FK2349686B98B62B | RANGE SCAN | 1 |
| %1,4% | | | | | | | images/minus.gif images/sigmakeys.png
(images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| NAMES10_.TASK_NAMES_ID=:B1 |
|
| | | | | | images/minus.gif images/sortascending_ena.png
(images/sortascending_ena.png) |
|
| SORT |
| | AGGREGATE | |
| | | | | | | images/minus.gif images/index.png (images/index.png)
|
|
| INDEX |
| FK2349686BB2FA6B18 | RANGE SCAN | 1 |
| %1,4% | | | | | | | images/minus.gif images/sigmakeys.png
(images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| SUBJECTS11_.TASK_SUBJECTS_ID=:B1 |
|
| | | | | | images/minus.gif images/sortascending_ena.png
(images/sortascending_ena.png) |
|
| SORT |
| | AGGREGATE | |
| | | | | | | images/minus.gif images/index.png (images/index.png)
|
|
| INDEX |
| FK2349686B69B21EE8 | RANGE SCAN | 1 |
| %1,4% | | | | | | | images/minus.gif images/sigmakeys.png
(images/sigmakeys.png) |
|
| 访问谓词 |
|
| %1,4% | | | | | | | | images/minus.gif images/empty.png
(images/empty.png) |
|
| DESCRIPTIO12_.TASK_DESCRIPTIONS_ID=:B1 |
|
|
--------------------------------------------------------------
Reply to this message by going to Community
[https://community.jboss.org/message/815455#815455]
Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&...]