18.104.22.168 enhancement – large trace file produced when cursor becomes obsolete
Posted by John Hallas on October 15, 2015
A minor change came in with 22.214.171.124 which is causing large trace files to be produced under certain conditions.
The traces are produced as a result of an enhancement introduced in an unpublished bug.
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.
A parent cursor will be marked as obsolete after a certain number of child cursors have been produced under that parent as defined by the parameter “_cursor_obsolete_threshold”. In 12.1, the default is 1024 child cursors.
A portion of the trace file is shown below – we are only seeing it in a 126.96.36.199 OEM database at the moment and the problem with obsoleted parent cursors is not affecting us in a noticeable manner, although the size of the trace files and their frequency is.
----- Cursor Obsoletion Dump sql_id=a1kj6vkgvv3ns -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=0xc4720a8f0 phd=0xc4720a8f0
The SQL being:
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
The ‘feature’ is used to help Oracle support track issues with cursor sharing.
There is a parameter which can be used to stop or reduce the frequency of traces from MoS note 1955319.1
The dump can be controlled by the parameter “_kks_obsolete_dump_threshold” that can have a value between 0 and 8.
When the value is equal to 0, the obsolete cursor dump will be disabled completely:
alter system set "_kks_obsolete_dump_threshold" = 0;
When set to a value N between 1 and 8, the cursor will be dumped after cursor has been obsoleted N times:
By default, a parent cursor that is obsoleted is dumped when the parent SQL is obsoleted first time, i.e., N is 1.
alter system set "_kks_obsolete_dump_threshold" = 8;
The work around is to set the below underscore parameter that controls when the cursor will be dumped with a range of 0 to 8, 0 being disabled and 8 being after 8 iterations of being made obsolete etc.
We will be raising a support call re the cursor problem but probably setting the dump threshold to 8 at first and then 0 if we still keep on getting large traces. The default is set to 1.