Sprint names and Sprint IDs are not created sequentially on JIRA when using Oracle DB
プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。
A JIRA admin or user may notice the Sprint name suggestion (explained on How JIRA suggests sprint names) occasionally skips or repeats numbers.
On JIRA's database, AO_60DB71_SPRINT table has a lot of gaps in the ID field.
This article will go through one of the potential causes of this behaviour exclusively for Oracle database.
You may query AO_60DB71_SPRINT table and order it by the ID field. If you're affected by this you may see consistent gaps in the ID value - these will be skipping multiple values and rarely a single ID.
Admins/users may delete Sprints on their daily tasks so it is common to see individual IDs missing. You should be able to track these on JIRA's audit log.
Oracle's sequence which is used to determine the next ID employs the use of CACHE and it may get flushed inadvertently. This will cause Oracle to lose the sequences that were in CACHE leading to the skips.
On this analysis I interacted with sequence named AO_60DB71_SPRINT_ID_SEQ associated to the ID field on AO_60DB71_SPRINT table.
I'd like to reiterate that this solution only applies to Oracle database. If you're dealing with similar symptoms when using other database product it's most likely a different problem.
Direct database manipulation is ultimately not recommended. Feel free to engage with Oracle's support and/or review this with them before taking action.
If you're comfortable with the analysis done here and would like to proceed with the steps, make sure this is ran with the application down and with a safety backup in hand. It may affect system performance but it should not be noticeable since this specific sequence has relatively low volume.
1. Bring down JIRA.
2. Run the statement below:
alter sequence AO_60DB71_SPRINT_ID_SEQ NOCACHE;
3. Restart JIRA.
4. Attempt to reproduce the problem by creating multiple Sprints in quick succession.
- If you're not able to reproduce the problem I suggest leaving the sequence with NOCACHE for a week or so to monitor for reoccurences.
When/if you'd like to rollback, the command is as follows:
alter sequence AO_60DB71_SPRINT_ID_SEQ CACHE 20;
More information on JIRA vs Oracle's CACHE/NOCACHE.
Oracle's default (and also the same value this sequence has by default) is CACHE 20.
From Jira's perspective, even with CACHE it will still need to fetch the "AO_60DB71_SPRINT_ID_SEQ.nextval" value from Oracle. The difference is that having a set value for CACHE specifies how many values of the sequence the database preallocates and keeps in database memory for faster access.
When you have NOCACHE, this means Oracle will have to fetch the nextval from database instead of memory every time Jira requests it.
So when does Jira request a new ID from AO_60DB71_SPRINT_ID_SEQ?
- This only happens when you create a new Sprint.
How much of a performance impact could the NOCACHE have?
While we do not have load testing performed under this scenario, we do know that:
- Jira requests data from the database several times for simply displaying an issue.
- The number of Sprints being created is negligible compared to the number of fetches done by Oracle for other purposes - it should not make a difference in the calls to the database volume-wise.
- Oracle will take a couple extra milliseconds to fetch the NEXTVAL a single time for each created Sprint.
Our understanding is that since Sprint creation is performed a relatively low number of times coupled with Oracle's very fast fetch, the alter table on the AO_60DB71_SPRINT_ID_SEQ table should not have any impact on performance even if under the most unusually high-Sprint-creation count days.