1 Reply Latest reply: May 26, 2010 1:13 AM by Billy~Verreynne RSS

    pmon waiting for enq RO  fast object reuse

      I would like to ask what's eng: RO - fast object reuse is.

      Im having a problem with oracle listener where as we did encounter ORA-12520: TNS:listener could not find available handler for requested type of server
      I am sure that we havent reached the session and process limits when the incident occur.

      During the time of the incident, i noticed that PMON is waiting for eng: RO - fast Object reuse. ( SID 1652 is PMON process )

      SQL> select dl.inst_id, s.sid, p.spid, dl.resource_name1,
      2 decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
      3 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
      4 'KJUSEREX','Exclusive',request_level) as grant_level,
      5 decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
      6 'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
      7 'KJUSEREX','Exclusive',request_level) as request_level,
      8 decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
      9 'KJUSERCA','Canceling','KJUSERCV','Converting') as state,
      10 s.sid, sw.event, sw.seconds_in_wait sec
      11 from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
      12 where blocker = 1
      13 and (dl.inst_id = p.inst_id and dl.pid = p.spid)
      14 and (p.inst_id = s.inst_id and p.addr = s.paddr)
      15 and (s.inst_id = sw.inst_id and s.sid = sw.sid)
      16 order by sw.seconds_in_wait desc;

      ------- ----- ------------ ------------------------------ ------------- ------------- ---------------- ----- ------------------------------ -----
      2 1652 29282 [0x20002][0x1],[RO] S/Row-X (SSX) S/Row-X (SSX) 1652 enq: RO - fast object reuse 152
      4 1652 8241 [0x40002][0x1],[RO] S/Row-X (SSX) S/Row-X (SSX) 1652 enq: RO - fast object reuse

      Aside from that, when i check the lock holder, it did show that pmon is one of the lock holders

      SQL> SELECT gvh.SID sessid, gvs.serial# serial,
      2 gvh.inst_id instance_id, username
      3 FROM gv$lock gvh, gv$lock gvw, gv$session gvs
      4 WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
      5 FROM gv$lock
      6 WHERE request = 0
      8 SELECT id1, id2
      9 FROM gv$lock
      10 WHERE lmode = 0)
      11 AND gvh.id1 = gvw.id1
      12 AND gvh.id2 = gvw.id2
      13 AND gvh.request = 0
      14 AND gvw.lmode = 0
      15 AND gvh.SID = gvs.SID
      16 AND gvh.inst_id = gvs.inst_id
      17 /

      ---------- ---------- ----------- ------------------------------
      1652 1 4
      1652 1 2

      And worst, when i checked the blocker, it seems that pmon is blocking itserf

      SQL> select s1.username || '@' || s1.machine
      2 || ' ( SID=' || s1.sid || ' ) is blocking '
      3 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS
      4 from v$lock l1, v$session s1, v$lock l2, v$session s2
      5 where s1.sid=l1.sid and s2.sid=l2.sid
      6 and l1.BLOCK=1 and l2.request > 0
      7 and l1.id1 = l2.id1
      8 and l2.id2 = l2.id2 ;


      @server2 ( SID=1652 ) is blocking @server2 ( SID=1652 )
      @server4 ( SID=1652 ) is blocking @server4 ( SID=1652 )

      PMON was killed on server2 for the instance to crash. The blocks and locks on server 2 went away after the instance startup. However, it took awhile before PMON unblock itself in server4.

      Does anyone experienced this problem?



      Edited by: user9217202 on May 25, 2010 4:03 PM
        • 1. Re: pmon waiting for enq RO  fast object reuse
          user9217202 wrote:
          I would like to ask what's eng: RO - fast object reuse is.
          I would say a bug if you see a process spinning on this event for more than a minute.

          Seen it very often on 10.1.0.x Standard Edition and on 10.2.0.x Enterprise Edition RAC. In my experience typically during a DROP TABLE or ALTER TABLE DROP PARTITION statement. The physical table/partition is dropped, but some post-drop step becomes stuck and spins on the event - and then block other processes (especially when it drops a partition and then via this event block all other processes from using any other valid partition of that table).

          Killing the process (using the ALTER SYSTEM command) usually works fine and things become unstuck. At the time I hunted through Metalink for this problem, but did not find one that described this particular situation. Could be that it has been reported and fixed in the meantime.

          I never bothered with a SR as I have custom monitoring software that can deal with non critical issues like these - and this was simply configured to kill a (maintenance) process when it becomes stuck after dropping a table or a partition.

          Never seen pmon stuck on it though...