Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Long times of query executions to database

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

0SELECT STATEMENT   60 (100) 
1   SORT ORDER BY 15636060 (2)00:00:01
2     HASH JOIN 15636059 (0)00:00:01
3       TABLE ACCESS BY INDEX ROWID BATCHEDCHANGEGROUP1540515 (0)00:00:01
4         INDEX RANGE SCANCHGGROUP_ISSUE15 3 (0)00:00:01
5       TABLE ACCESS BY INDEX ROWID BATCHEDCHANGEITEM13973 (0)00:00:01
6         INDEX RANGE SCANCHGITEM_FIELD2 2 (0)00:00:01

 

 

Execution Plan 2

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT   54 (100) 
1   SORT ORDER BY 13535654 (2)00:00:01
2     NESTED LOOPS 13535653 (0)00:00:01
3       NESTED LOOPS 39535653 (0)00:00:01
4         TABLE ACCESS BY INDEX ROWID BATCHEDCHANGEGROUP1335114 (0)00:00:01
5           INDEX RANGE SCANCHGGROUP_ISSUE13 3 (0)00:00:01
6         INDEX RANGE SCANCHGITEM_CHGGRP3 2 (0)00:00:01
7       TABLE ACCESS BY INDEX ROWIDCHANGEITEM13853 (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ł 

1 answer

0 votes

I hope you fixed via upgrade. 

And tuned the DBMS

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

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

Events near you