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 18.104.22.168 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 12.2 has increased it a lot …
Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default
Somebody must have increased the default a lot in Oracle 12.2 – 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='MOS Note: 2431353.1 - 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.
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