High Waits On cursor: mutex X on 12.2.10 + 12c DB

  • 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<-

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.

To Apply the Patch 28889389 which has the optimized code for cursor mutex while searching the parent cursor for the match.

High Waits On cursor: mutex X After Upgrading The Database to 12c, 18c & 19c (Doc ID 2625815.1)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s