SQL – AWR TOP 5 EVENTS

WITH CPU_TIME
AS
(SELECT snap_id, end_interval_time, instance_number, stat_id, stat_name, value
            FROM (SELECT /*+ leading(s,sn,sy) */
                         s.snap_id,
                         s.instance_number,
                         s.dbid,
                         s.end_interval_time,
                         sy.stat_id,
                         sy.stat_name,
                         CASE
                            WHEN s.begin_interval_time = s.startup_time
                            THEN sy.value
                            ELSE sy.value – LAG (sy.value, 1)
                                            OVER (PARTITION BY
                                                  sy.stat_id, sy.instance_number,
                                                  sy.dbid, s.startup_time
                                            ORDER BY sy.snap_id)
                         END value
                    FROM dba_hist_snapshot s,
                         dba_hist_sys_time_model sy
                   WHERE s.dbid = sy.dbid
                     AND s.instance_number = sy.instance_number
                     AND s.snap_id = sy.snap_id
                     AND s.instance_number = 1
                     AND s.end_interval_time > TO_TIMESTAMP (:start_time, ‘yyyymmdd hh24mi’)
                     AND s.end_interval_time < TO_TIMESTAMP (:end_time, ‘yyyymmdd hh24mi’)
                     — this ensures hourly data are used
                     AND to_char(trunc(s.end_interval_time, ‘mi’), ‘mi’) = ’00’
                     AND sy.stat_name= ‘DB CPU’)),
WAIT_TIME
AS
(SELECT snap_id, end_interval_time, instance_number, event_name, time_waited_micro
            FROM (SELECT s.snap_id,
                         s.instance_number,
                         s.dbid,
                         s.end_interval_time,
                         sy.event_name,
                         CASE
                            WHEN s.begin_interval_time = s.startup_time
                            THEN sy.time_waited_micro
                            ELSE sy.time_waited_micro – LAG (sy.time_waited_micro, 1)
                                                        OVER (PARTITION BY
                                                              sy.event_id, sy.instance_number,
                                                              sy.dbid, s.startup_time
                                                        ORDER BY sy.snap_id)
                         END time_waited_micro
                    FROM dba_hist_snapshot s,
                         dba_hist_system_event sy
                   WHERE s.dbid = sy.dbid
                     AND s.instance_number = sy.instance_number
                     AND s.snap_id = sy.snap_id
                     AND s.instance_number = 1
                     AND s.end_interval_time > TO_TIMESTAMP (:start_time, ‘yyyymmdd hh24mi’)
                     AND s.end_interval_time < TO_TIMESTAMP (:end_time, ‘yyyymmdd hh24mi’)
                     AND sy.wait_class != ‘Idle’
                     — this ensures hourly data are used
                     AND to_char(trunc(s.end_interval_time, ‘mi’), ‘mi’) = ’00’))
SELECT end_interval_time, event, time_seconds
  FROM (SELECT end_interval_time, instance_number, event, time_seconds, dense_rank() over (partition by snap_id order by time_seconds desc) rango
          FROM (SELECT snap_id, TO_CHAR(TRUNC (end_interval_time, ‘mi’), ‘mm-dd hh24mi’) end_interval_time,
                       instance_number, stat_name event, round(value/1000000, 2) time_seconds
                  FROM cpu_time
                 WHERE value is not null
                UNION ALL
                SELECT snap_id, TO_CHAR(TRUNC (end_interval_time, ‘mi’), ‘mm-dd hh24mi’) end_interval_time,
                       instance_number, event_name event, round(time_waited_micro/1000000, 2) time_seconds
                  FROM wait_time
                 WHERE time_waited_micro is not null))
 WHERE rango <= 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