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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12361284/viewspace-528/