Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default

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.

Photo by Jakub Gorajek on Unsplash

We introduced this parameter in Oracle 11.2.0.3 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:

• 11.2.0.3: _cursor_obsolete_threshold=100
• 11.2.0.4: _cursor_obsolete_threshold=1024
• 12.1.0.1: _cursor_obsolete_threshold=1024
• 12.1.0.2: _cursor_obsolete_threshold=1024
• 12.2.0.1: _cursor_obsolete_threshold=8192
• 18c: _cursor_obsolete_threshold=8192
• 19c: _cursor_obsolete_threshold=8192

Ups … somebody in Oracle 12.2.0.1 has increased it a lot …

Oracle 12.2.0.1 and higher: Set _cursor_obsolete_threshold to old default

Somebody must have increased the default a lot in Oracle 12.2.0.1 – 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.

Additional Information

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

–Mike