Top 5 Timed Events (FG & BG) from AWR Repository by SNAP_ID for a given snapshot range

top-n-timed-events-by-snapid-awr.sql

----------------------------------------------------------------------------------------
--
-- File name:   top-n-timed-events-by-snapid-awr.sql
-- Purpose:     List the top N events per snapshot from the AWR repository, by snap_id. 
--              The data is aggregated for all nodes in a cluster, though it wouldn't
--              be hard to add instance_number as a dimension.
--
-- Author:      Tyler D. Muth
--
-- Usage:       There are 3 bind variables in this script, all are required:
--
--              dbid: The databases ID. This is in case there are multiple AWR repositories
--                    in the databases.
--
--              bid:  begin snapshot ID
--
--              bid:  end snapshot ID
--
--
---------------------------------------------------------------------------------------

SELECT snap_id,
  wait_class,
  event_name,
  pctdbt,
  total_time_s
FROM
  (SELECT a.snap_id,
    wait_class,
    event_name,
    b.dbt,
    ROUND(SUM(a.ttm) /b.dbt*100,2) pctdbt,
    SUM(a.ttm) total_time_s,
    dense_rank() over (partition BY a.snap_id order by SUM(a.ttm)/b.dbt*100 DESC nulls last) rnk
  FROM
    (SELECT snap_id,
      wait_class,
      event_name,
      ttm
    FROM
      (SELECT
        /*+ qb_name(systemevents) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        wait_class,
        e.event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN e.time_waited_micro
          ELSE e.time_waited_micro - lag (e.time_waited_micro ) over (partition BY e.instance_number,e.event_name order by e.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_system_event e
      WHERE s.dbid          = e.dbid
      AND s.dbid            = :dbid
      AND s.instance_number = e.instance_number
      AND s.snap_id         = e.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND e.wait_class != 'Idle'
      UNION ALL
      SELECT
        /*+ qb_name(dbcpu) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        t.stat_name wait_class,
        t.stat_name event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
      AND s.dbid            = :dbid
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND t.stat_name = 'DB CPU'
      )
    ) a,
    (SELECT snap_id,
      SUM(dbt) dbt
    FROM
      (SELECT
        /*+ qb_name(dbtime) */
        s.snap_id,
        t.instance_number,
        t.stat_name nm,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END dbt
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
      AND s.dbid            = :dbid
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND t.stat_name = 'DB time'
      ORDER BY s.snap_id,
        s.instance_number
      )
    GROUP BY snap_id
    HAVING SUM(dbt) > 0
    ) b
  WHERE a.snap_id = b.snap_id
  GROUP BY a.snap_id,
    a.wait_class,
    a.event_name,
    b.dbt
  )
WHERE pctdbt > 0
AND rnk     <= 5
ORDER BY snap_id,
  pctdbt DESC; 

Example output:

SNAP_ID WAIT_CLASS     EVENT_NAME                                               PCTDBT TOTAL_TIME_S
------- -------------- ------------------------------------------------------- ------- ------------
    613 DB CPU         DB CPU                                                    90.44    205559579 
    613 Other          Streams AQ: qmn coordinator waiting for slave to start    10.91     24796767 
    613 User I/O       cell smart table scan                                      7.72     17554828 
    613 Other          CSS operation: data query                                  5.68     12900496 
    613 User I/O       Disk file Mirror Read                                      1.48      3355493 
    614 DB CPU         DB CPU                                                     89.5    186190544 
    614 Other          Streams AQ: qmn coordinator waiting for slave to start     10.7     22264691 
    614 Other          CSS operation: data query                                  6.24     12974465 
    614 User I/O       cell smart table scan                                      4.74      9859266 
    614 User I/O       cell multiblock physical read                               2.1      4358722 
    615 DB CPU         DB CPU                                                     68.4    472562968 
    615 User I/O       cell smart table scan                                     11.81     81562192 
    615 User I/O       cell multiblock physical read                              10.7     73947510 
    615 Other          Streams AQ: qmn coordinator waiting for slave to start     5.06     34937692 
    615 Concurrency    cursor: pin S wait on X                                    4.65     32109388