High wait event ‘row cache mutex’ in 12cR2、19c

CASE 1

an Oracle 12.2.0.1.0 (12cR2), “row cache mutex” replaced 12.1.0.2.0 (12cR1) and 11g “latch: row cache objects”, similar to “latch: library cache” substitution by “library cache: mutex X” in the previous release.

P1TEXT = cache id ，”cache id” can be used to directly pinpoint the exact contention row cache objects. vrowcache.cache#(xkqrst.kqrstcln)

SQL> select cache#,type,PARAMETER,gets,getmisses,flushes from V$ROWCACHE where cache#=10;

    CACHE# TYPE        PARAMETER                              GETS  GETMISSES    FLUSHES
---------- ----------- -------------------------------- ---------- ---------- ----------
        10 PARENT      dc_users                              38441        113          0
复制

12cR2 v$rowcache contains 71 rows, 19c（19.3）contains 75 rows, 20c(20.2) contains 76 rows.

Case 2.

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT P1                  P2                  P3                  P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ------------------- ------------------- ------------------- ------------------------------------------
  25408 WAITING row cache mutex                               18936           0 cache id= 10        where requested= 19 0

SQL> @usid 25408

USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID           HASH_VALUE   LASTCALL STATUS   SADDR            PADDR            TADDR            LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ --------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -----------------
ANBOB                     '25408,43879'  1940483454 appuser          M10101             JDBC Thin Client     19218            9297 1234                     as330bnj817qs     578854616          3 INACTIVE C0000009980CCE78 C0000009557DF350                  20210713 18:04:37

SQL> oradebug setorapid 9297
Oracle pid: 9297, Unix process pid: 19218, image: oracle@qdyye1
SQL> oradebug short_stack
ksedsts()+592<-ksdxfstk()+64<-ksdxcb()+2000<-sspuser()+640<-<-_select_sys()+48<-_select()+240<-skgpnap()+528<-skgpwwait()+592<-kgxWait()+1440<-kgxModifyRefCount()+896<-kgxSharedExamine()+96<-kxsGetRuntimeLock()+544<-kkscsCheckCursor()+944<-kkscsSearchChildList()+2656<-kksfbc()+5168<-kkspsc0()+3120<-kksParseCursor()+336<-opiosq0()+6144<-kpooprx()+656<-kpoal8()+1520<-opiodr()+2176<-ttcpip()+1760<-opitsk()+3936<-opiino()+1712<-opiodr()+2176<-opidrv()+1824<-sou2o()+288<-opimai_real()+608<-ssthrdmain()+864<-main()+336<-main_opd_entry()+80 SQL> oradebug short_stack
ksedsts()+592<-ksdxfstk()+64<-ksdxcb()+2000<-sspuser()+640<-<-_pw_wait()+48<-pw_wait()+128<-sskgpwwait()+432<-skgpwwait()+448<-ksliwat()+4480<-kslwaitctx()+368<-ksfwaitctx()+48<-kgxWait()+2704<-kgxExclusive()+640<-kqrGetMutexByAddr()+256<-kqrGetHashMutex()+160<-kqrpre1()+992<-ktatminextsz()+832<-qerhjComputeFanoutAndBPS()+800<-kkejnc()+4448<-kkojnp()+23904<-kkocnp()+96<-kkooqb()+3664<-kkoqbc()+5456<-apakkoqb()+368<-apaqbdDescendents()+976<-apaqbdList()+160<-apaqbdDescendents()+448<-apaqbd()+144<-kkofkrSetup()+528<-apadrv()+6704<-opitca()+4320<-kksFullTypeCheck()+192<-rpiswu2()+1488<-kksSetBindType()+4448<-kksfbc()+24064<-opiexe()+9280<-kpoal8()+5856<-opiodr()+2176<-ttcpip()+1760<-opitsk()+3936<-opiino()+1712<-opiodr()+2176<-opidrv()+1824<-sou2o()+288<-opimai_real()+608<-ssthrdmain()+864<-main()+336<-main_opd_entry()+80 SQL> oradebug short_stack
ksedsts()+592<-ksdxfstk()+64<-ksdxcb()+2000<-sspuser()+640<-<-_pw_wait()+48<-pw_wait()+128<-sskgpwwait()+432<-skgpwwait()+448<-ksliwat()+4480<-kslwaitctx()+368<-ksfwaitctx()+48<-kgxWait()+2704<-kgxExclusive()+640<-kqrGetMutexByAddr()+256<-kqrGetHashMutex()+160<-kqrpre1()+992<-kqrpre()+64<-kkdlGetBaseUser2()+256<-kkdlGetBaseUser()+48<-kzulgt1()+320<-kzulgt()+48<-kksLockUserSchema()+144<-kksLoadChild()+3040<-kkslod()+112<-kglobld()+2064<-kglobpn()+1936<-kglpim()+1120<-kglpin()+3216<-kxsGetRuntimeLock()+3744<-kksfbc()+18224<-kkspsc0()+3120<-kksParseCursor()+336<-opiosq0()+6144<-kpooprx()+656<-kpoal8()+1520<-opiodr()+2176<-ttcpip()+1760<-opitsk()+3936<-opiino()+1712<-opiodr()+2176<-opidrv()+1824<-sou2o()+288<-opimai_real()+608<-ssthrdmain()+864<-main()+336<-main_opd_entry()+80 SQL> oradebug short_stack
ksedsts()+592<-ksdxfstk()+64<-ksdxcb()+2000<-sspuser()+640<-<-_pw_wait()+48<-pw_wait()+128<-sskgpwwait()+432<-skgpwwait()+448<-ksliwat()+4480<-kslwaitctx()+368<-ksfwaitctx()+48<-kgxWait()+2704<-kgxExclusive()+640<-kqrGetMutexByAddr()+256<-kqrGetHashMutex()+160<-kqrpre1()+992<-kqrpre()+64<-kkdlGetBaseUser2()+256<-kkdlGetBaseUser()+48<-kzulgt1()+320<-kzulgt()+48<-kksLockUserSchema()+144<-kksLoadChild()+3040<-kxsGetRuntimeLock()+2944<-kksfbc()+18224<-kkspsc0()+3120<-kksParseCursor()+336<-opiosq0()+6144<-kpooprx()+656<-kpoal8()+1520<-opiodr()+2176<-ttcpip()+1760<-opitsk()+3936<-opiino()+1712<-opiodr()+2176<-opidrv()+1824<-sou2o()+288<-opimai_real()+608<-ssthrdmain()+864<-main()+336<-main_opd_entry()+80

