使用Statspack进行Oracle性能分析

select w.snap_id,
       sn.SNAP_TIME,
       w.event event,
       w.total_waits - w.last_wait waits,
       w.total_timeouts - w.last_timeout timeouts,
       (w.time_waited - w.last_time_waited)/1000000 time_waited_S
from  perfstat.stats$snapshot sn,
      (select snap_id, dbid, instance_number, event,
              total_waits,
              lag(total_waits, 1) over (order by event, snap_id) last_wait,
              total_timeouts,
              lag(total_timeouts, 1) over (order by event, snap_id) last_timeout,              time_waited_micro time_waited,
              lag(time_waited_micro, 1) over (order by event, snap_id)
last_time_waited       from perfstat.stats$system_event
       where    event not in (select event from perfstat.stats$IDLE_EVENT)
       and    snap_id in (select snap_id from stats$snapshot where snap_time >sysdate -300) ----300 days
       ) w
where w.time_waited - w.last_time_waited > 0
and   w.snap_id > (select min(snap_id) from stats$snapshot where snap_time >sysdate -300)  ----300 days
--and   SN.snap_id in (22391,22392,22393)
and   sn.snap_id = w.snap_id
and   sn.dbid = w.dbid
and   sn.instance_number = w.instance_number
and   event not like '%message%'
and   event not like 'SQL*Net%'
and   (w.time_waited - w.last_time_waited) > 600*1000000 ---- wait time >10 min
UNION ALL
select   ss.snap_id, sn.SNAP_TIME, 'CPU TIME' ,0, 0,ROUND((ss.value - lag(ss.value,1) over (order by ss.snap_id))/100,0) as value_cputime
from     perfstat.stats$sysstat ss, perfstat.stats$snapshot sn
where    ss.snap_id = sn.snap_id
and      ss.name = 'CPU used by this session'
and ss.snap_id in (select snap_id from stats$snapshot where snap_time >sysdate -300)
--and ss.snap_id in (22391,22392,22393)
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
order by 1, 6 desc