In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 19.5.0.0.0 [Release 12.1 to 18]
Information in this document applies to any platform.
SYMPTOMS
High waits on cursor: mutex X after upgrading the database to 12.1.0.2, 12.2.0.1, 18c or 19c.
CPU utilization spikes to 100% due to the mutex waits leading to database hang or application outage.
Huge version count is seen for the SQL statements during the problem period.
Login or connection storm is observed during the problem.
Huge sleeps on functions kkscsAddChildNode & kkscsPruneChild as per 'Mutex Sleep Summary' from AWR report.
Mutex Type Location Sleeps Wait Time (ms)
Cursor Parent kkscsAddChildNode [KKSPRTLOC34] 302,348,122 140,622
Cursor Parent kkscsPruneChild [KKSPRTLOC35] 4,763,695 -493,302
Cursor Pin kkslce [KKSCHLPIN2] 1,232,845 659,278
Hanganalyze trace shows most of the chains running the same cursor from different processes waiting on 'cursor: mutex X' with the below stack.
The stack trace:
<-kgxExclusive<-kkscsAddChildNode<-kxscod<-kkscsCompareBinds<-kkscscid_bnd_eval<-kkscsCheckCriteria<-kkscsCheckCursor<-kkscsSearchChildList<-kksfbc<-
CHANGES
Upgrading the database to 12c and higher.
CAUSE
This is due to below internal bug which is fixed in 20.1.
Unpublished Bug 28889389 : HIGH WAITS ON CURSOR: MUTEX X AFTER UPGRADE TO 12.1.0.2
This bug is hit after having observed that multiple unique sessions are waiting for a parent cursor mutex in exclusive mode on the same cursor under the following stack:-
ksfwaitctx()+28<-kgxWait()+1164<-kgxExclusive()+652<-kkscsAddChildNode()
'cursor: mutex X' would have been taken on the parent cursor to find out the matching child cursor for that particular SQL. Some kind of logon storm where all sessions are executing the same cursor at the same time leading to this mutex contention. The child cursor sharing code has been optimized to reduce the Mutex Contention so that exclusive mutex is not acquired for searching the child cursors for match under the same parent.
SOLUTION
To Apply the Patch 28889389 which has the optimized code for cursor mutex while searching the parent cursor for the match.