显示等待信息

--检查当前有哪些等待的session
select c.sid wait_sid, c.event,
       chr(bitand(c.p1, -16777216) / 16777215) || chr(bitand(c.p1, 16711680) / 65535) enq,
       decode(bitand(c.p1, 65535), 1, 'Null', 2, 'Sub-Share', 3, 'Sub-Exclusive', 4, 'Share', 
                                   5, 'Share/Sub-Exclusive', 6, 'Exclusive', 'Other') lock_mode,
       a.sid lock_sid
  from v$session a, v$transaction b, v$session_wait c
 where c.event like 'enq:%'
   and trunc(c.p2 / power(2, 16)) = b.xidusn
   and (bitand(c.p2, to_number('ffff', 'xxxx')) + 0) = b.xidslot
   and c.p3 = b.xidsqn
   and a.taddr = b.addr;

--检查当前有哪些等待的session(RAC)
select a.inst_id wait_inst_id, a.sid wait_sid, a.event, c.inst_id lock_inst_id, c.sid lock_sid,
       chr(bitand(a.p1, -16777216) / 16777215) || chr(bitand(a.p1, 16711680) / 65535) enq,
       decode(bitand(a.p1, 65535), 1, 'Null', 2, 'Sub-Share', 3, 'Sub-Exclusive', 4, 'Share', 
                                   5, 'Share/Sub-Exclusive', 6, 'Exclusive', 'Other') lock_mode
  from gv$session_wait a, gv$transaction b, gv$session c
 where a.event like 'enq:%'
   and trunc(a.p2 / power(2, 16)) = b.xidusn
   and (bitand(a.p2, to_number('ffff', 'xxxx')) + 0) = b.xidslot
   and a.p3 = b.xidsqn
   and c.taddr = b.addr;

--检查
select a.sid wait_sid,
       a.event,
       b.sid lock_sid,
       trunc(b.id1 / power(2, 16)) rbs,
       bitand(b.id1, to_number('ffff', 'xxxx')) + 0 slot,
       b.id2 seq,
       b.lmode,
       b.request,
       b.type
  from v$session_wait a, v$lock b
 where a.p2 = b.id1
   and a.p3 = b.id2
   and a.event like 'enq:%';
Trackback

no comment untill now

Add your comment now

切换到手机版