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.<\/p>\n
\r\ncreate or replace function corr_events (p_event1 in varchar2,\r\n p_event2 in varchar2)\r\n return number is\r\n l_num number;\r\nbegin\r\n select round((corr(val1,val2) * 100),0)\r\n into l_num\r\n from (select snap_id,\r\n last_value(snap_id)\r\n over (order by snap_id\r\n rows between 1 preceding and current row) snap_id1,\r\n time_waited_micro - min(time_waited_micro)\r\n over (order by snap_id\r\n rows between 1 preceding and current row) val1\r\n from dba_hist_system_event\r\n where event_name = p_event1\r\n order by snap_id) a,\r\n (select snap_id,\r\n last_value(snap_id)\r\n over (order by snap_id\r\n rows between 1 preceding and current row) snap_id1,\r\n time_waited_micro - min(time_waited_micro)\r\n over (order by snap_id\r\n rows between 1 preceding and current row) val2\r\n from dba_hist_system_event\r\n where event_name = p_event2\r\n order by snap_id) b\r\n where a.snap_id = b.snap_id;\r\n return l_num;\r\nend;\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n