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.
|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|
|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?
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events