Issue Summary

The /status endpoint can return slowly in enterprise scale Oracle 19c deployments since Jira 9.6.0 / 9.4.8 LTS.

In customer prod environment and in our synthetic tests, Oracle 19c's optimizer tends to choose TABLE ACCESS FULL in Jira 9.4.8, while it picks INDEX FAST FULL SCAN in 8.20.

This seems to be because a WHERE clause was introduced in 9.4.8 to filter on ("I"."ARCHIVED"='N' OR "I"."ARCHIVED" IS NULL)

Examining the EXPLAIN PLAN output for the query with and without this WHERE cause shows a drastic change - a query cost of 5763 vs 1 in our synthetic test

The impact is that the query runs several orders of magnitude slower, resulting in slow /status return and decreased database performance once every com.atlassian.jira.status.index.check.expiration.seconds seconds

Steps to Reproduce

1. Jira 9.4.11
2. Oracle 19c
3. 60k issues, archive 10k of them (this is minuscule compared to customer environment with 15M+ issues but reflects the not-archived to archived ratio of 1:5)

Check the optimizer

EXPLAIN PLAN FOR SELECT COUNT(I.ID) FROM jiraissue I WHERE (I.ARCHIVED =  'N' ) OR (I.ARCHIVED IS NULL );
SELECT * FROM table(dbms_xplan.display);

If you have sufficiently poor performance on the DB then you may also check /status endpoint performance, to exclude memoization check once every 60 seconds.

Expected Results

A full table scan is avoided.

>= 9.4.8's /status returns in  <= speed of 8.20.

Actual Results

--------------------------------------------------------------------------------
| Id  | Operation	   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	       |     1 |     2 |  5763	 (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	       |     1 |     2 |	    |	       |
|*  2 |   TABLE ACCESS FULL| JIRAISSUE | 42492 | 84984 |  5763	 (1)| 00:00:01 |
--------------------------------------------------------------------------------

9.4.8's /status performance is significantly slower than 8.20's /status (> 1 order of magnitude)

Workaround

Add the following composite index

CREATE INDEX idx_jiraissue_archived_id ON jiraissue (ARCHIVED, ID)

Which should result in a similar EXPLAIN PLAN output:

--------------------------------------------------------------------------------

| Id  | Operation	      | Name			  | Rows          | Bytes | Cost (%CPU)| Time |	|
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      | 			  |	1 |	2 |    4  | 1   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       | 			  |	1 |	2 |       |	   |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_JIRAISSUE_ID_ARCHIVED | 42492 | 84984 |    4  | 1   (3)| 00:00:01 |

--------------------------------------------------------------------------------

This solution might not be the most efficient one, as the index will take space on disk, and will need to be updated on write (although this index could replace the existing "ARCHIVED" index, there may still be some additional impact here). Thus we would be interested in your feedback