Database Software

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;

INST_ID SID SPID RESOURCE_NAME1 GRANT_LEVEL REQUEST_LEVEL STATE SID EVENT SEC
------- ----- ------------ ------------------------------ ------------- ------------- ---------------- ----- ------------------------------ -----
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
7 INTERSECT
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 /

SESSID SERIAL INSTANCE_ID USERNAME
---------- ---------- ----------- ------------------------------
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
blocking_status
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 ;

BLOCKING_STATUS
-------------------------------------------------------------------------------

@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?

Regards

user9217202

Edited by: user9217202 on May 25, 2010 4:03 PM