查詢等待事件(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'   ;