Sometimes development groups change default parameter settings because it fits well for a specific feature. This has happened with _cursor_obsolete_threshold in Oracle Database 12.2. But there’s now an official recommendation for Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default.
What is _cursor_obsolete_threshold?
First of all, this is obviously an underscore parameter. And usually the policy is: Don’t change underscore parameters unless either you clearly understand what you are changing. Or unless Oracle Support (or another Oracle authority) advises you to do.
We introduced this parameter in Oracle 220.127.116.11 for an issue of growing child cursors. An enhancement request was filed to address this issue, known as bug 10187168. When the child cursors grows beyond a certain count, lets say 20 or 100, it obsoletes the parent cursors.
We activated it by default, and the parameter got adjusted in later releases:
Ups … somebody in Oracle 18.104.22.168 has increased it a lot …
Oracle 22.214.171.124 and higher: Set _cursor_obsolete_threshold to old default
Somebody must have increased the default a lot in Oracle 126.96.36.199 – to accommodate thousands of PDBs. Well, but you may not operate thousands of PDBs within one container databases – yet. And unfortunately this drastic change of an underscore’s default leads to drastic performance issues in some cases.
Oracle Support described this very well in MOS Note: 2431353.1 – High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance. The performance issues you may see are typically indicated by “cursor: mutex X” or “cursor: mutex S” or “cursor: pin S wait on X“ in AWR reports and wait diagrams. But other indications are described in the MOS note as well.
A clear recommendation for you:
- Set _cursor_obsolete_threshold back to the old default unless you are operating thousands of PDBs.
ALTER SYSTEM SET "_cursor_obsolete_threshold"=1024 COMMENT='
- Sep 11, 2018 by Mike' SCOPE=SPFILE;
From the MOS Note:
The default value of _cursor_obsolete_threshold is increased heavily (8192 from 1024) from 12.2 onwards in order to support 4096 PDBs which was only 252 PDBs till 12.1. This parameter value is the maximum limit for obsoleting the parent cursors in a multitenant environment and cannot be increased beyond 8192.
Having said, this is NOT applicable for non-CDB environment and hence for those databases, this parameter should be set to 12.1 default value manually i.e. 1024. The default value of 1024 holds good for non-CDB environment and the same parameter can be adjusted case-to-case basis should there be a problem.
An (unpublished) Bug 28535562 – DEFAULT VALUE OF _CURSOR_OBSOLETE_THRESHOLD IS TOO HIGH was filed a while ago but hasn’t fixed yet. Even with the current release updates you will face this high value and have to reset it manually in your SPFILE. Please open an SR and reference this bug in case you have further questions.
Further Links and Information
You will find more information in this documents:
- MOS Note: 296377.1 – Troubleshooting: High Version Count Issues
- MOS Note: 2431353.1 – High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance
The MOS article doesn’t really give a guideline on adjusting the value in a CDB environment. If we are running anywhere from a single PDB to maybe 10 PDBs, should we leave it at default or decrease it down to the 12.1 value of 1024?
you are right. I think the authors didn’t want to give a blank recommendation for CDB/Multitenant environments.
My take here:
Set it to 1024 as well. The value really got bumped up only for tests with thousands of PDBs. The largest customer deployment I know has limited the PDB count to 50 per CDB for several reasons. And there this value (the old default) is good enough.
This is one of the reasons why Oracle DBs are such complicated things.
– one is changing defaults (for situations which are not “default”),
– then MOS says “change the value back to something normal”.
– and then I am asked “why do you touch Oracle-internal parameters”?
Wouldn’t it be nice if Development (or “default-value-persons” or whoever is responsible) are starting their brains _before_ they set such values?
I almost agree by 100%. We were “surprised” as well by this change.
If you are at DOAG, I can tell you a bit more about it.
Please understand that I can’t explain more details on the blog.
But I think the MOS Note is unusually explicit on where this change came from.
Thanks for nice hint 🙂
It would be nice if the Metalink note clarified either of these two:
– the slowness caused by high underscore parameter is fixed (a patch, and included in the next PSU)
– or, the underscore parameter is changed back to the lower value (in the next PSU)
I see your point. But honestly I doubt that either one will happen. At least as long as customers don’t insist in getting a patch or change in the future release for it.
And I know what that means: Setting the parameter correctly in the future for every release. That’s why only a good number of SRs insisting on a code change could help.
I see your point 🙂 . But I am tired of doing such SRs and arguing with support, development, and escalation level management I +II, and investing months of discussion. If people inside Oracle (like you) can not influence them to make real live values – who else can get them out of their ivory tower?
I know – I can influence things and I do whenever it is possible. In most database areas of development people listen and are open – and maybe sometimes not happily amused.
But unfortunately, as in every other larger company, there are areas or groups thining they know better (always!) – or groups who have simply no bandwidth to change anything.
That’s the reason why SRs matter. It is nice and useful sometimes when one of us can give you a good recommendation. But as soon as people stop logging SRs and forcing Support to file bugs, those groups who don’t listen in the “ivory tower” think they are doing right. I understand your point – but then we won’t see any progress as in some cases the only currency of value are SRs and Bugs.
Maybe you shouldn’t publish the recommendation to use this underscore parameter. We would all discover a ‘slowness’ in the database, and raise SRs, potentially leading to a fix.
Now we know about this parameter we just set it and forget it, adding it to a ‘magic list’ of underscore parameters necessary to keep the systems alive.
the note is out on MOS. And my blog gets read by a small number of people compared to the customers with Oracle databases 😉
Hence, I think the impact of my posting is very little. And actually I favor if people avoid known issues instead of spending times with SRs.
I fully see your point and usually I fully agree. I hope that enough people open SRs to request this parameter to be changed back to the old default for 19c – but without running into a known performance issue.
Thanks for your comment!
Would you know if this recommendation (of changing _cursor_obsolete_threshold to 1024) is applicable to Cloud/OCC environments too?
it is applicable to all environments unless you have hundreds of PDBs.
Does this remain an issue and therefore need to be adjusted downward for 19.12 non-CDB customers as well?
This needs to be remedied in all releases from 188.8.131.52 until 21c including all and every RU/RUR.
So please put it in your SPFILE and leave it there.