首页 > Linux操作系统 > Linux操作系统 > 自己总结的一些enqueue的东东


原创 Linux操作系统 作者:oracle_ace 时间:2007-12-13 13:52:10 0 删除 编辑

When running the declare operation,IRMDB have the top hot wait event named ENQUEUE

1,we can see this kind of wait events by using the dynamic view named v$session_wait
select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';

or more detail information as below

select chr(to_char(bitand(p1,-16777216))/16777215)||
chr(to_char(bitand(p1, 16711680))/65535) "Lock",
to_char( bitand(p1, 65535) ) "Mode"
from v$session_wait
where event = 'enqueue';

2,Then we can query the detail information of ENQUEUE
select * from v$enqueue_stat;

select eq_type "Lock", total_req# "Gets", total_wait# "Waits",
cum_wait_time "Total Wait time" from V$enqueue_stat
where Total_wait# > 0 ;

3,TX,TT,TM these kinds of ENQUEUE is
1) Another session is locking the requested row.

2) When two sessions tries to insert the same unique key into a table (none of them has done a COMMIT), then the last session is waiting for the first one to COMMIT or ROLLBACK.

3) There are no free ITL (Interested Transaction List) in the block header (increase INI_TRANS or PCT_FREE for the segment).

TT: Serializes DDL operations on tablespaces;

4,Find out the holder and waiter of enqueue lock
set line 160
col machine format a10
col username format a15

Notice that the result for SID(18) is the session id who is waiting for the enqueue lock

select b.sid,b.serial#,b.username,machine,event,wait_time,chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) "Enqueue Type"
from v$session_wait a,v$session b
where event not like 'SQL*N%' and event not like 'rdbms%' and a.sid=b.sid
and b.sid>8 and event='enqueue'
order by username;

       SID    SERIAL# USERNAME        MACHINE    EVENT                           WAIT_TIME En
---------- ---------- --------------- ---------- ------------------------------ ---------- --
       18      44840 SYS             rmsvtp02      enqueue                                 0 TX

select decode(request,0,'Lock Holder: ','Lock Waiter: ')|| sid sess,
id1,id2,lmode,request,type  from v$lock
where (id1,id2,type) in (select id1,id2,type from v$lock where request>0)
order by id1,request;

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 24                                           655363       1673          6          0 TX
Waiter: 18                                           655363       1673          0          6 TX

5,Find out the relevant SQL statment
select /*+ ORDERED */ sql_text FROM v$sqltext a
where (a.hash_value, a.address) in
(select decode(sql_hash_value,0,prev_hash_value,sql_hash_value),
        decode(sql_hash_value,0,prev_sql_addr, sql_address)
from v$session b
where b.sid = &sid) order by piece ASC;

6,Find out the relevant Locked object
select /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#
from v$locked_object l,dba_objects o,v$session s
where l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC;


alter system kill session '24,36906';

select pro.spid from v$session ses,v$process pro where ses.sid=&sid and ses.paddr=pro.addr;

select o.object_name
from v$locked_object l,dba_objects o,v$session s
where l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC;
group by o.object_name;

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量