1、latch free等待事件的SQL:
select *
from v$session_wait t
where t.EVENT not in
('SQL*Net message from client', 'rdbms ipc message',
'SQL*Net more data from client', 'SQL*Net more data to client',
'SQL*Net message to client', 'jobq slave wait')
结果分析说明:观察一下查询结果中有没有latch、enqueue的事件,要是有,就根据sid去找SQL的HASHVALUE
2、运行超长SQL监控SQL:
select * from v$session_longops a where a.time_remaining>0
结果分析说明:找到有全表扫描的,或者hash jion的,成本较高。需要找到hash_value,再去v$sqltext查到具体的语句进行优化!