-- or  using wait_event new feature from 12.1--
ALTER SESSION SET EVENTS 'wait_event["row cache mutex"] 
                               trace("stack is: %\n", shortstack())';
-- run sql
复制

12cR2 v$rowcache contains 71 rows, 19c（19.3）contains 75 rows, 20c(20.2) contains 76 rows.

• 1. if High waits on “row cache mutex” when looking up user or role information in user row cache (dc_users).lots of requests for mutex on user$ row cache, may hit Bug 30623138. fix in 19.8
• 1. if row cache dc_props or dc_cdbprops and the sessions are using dblinks. call stack like “kqrpre2 kqrpre1 kkdlpExecSqlCbk kkdlpExecSql kkdlpGet kziagvs kzdlkcsaltd kzdlkdbde npigdn0 npicon0 kpndbcon” , may hit Bug 30712670. fix in 19.10
• 1. if Already have fix for 30623138 installed, 19.8 or newer have high get hit rate on dc_users ,Recommend to apply patch 31933451.
• 1. if call stack like “kslwaitctx ksfwaitctx kgxWait kgxExclusive kqrGetMutexByAddr“, may hit bug 31135517 （fixed in 19.8）, This fix has been superseded by the fix in bug 31933451.
• 1. if Call stack with the” kqrGetHashMutexInt <- kqrpre1 <- kkaegeo_get_edition_objno_1 “, may hit Bug 32042352. fix in 19.12
• 1. if when query V<math><semantics><mrow><mi>P</mi><mi>R</mi><mi>O</mi><mi>C</mi><mi>E</mi><mi>S</mi><mi>S</mi><mi>P</mi><mo separator="true">,</mo><mi>V</mi></mrow><annotation encoding="application/x-tex">PROCESS P, V</annotation></semantics></math>PROCESSP,VSESSION, call stack like “kqrpre<-kpdbCheckAuthIdCbk<-kpdbCheckAuthId<-kpdbCommonUserOrRole<-qerfxStart” may hit bug 31903523, fix in 19.10.
• 1. if Call stack with kqrpre<-- kkdltob, may hit bug 30329209. fix in 19.8

More about latch: row cache objects Troubleshooting “latch: row cache objects” case and Event 10089 to do.