Cursor obsolete threshold

From Robs_Wiki
Jump to: navigation, search

About parameter _cursor_obsolete_threshold

Oracle 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. The parameter has the following default in the different Oracle versions:

  • 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

Somebody must have increased the default a lot in Oracle 12.2 – to accommodate thousands of PDBs. In most database landscapes this is not the case. And unfortunately this drastic change of an underscore’s default leads to drastic performance issues in some cases.

Recommended Value

 ALTER SYSTEM SET "_cursor_obsolete_threshold"=1024 COMMENT='MOS Note: 2431353.1 - Sep 11, 2018' SCOPE=SPFILE;

More information

MOS Note: 2431353.1