Just a quick query to run to see if there’s a particular wait event that’s spiking during a timeframe:
select COUNT(*), event, sample_time, inst_id
from gv$active_session_history
where sample_time BETWEEN TRUNC(SYSDATE) + (10/24) + (50/60)/24
AND TRUNC(SYSDATE) + (10/24) + (56/60)/24
HAVING COUNT(*) > 10
GROUP BY event, sample_time, inst_id
ORDER BY sample_time, event, instead
I like to use the fractions to make it easier to tell the timeframe that I’m looking at (in this case between 10:50 AND and 10:56 AM).
Since this uses active_session_history, it only works for recent events – that table is in the cache only and can age out pretty quickly (hours or days).