自己總結的一些enqueue的東東

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
************************************
TX:
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;

Others:

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部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-528/，如需轉載，請註明出處，否則將追究法律責任。