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.

Growth - Trace Files - (c) Mike Dietrich

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

5 thoughts on “Where do these large trace files come from in Oracle 12c?

  1. Pingback: Parameter Recommendations for Oracle Database 12c – Part I | Upgrade your Database - NOW!

  2. Hmmmm……
    12.2.0.1 Oracle on Aix 64-bit, latest April 18 patch installed.
    Every time I start rman and do any backup command, it generates 10GB of trace files and crashes once the trace file system is full…
    Now, is this a widely used release that doesn’t need dbas to take care of it?
    It all works fine in the cloud, is it? πŸ™‚
    Sorry, but for what is supposed to be a well established release, so far 12.2 is a sad joke…
    And yes, I know it’s not your fault! It’s mine for not accepting the “cloud dbaas” offering.
    Or should I call that by its proper name: Outsourcing 2.0? πŸ™‚

    • Noons,

      I fully understand your “enjoyment” πŸ˜‰

      Without knowing more details I did a quick check on MOS:

      Oracle Support Document 26844870.8 (Bug 26844870 – Backup Via RMAN Generates Trace Files “kcbtse_print_kod: printing kcbtekod …”) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=26844870.8
      Workaround
      None, but to ignore the messages and remove the trace files.

      Oracle Support Document 2311975.1 (RMAN BACKUP Command Always Generates Trace File Having “kcbtse_print_kod” Message) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2311975.1
      Those messages are debugging messages.
      So please ignore these messages or remove trace file having above messages.
      Or get patch of BUG:26844870

      BUG:26844870 – BACKUP VIA RMAN ALWAYS GENERATES TRACE FILES OF KCBTSE_PRINT_KOD MESSAGE

      Sorry πŸ™
      Actually I wasn’t aware – but good that I know now. It’s actually the 2nd “excessive” tracing I come across now within a few days (the other one was MMNON unconditional traces every 3 seconds).

      Hope this helps!

      Cheers,
      Mike

  3. Thanks for that, Mike.
    Yes, we got the MMON one as well. That one was easy, it has a known patch.
    All I had to do was install the patch and bingo: done. Pity it’s not included in the April18 RU. But fixed now anyway!
    The one for RMAN was a bit worse There is no known fix for Aix, or at least the guy in MOS could not find it. But he found a workaround!
    Set this event in spfile:
    event=’trace[krb.*] disk disable, memory disable’
    restart db and bingo!, all those pesky infinite growing trace files during an rman backup go away like a blown cloud! (ooops… Sorry! πŸ™‚ )
    Anyways, here it is just in case someone else hits this in Aix Oracle.
    Please keep up this excellent work of letting us know of issues with upgrades and how best to go about them.
    It’s been incredibly useful of late, now that I’ve finally managed to convince our PTB (Powers That Be) that running databases in an unsupported release is a very risky idea!

    • Noons,

      the MMON trace issue will be included in the October RU – see my blog post tomorrow as well about it.
      Reason why it wasn’t included before: Somebody classified it as “non RAC rolling installable” which was falsely done.

      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