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;
/