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.

Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default

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
  • 18.3.0: _cursor_obsolete_threshold=8192

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.

More information

You will find more information in this documents:

–Mike

10 thoughts on “Oracle 12.2 and higher: Set _cursor_obsolete_threshold to old default

  1. 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?

    Thanks

    • Ross,

      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.

      Cheers,
      Mike

  2. 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?

    • Wilhelm,

      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.

      Cheers,
      Mike

  3. 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)

    • Pavel,

      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.

      Thanks,
      Mike

      • Mike,

        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?

        Thanks
        Wilhelm

        • Wilhelm.

          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.

          Cheers,
          Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.