enqueque wait的查杀,不用v$(运行成本较高)

  在一个OLTP系统中, 如果程序设计不够合理, 或由于其他种种原因, 导致数据库中出现了大量的Enqueue Wait的话, 是一件很严重的事, 如果不马上采取措施的话, 有可能在很短的时间内就拖跨整个系统. 因此我们需要及时发现那些会话可能占了锁没有释放, 也就是找出锁的拥有者, 一般是锁了记录, 而又很久没有和数据库交互的会话, 可以用下面的语句去找出来, 然后杀掉这些会话, 或重起这些应用服务器.

select sysdate day, sid, serial#, machine,
    nvl(sql_hash_value, prev_hash_value) hash_value,
    last_call_et
from v$session
where sid in              -- Holding Some TX or TM Locks
  (select sid from v$lock where block=1
    UNION ALL
    select sid from v$lock where type='TX' and lmode > 1
  )
  and last_call_et > 60  -- Sleep More Than 60 Seconds
  and type = 'USER'      -- User Session
  and status <> 'ACTIVE' -- Inactive Session

    网上也有根据V$LOCK, 列出锁等待关系的SQL语句, 但他们的运行成本太高了, 不能拿来频繁地执行以实时监控. 根据特定的环境, 编写适用的SQL是个好习惯.

3 Responses to “及时清除Enqueue Wait”

1. 木匠Charlie Says: 2009-02-16 at 00:14

   这里是我用来定期(每3分钟)检查blocking lock的一段代码,
   FOR c_lock IN (
   WITH b AS (SELECT /*+ NO_PARALLEL(b)*/inst_id,ID1,ID2,TYPE FROM gv$lock a WHERE request>0 AND ctime>5
   group by inst_id,ID1,ID2,TYPE)
   SELECT –+ ordered use_nl(l,b) NO_PARALLEL(l) NO_PARALLEL(b)
   l.INST_ID,
   l.SID, l.TYPE, l.ID1, l.ID2, l.LMODE, l.REQUEST, l.CTIME, l.BLOCK
   FROM b, gv$lock l
   WHERE l.ID1 = b.ID1 AND l.ID2 = b.ID2 AND l.TYPE = b.TYPE
   AND b.inst_id = l.inst_id
   ORDER BY id1, id2, request ASC
   )
   LOOP
   DEBUG.f(‘ ‘);
   DEBUG.f(‘%s Session:’, CASE WHEN c_lock.REQUEST > 0 THEN ‘BLOCKED’ ELSE ‘BLOCKING’ END );
   DEBUG.f(‘Node: %s, LockTime: %s s’, c_lock.inst_id, c_lock.ctime);
   –…
   END LOOP;

2. zergduan Says: 2009-02-26 at 15:21

   我觉得hash_value的意义不大，毕竟不能保证是因为这个sql,block了别的session. 产生block的sql很难找到的，还有dba_waiters不是更好么？holder waiter都有了

3. anysql Says: 2009-02-26 at 15:40

   DBA_WAITERS没有等待时长, 可能内部实现也是查询V$LOCK的吧, 对于开发人员来讲告诉他们应用服务器的名字比较好。