原创：oracle脚本整理1

转载请标明地址：

--求等待事件及其对应的latch
col event format a32
col name format a32
select sid,event,p1 as file_id,p2 as "block_id/latch" ,p3 as blocks ,l.name
from v$session_wait sw ,v$latch l
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2=l.latch#(+);

--求等待事件及其热点对象
col owner format a18
col segment_name format a32
col segment_type format a32
select owner,segment_name,segment_type
from dba_extents
where file_id=&file_id and &block_id between block_id
and block_id+&blocks-1;

--综合以上两条SQL，同时显示latch及热点对象（速度较慢）
select sw.sid,event,l.name,de.segment_name
from v$session_wait sw,v$latch l,dba_extents de
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%'  and sw.p2=l.latch#(+) and sw.p1=de.file_id(+) and p2 between
de.block_id and de.block_id +de.blocks -1;

--如果是非空闲等待事件，通过等待会话的SID可以求出该会话在执行的SQL
select sql_text from v$sqltext_with_newlines st ,v$session se
where st.address=se.sql_address and st.hash_value=se.sql_hash_value
and se.sid=&wait_sid order by piece;