3 Replies Latest reply: Feb 13, 2013 11:46 AM by 982500 RSS

    what is "resmgr:schema config" latch in 11gR2??

    872581
      Hi, all.

      The database is 2-node RAC 11.2.0.3 on a linux machine.

      I found "latch free" waits in my production system.

      What is "resmgr:schema config" latch?

      The current_obj# indicates tables and indexes.

      select event,p1,p2,p3 ,current_obj#
      from hoho.tt8 -- dump for ash
      where event ='latch free'
      order by sample_time
      
      EVENT     P1     P2     P3     CURRENT_OBJ#
      latch free     1610837928     316     0     175598
      latch free     1610837928     316     0     26767
      latch free     1610837928     316     0     93167
      latch free     1610837928     316     0     -1
      latch free     1610837928     316     0     129305
      
      
      select * from gv$latchname where latch#=316
      
      INST_ID     LATCH#     NAME     HASH
      1     316     resmgr:schema config     1846237044
      2     316     resmgr:schema config     1846237044
      
      
        SELECT name, 'Child '||child#, gets, misses, sleeps
          FROM Gv$latch_children
         WHERE addr='1610837928'
        UNION
        SELECT name, null, gets, misses, sleeps
          FROM Gv$latch
         WHERE addr='1610837928'
      
      => no row selected !
      
      select session_id,session_type,  session_state,
             blocking_session_status, program
        from hoho.tt8
      where session_id=1035
      
      SESSION_ID     SESSION_TYPE     SESSION_STATE     BLOCKING_SESSION_STATUS     PROGRAM
      1035     BACKGROUND     ON CPU     NOT IN WAIT     oracle@pd1dbw02 (LMON)
      1035     BACKGROUND     ON CPU     NOT IN WAIT     oracle@pd1dbw02 (LMON)
      1035     BACKGROUND     ON CPU     NOT IN WAIT     oracle@pd1dbw02 (LMON)
      1035     BACKGROUND     ON CPU     NOT IN WAIT     oracle@pd1dbw02 (LMON)
      1035     BACKGROUND     ON CPU     NOT IN WAIT     oracle@pd1dbw02 (LMON)
      1035     BACKGROUND     ON CPU     NOT IN WAIT     oracle@pd1dbw01 (LMON)
      
      => the blocker is LMON.
      ..

      Thanks in advance.
      Best Regards.

      Edited by: 869578 on 2013. 1. 29 오후 5:09

      Edited by: 869578 on 2013. 1. 29 오후 8:46
        • 1. Re: what is "resmgr:schema config" latch in 11gR2??
          Raman
          May be bug?

          Bug 10130415 - Latch contention on "resmgr group change latch" [ID 10130415.8]
          • 2. Re: what is "resmgr:schema config" latch in 11gR2??
            988672
            Hi,

            I'm also trying to find out something about "resmgr:schema config", but there is nothing, is like not documented by Oracle.
            This is on the top of the list in the DB I'm analyzing:

            SELECT * FROM (SELECT NAME, WAIT_TIME FROM V$LATCH
            ORDER BY WAIT_TIME DESC) WHERE ROWNUM <= 10;

            NAME WAIT_TIME
            ---------------------------------------------------------------- ----------
            resmgr:schema config 3423793060
            In memory undo latch 1448622367
            cache buffers chains 258764157
            shared pool 221601646
            space background task latch 2627785
            FOB s.o list latch 1803154
            enqueues 1563774
            row cache objects 1495877
            redo allocation 871733
            session idle bit 827759

            I'm thinking about to turn resource manager off temporarily and see if this can be a reason behind slower DB response.

            Any ideas?

            Regards,
            Matt
            • 3. Re: what is "resmgr:schema config" latch in 11gR2??
              982500
              Hello.

              I'm facing the same problem. Wait event "latch free" appears on the Top 5 Timed Foreground Events of our database, with 44.91%.

              Top 5 Timed Foreground Events

              Event     Waits     Time(s)     Avg wait (ms)     % DB time     Wait Class
              latch free     11,183,283     1,732,103     155     44.91     Other
              DB CPU          830,211          21.53     
              log file sync     107,883,554     668,402     6     17.33     Commit
              library cache: mutex X     105,399,128     277,537     3     7.20     Concurrency
              db file sequential read     78,313,388     123,106     2     3.19     User I/O

              On the Latch Activity section, "resmgr:schema config" is the one that have the high Wait Time:

              Latch Name     Get Requests     Pct Get Miss     Avg Slps /Miss     Wait Time (s)     NoWait Requests     Pct NoWait Miss
              resmgr:schema config     789,096,924     14.31     0.10     1729345     160     12.50

              And here is the Instance Efficiency Percentages section:

              Instance Efficiency Percentages (Target 100%)

              Buffer Nowait %:     100.00     Redo NoWait %:     99.99
              Buffer Hit %:     99.83     In-memory Sort %:     100.00
              Library Hit %:     100.23     Soft Parse %:     97.31
              Execute to Parse %:     99.99     Latch Hit %:     99.27
              Parse CPU to Parse Elapsd %:     1.94     % Non-Parse CPU:     95.87



              I didn't find any documents explaining what's behind this "resmgr:schema config" latch. Any ideas?