Issue Summary

This is reproducible on Data Center: (yes)

When editing application properties in Jira, there are SQL queries sent that relate to the jiraissue table. The query used is not optimized for when the jiraissue table contains a high number of records and, as a result, the browser hangs for the end-user until the query completes or times out.

Steps to Reproduce

Example workflows where we have seen this performance issue stem from:

• • Going to System →General configuration → Settings. By clicking on Edit Settings
  • Going to System → System info
  • Going to System → Indexing

Expected Results

pages are quick to load and changes made within Jira

Actual Results

Nothing appears to happen from the end-user perspective

We see Stuck threads in the catalina.out file

WARNING [Catalina-utility-2] org.apache.catalina.valves.StuckThreadDetectionValve.notifyStuckThreadDetected Thread [http-nio-8080-exec-16 url: /secure/admin/EditApplicationProperties!default.jspa, /secure/admin/jira/views/editapplicationproperties.jsp;
has been active for [124,236] milliseconds (since [11/7/23 6:32 AM]) to serve the same request for [/secure/admin/EditApplicationProperties!default.jspa] and may be stuck (configured threshold for this StuckThreadDetectionValve is [120] seconds). There is/are [1] thread(s) in total that are monitored by this Valve and may be stuck.
	java.lang.Throwable
...
...
...

and we see entries like this in the atlassian-jira.log file:

http-nio-8080-exec-10 url: /secure/admin/EditApplicationProperties!default.jspa, /secure/admin/jira/views/editapplicationproperties.jsp
...
...
com.atlassian.jira.web.action.admin.EditApplicationProperties.getUnassignedIssueCount(EditApplicationProperties.java:599) 

Workaround

In some cases, your DBA will be able to add the following index:

-- oracle db
CREATE INDEX ISSUE_ASSIGNEE_NULL
ON JIRAISSUE(NVL(ASSIGNEE,'NULL'))
TABLESPACE JIRA
/

However, you will need to force a new execution plan or else Jira might continue to use a full table scan against the jiraissue table. Please refer to your specific database documentation for instruction.