Large tracefiles Created - Cursor Obsoletion Dump

Faced this issue in an OEM 12c repository database, where SYSMAN sessions would keep on writing to trace files and keeps them growing until deleted.

However, the files would again be created and SYSMAN sessions would not stop writing to it.

Each of the tracefile's header shows messages similar to below:


*** 2017-11-30 12:10:28.758
*** SESSION ID:(382.54831) 2017-11-30 12:10:28.758
*** CLIENT ID:(SYSMAN) 2017-11-30 12:10:28.758
*** SERVICE NAME:(SYS$USERS) 2017-11-30 12:10:28.758
*** MODULE NAME:(OEM.DefaultPool) 2017-11-30 12:10:28.758
*** CLIENT DRIVER:(jdbcthin) 2017-11-30 12:10:28.758
*** ACTION NAME:([ACTIVE] ExecuteThread: '2' for ) 2017-11-30 12:10:28.758

----- Cursor Obsoletion Dump sql_id=a1kj6vkgvv3ns -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=0xb671cc90 phd=0xb671cc90
----- Dump Cursor sql_id=a1kj6vkgvv3ns xsc=0x7f851a21db48 cur=0x7f851abb2070 -----

LibraryHandle:  Address=0xb671cc90 Hash=9fbd8e98 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=SELECT TP.PROPERTY_NAME, TP.PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES TP, MGMT_TARGETS T WHERE TP.TARGET_GUID = T.TARGET_GUID AND T.TARGET_NAME = :B2 AND T.TARGET_TYPE = :B1
    FullHashValue=a9363acd946320fea0ca26dc9fbd8e98 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 ContainerUid=0 Identifier=2680000152 OwnerIdn=102
  Statistics:  InvalidationCount=22013 ExecutionCount=250437 LoadCount=42292 ActiveLocks=1 TotalLockCount=263612 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=672 Version=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0xb671cd40(0, 14857, 3, 0) Mutex=0xb671cdd8(0, 55916292, 12, 0)
  Flags=RON/PIN/TIM/OBS/PN0/DBN/[10412841] Flags2=[0000]
  WaitersLists:
    Lock=0xb671cd20[0xb671cd20,0xb671cd20]
    Pin=0xb671cd00[0xb671cd00,0xb671cd00]
    LoadLock=0xb671cd78[0xb671cd78,0xb671cd78]
  Timestamp:  Current=09-13-2017 00:09:02
  HandleReference:  Address=0xb671cef8 Handle=(nil) Flags=[00]
  LibraryObject:  Address=0xd6b5dc08 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable:  size='1024'
      Child:  id='0' Table=0xd6b5ea88 Reference=0xd6b5e548 Handle=0xc1199ab0
      Child:  id='1' Table=0xd6b5ea88 Reference=0xd6b5e8c0 Handle=0x1143cd090
      Child:  id='2' Table=0xd6b5ea88 Reference=0xa6277020 Handle=0x97489fa8
      Child:  id='3' Table=0xd6b5ea88 Reference=0xa6277330 Handle=0x107b002b0
      Child:  id='4' Table=0xd6b5ea88 Reference=0xa6277640 Handle=0xe0671bb8
      Child:  id='5' Table=0xd6b5ea88 Reference=0xa6277950 Handle=0x100d842c8
      Child:  id='6' Table=0xd6b5ea88 Reference=0xa6277c60 Handle=0x8ded1870
      Child:  id='7' Table=0xd6b5ea88 Reference=0xaf605168 Handle=0xce89cfe0
      Child:  id='8' Table=0xd6b5ea88 Reference=0xaf605478 Handle=0xa65d5678
      Child:  id='9' Table=0xd6b5ea88 Reference=0xaf605788 Handle=0xc70b14c0
      Child:  id='10' Table=0xd6b5ea88 Reference=0xaf605a98 Handle=0xa9adece0
....

There is a similar problem noted in Oracle Doc ID (1955319.1) which describes issue as:

The traces are produced as a result of a diagnostic enhancement introduced in 12c. This enhancement dumps diagnostic data for concurrent sessions

This proactive diagnostic which dumps the parent cursor when the obsolete threshold is exceeded.

In some cases, when many concurrent sessions dumping this diagnostic information, performance issues may be observed in the form of High CPU and latch waits.

The aim of the bug is 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.

Solution:

The problem in my case was resolved by flushing the shared pool. This would clear the cursor area thus resolving the problem temporarily.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

As per Oracle note below solutions can be implemented:

1. Apply Patch 22075064: GSI-12C :- ADVERSE SYSTEM IMPACT DUE TO CONCURRENT CURSOR OBSOLETION DUMPS

2. Diagnostic feature can be disabled by setting parameter "_kks_obsolete_dump_threshold" = 0

Cheers,
Anurag