Where do these large trace files come from in Oracle 12c?

Just had an observation about very large trace files on one of my customers I’m working with at the moment. When I write “very” I mean “VERY” as some grew over 10GB within a few hours.

The files contained a ton of such messages:

----- Cursor Obsoletion Dump sql_id=5p8a9d4017bq3 -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=00007FFB8AD45CB0 phd=00007FFB8AD45CB0

After doing a bit of research I came across this document and an explanation:

MOS Note:1955319.1;
Huge Trace Files Created Containing “—– Cursor Obsoletion Dump sql_id=%s —–“

Well, we introduced an Enhancement – via an unpublished bug (and I’d guess it is undocumented then) in Oracle Database 12.1.0.2 to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and it’s child cursors after the parent cursor has been obsoleted N times.

You can control this behavior by altering the value for:

alter system set "_kks_obsolete_dump_threshold" = N;

Possible value range is 0..8 whereas 0 means: switch the obsolete cursor dump off completely, and other values (N) defined the number of invalidations after which the cursor will be dumped.

The default in Oracle Database 12.1.0.2 is 1 meaning this will happen after every single invalidation.Just remember: this will affect child cursors as well.

Just be aware that the underlying cursor sharing problem needs to be investigated – always.
If you have cursor sharing issues you may set this parameter higher therefore not every invalidation causes a dump, then investigate and solve the issue, and finally switch the parameter to 0 once the issue is taken care of.

Please be aware that switching the parameter to 0 will lead to a lack of diagnostics information in case of cursor invalidations.

–Mike