Hello,
From time to time we encounter a problem with performance of some of our jira functionalities. We have to wait like 5 secs to show an issue descritpion in project for example. We have ran a sql trace log and found out a problem with execution of following queries:
2018-07-30 10:13:25,677 ajp-nio-8010-exec-567 174564 613x19069626x1 7w21sv /secure/CommentAssignIssue!default.jspa 7243ms "SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING FROM changegroup CG INNER JOIN changeitem CI ON CG.ID = CI.groupid WHERE CG.issueid='6452433' AND CI.FIELD='assignee' ORDER BY CG.CREATED ASC, CI.ID ASC"
2018-07-30 10:13:25,677 ajp-nio-8010-exec-567 174564 613x19069626x1 7w21sv /secure/CommentAssignIssue!default.jspa 7243ms Connection returned. borrowed : 4
We forwarded a problem to our database engineers and they investigated two diffrent execution plans of these queries.
Execution Plan 1
IdOperationNameRowsBytesCost (%CPU)Time
| 0 | SELECT STATEMENT | | | | 60 (100) | |
| 1 | SORT ORDER BY | | 15 | 6360 | 60 (2) | 00:00:01 |
| 2 | HASH JOIN | | 15 | 6360 | 59 (0) | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | CHANGEGROUP | 15 | 405 | 15 (0) | 00:00:01 |
| 4 | INDEX RANGE SCAN | CHGGROUP_ISSUE | 15 | | 3 (0) | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | CHANGEITEM | 1 | 397 | 3 (0) | 00:00:01 |
| 6 | INDEX RANGE SCAN | CHGITEM_FIELD | 2 | | 2 (0) | 00:00:01 |
Execution Plan 2
IdOperationNameRowsBytesCost (%CPU)Time
| 0 | SELECT STATEMENT | | | | 54 (100) | |
| 1 | SORT ORDER BY | | 13 | 5356 | 54 (2) | 00:00:01 |
| 2 | NESTED LOOPS | | 13 | 5356 | 53 (0) | 00:00:01 |
| 3 | NESTED LOOPS | | 39 | 5356 | 53 (0) | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | CHANGEGROUP | 13 | 351 | 14 (0) | 00:00:01 |
| 5 | INDEX RANGE SCAN | CHGGROUP_ISSUE | 13 | | 3 (0) | 00:00:01 |
| 6 | INDEX RANGE SCAN | CHGITEM_CHGGRP | 3 | | 2 (0) | 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | CHANGEITEM | 1 | 385 | 3 (0) | 00:00:01 |
Average plan times:
plan 1 : 245.81 ms
plan 2 : 11.03 ms
Sometimes jira switches to the first plan. Only instance restart helps. Problem occurs every few weeks. Maybe it is connected with some session parameters which jira sets, so they force to use 1st execution plan. Any ideas how to fix this problem please?
Greetings,
Paweł
