从v$SESSMETRIC视图中选出当前最占用资源的会话：
显示在定义的时间间隔内（默认是15秒）最占用物理读取，cpu使用和逻辑读取的会话。
select
to_char(m.END_TIME,'DD-MON-YYYY HH24:MI:SS') e_dttm,
m.INTSIZE_CSEC/100 ints,
s.USERNAME usr,
m.SESSION_ID sid,
m.SESSION_SERIAL_NUM ssn,
ROUND (m.CPU) cpu100,
m.PHYSICAL_READS prds,
m.LOGICAL_READS lrds,
m.PGA_MEMORY,
m.HARD_PARSES hp,
m.SOFT_PARSES sp,
m.PHYSICAL_READ_PCT prp,
m.LOGICAL_READ_PCT lrp,
s.SQL_ID
from
v$sessmetric m,v$session s
where
(m.PHYSICAL_READS>100
or m.CPU>100
or m.LOGICAL_READS>100)
and m.session_id=s.SID
and m.SESSION_SERIAL_NUM=s.SERIAL#
order by m.PHYSICAL_READS DESC,m.CPU desc,m.LOGICAL_READS desc;
查看可用的AWR快照
select
hs.snap_id,
to_char(hs.begin_interval_time,'dd-mon-yyyy hh24:mi:ss') b_dttm,
to_char(hs.end_interval_time,'dd-mon-yyyy hh24:mi:ss') e_dttm
from
dba_hist_snapshot hs
where
hs.begin_interval_time>trunc(sysdate);
从DBA_HIST_SQLSTAT视图中选出最占用资源的查询
select hs.snap_id,hs.disk_reads_delta,hs.executions_delta,hs.disk_reads_delta/decode(hs.executions_delta,0,1,hs.executions_delta)
from
dba_hist_sqlstat hs
where hs.disk_reads_delta>10000
order by hs.disk_reads_delta desc;
查出如果同一个SQL_ID出现在不同的快照，次数最多，就说明该查询时间最长。还可以过滤其他标准，包括disk_reads,buffers_gets,rows_processed,cpu_time,iowait等。
select hq.command_type,hq.sql_text
from dba_hist_sqltext hq
where
hq.sql_id='';
查询出有问题sql_id的语句。
存在问题的SQL的执行计划也能被捕获
select *
from table(dbms_xplan.display_awr('');(sql_id)
