查询等待事件(wait event)相关的SQL - v$session_wait, v$rowcache,v$sqltext

  

select event,count(*) from gv$session_wait group by event order by count(*) desc; 

select  *  from   v$session_wait   where  event = 'row cache lock' ;

select   sid   from   v$rowcache  where   cache#=7 ;

查询相关的SQL:
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  in  (select sid from v$session_wait where event = 'row cache lock' )  )
order by piece asc  ;

批量kill session 的sql :

select   'alter system kill  session '''||sid||','||serial#||'''  ;'   from  v$session  where
sid in  (select  sid  from   v$session_wait   where  event = 'row cache lock' )  and  type='USER'   ;

 

实在不行查询出来kill os pid .

SELECT pro.spid, sess.SID, sess.*  from v$session sess, v$process pro
where  sess.PADDR = pro.ADDR  and sess.sid in 
(
select  sid  from  v$session  where
sid in  (select  sid  from   v$session_wait   where  event = 'row cache lock' )  and  type='USER'
)  ;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-755108/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-755108/