What is below will show SQL statements for a given window (the last four days in the example below) that are tightly correlated with load average on a server. In general, a statistical correlation greater than 60 means there is an increasing degree of correlation between the two data sets. If one goes up, so does the other.
It ignores PL/SQL blocks, so it only focuses on SELECT, INSERT, UPDATE, and DELETE statements.
FWIW, an excellent write up on load average can be found at http://www.linuxjournal.com/article/9001. A hint is that it is not always user CPU utilization that drives up load average. It is often high I/O, or poor average I/O response time that is the issue.
set serveroutput on size unlimited
declare
cursor top_sql_execs is
select distinct sql_id
from (select dhss.sql_id,iowait_delta
from dba_hist_sqlstat dhss, dba_hist_sqltext dhst
where snap_id >= (select min(snap_id)
from dba_hist_snapshot
where begin_interval_time > sysdate - 4)
and dhss.sql_id = dhst.sql_id
and lower(sql_text) not like 'begin%'
order by 2 desc)
where rownum <= 10;
l_corr number;
begin
for cur in top_sql_execs loop
select round((corr(val1,val2) * 100),0)
into l_corr
from (select snap_id,
instance_number,
iowait_delta val1
from dba_hist_sqlstat
where sql_id = cur.sql_id) a,
(select snap_id,
instance_number,
value val2
from dba_hist_osstat
where stat_name = 'LOAD') b
where a.snap_id = b.snap_id
and a.instance_number = b.instance_number;
dbms_output.put_line(cur.sql_id || ' ' || l_corr);
end loop;
end;
/