查询等待事件(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' ;
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博客 ” ，链接：https://blog.itpub.net/35489/viewspace-755108/ ，如需转载，请注明出处，否则将追究法律责任。
