SQL – STATSPACK TOP 5 EVENTS

with top_events as (
select snap_id, stime, name, null waits, round(diff/100) time
from (
    select  c.snap_id,
            to_char(c.snap_time, ‘yyyymmdd hh24’) stime,
            b.name,
            b.value,
            lag(b.value) over (partition by name order by b.snap_id) last_val,
            b.value – lag(b.value) over (partition by name order by b.snap_id) diff
    from    stats$sysstat b,
            stats$snapshot c
    where   c.snap_id = b.snap_id
    and     b.name = ‘CPU used by this session’
    and     c.snap_time between to_date(:start_date, ‘yyyymmdd hh24miss’)
                            and to_date(:end_date, ‘yyyymmdd hh24miss’)
    order by 3, 1)
where last_val is not null
union all
select snap_id, stime, event, waits, wait_seconds time
from (
     select snap_id, stime, event, rank() over (partition by snap_id order by diff_wait_time_micro desc) ranking,
            round(diff_waits) waits,
            round(diff_wait_time_micro/1000000) wait_seconds
       from (
         select  c.snap_id,
                 to_char(c.snap_time, ‘yyyymmdd hh24’) stime,
                 b.event,
                 b.total_waits,
                 lag(b.total_waits) over (partition by b.event order by b.snap_id) last_total_waits,
                 b.time_waited_micro,
                 lag(b.time_waited_micro) over (partition by b.event order by b.snap_id) last_total_time_micro,
                 b.total_waits – lag(b.total_waits) over (partition by b.event order by b.snap_id) diff_waits,
                 b.time_waited_micro – lag(b.time_waited_micro) over (partition by b.event order by b.snap_id) diff_wait_time_micro,
                 d.wait_class
         from    stats$system_event b,
                 stats$snapshot c,
                 v$system_event d
         where   c.snap_id = b.snap_id
         and     b.event_id = d.event_id
         and     d.wait_class != ‘Idle’
         and     c.snap_time between to_date(:start_date, ‘yyyymmdd hh24miss’)
                                 and to_date(:end_date, ‘yyyymmdd hh24miss’)
         order by 3, 1)
     where diff_waits is not null
)
order by 1, 5 desc)
select b.*
  from (select row_number() over (partition by snap_id order by time desc) rn, a.*
          from top_events a) b
 where rn <= 5;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s