Cool little function to provide statistical correlation between two events. This helped us to identify performance drivers in our database. Statistically, anything above 60 is considered “statistically significant”, or correlated. If one goes up, so does the other.
create or replace function corr_events (p_event1 in varchar2, p_event2 in varchar2) return number is l_num number; begin select round((corr(val1,val2) * 100),0) into l_num from (select snap_id, last_value(snap_id) over (order by snap_id rows between 1 preceding and current row) snap_id1, time_waited_micro - min(time_waited_micro) over (order by snap_id rows between 1 preceding and current row) val1 from dba_hist_system_event where event_name = p_event1 order by snap_id) a, (select snap_id, last_value(snap_id) over (order by snap_id rows between 1 preceding and current row) snap_id1, time_waited_micro - min(time_waited_micro) over (order by snap_id rows between 1 preceding and current row) val2 from dba_hist_system_event where event_name = p_event2 order by snap_id) b where a.snap_id = b.snap_id; return l_num; end; /