High Waits On cursor: mutex X After Upgrading The Database to 12c, 18c & 19c

郑全

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

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.

郑全

Versions confirmed as being affected       
19.8.0
19.7.0
19.6.0
19.5.0
19.4.0
19.3.0
19.1.0
18.12.0
18.11.0
18.10.0
18.9.0
18.8.0
18.7.0
18.6.0
18.5.0
18.4.0
18.1.0
12.2.0.1 (Base Release)
12.1.0.2 (Server Patch Set)
11.2.0.4

the fix for 28889389 is first included in       
20.1.0
19.10.0.0.210119 (Jan 2021) Database Release Update (DB RU)
19.9.0.0.201020 (Oct 2020) Database Release Update(DB RU)
18.13.0.0.210119 (JAN 2021) Database Release Update (DB RU)
12.1.0.2.210119 (JAN 2021) Database Proactive Bundle Patch
12.2.0.1.210119 (Jan 2021) Bundle Patch for Windows Platforms