oracle等待事件查询,Oracle查看等待事件_yh_zeng2的博客-CSDN博客

--查询等待的会话ID ， 阻塞的等待时间类型、事件ID 、 SQLID 等等信息 select *

from v$active_session_history h

where sample_time > trunc(sysdate)

and session_state = 'WAITING'

and exists(

select 1 from v$sql s

where upper(s.sql_text) like '%T_USER%'

and s.sql_id = h.sql_id

)

order by sample_time desc;

--会话阻塞的事件查询 select * from v$session_wait where sid = 148;

--会话发生过的所有等待事件查询 select s.time_waited/1000,s.* from v$session_event s where sid = 148;

--被锁了之后，查看持有该锁的会话查询 select a.sid blocker_sid,

a.serial#,

a.username as blocker_username,

b.type,

decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,

b.ctime as time_held,

c.sid as waiter_sid,

decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,

c.ctime time_waited

from   v$lock b, v$enqueue_lock c, v$session a

where  a.sid = b.sid

and  b.id1= c.id1(+)

and  b.id2 = c.id2(+)

and  c.type(+) = 'TX'

and  b.type = 'TX'

and  b.block   = 1

and  c.sid = 148

order by time_held, time_waited;

--查询持有锁的会话执行了的SQL select s.sql_text,h.* from v$active_session_history h,v$sql s

where h.sql_id = s.sql_id

and h.session_id = 150;