首页 » Cloud, ORACLE 9i-23ai » High wait event ‘row cache mutex’ in 12cR2、19c

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

  • 2020/06/25
  • Cloud, ORACLE 9i-23ai
  • 2,772 views
  • High wait event ‘row cache mutex’ in 12cR2、19c已关闭评论

case 1

In 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. v$rowcache.cache#(x$kqrst.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

SQL> select mutex_type,location,sleeps,wait_time from x$mutex_sleep where location_id=19;

MUTEX_TYPE                       LOCATION                                     SLEEPS  WAIT_TIME
-------------------------------- ---------------------------------------- ---------- ----------
Row Cache                        [19] kqrpre                                      25     172915

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

2.  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

3. if Already have fix for 30623138 installed,   19.8 or newer have high get hit rate on dc_users ,Recommend to apply patch 31933451.

4. 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.

5. if Call stack with the” kqrGetHashMutexInt <- kqrpre1 <- kkaegeo_get_edition_objno_1 “, may hit Bug 32042352. fix in 19.12

6. if when query V$PROCESS P, V$SESSION, call stack like “kqrpre<-kpdbCheckAuthIdCbk<-kpdbCheckAuthId<-kpdbCommonUserOrRole<-qerfxStart” may hit bug 31903523, fix in 19.10.

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

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：