break on host
column host format a10
column event format a35
column ass format 999.99
select  db.host, nvl(n.name,'CPU') event, act.aas ass, cnt
from  (
   SELECT
        distinct ash.dbid, event#,
          count(event#)
           OVER (partition by dbid,event# ORDER BY event#
                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) cnt,
        round(
          count(event#)
           OVER (partition by dbid,event# ORDER BY event#
                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) /
          nullif(
            LAST_VALUE(sample_id)
             OVER (partition by dbid ORDER BY sample_id
                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
               -
            FIRST_VALUE(sample_id)
             OVER (partition by dbid ORDER BY sample_id
                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
           ,0)
         , 2.2) aas
    from
        ( select
                   dbid,
                   sample_id,
                   sample_time,
                   decode(session_state,'ON CPU',-1,event#) event#
                   from v$ash_all
           where  session_type!=81)   ash
    where
        sample_time >= sysdate - 15/(24*60)
    and sample_time <= sysdate
   )  act,
   V$ASH_EVENT_NAMES_ALL n,
   dbids db
where
    act.event# = n.event#  (+)
and act.dbid    = n.dbid (+)
and db.dbid = act.dbid (+)
order by db.dbid, act.aas
/


select sql_id
     , ltrim(address,'0') address
     , sum(cnt) tot
     , round(max(decode(seq,1,pct,null)),2) fpct
     , max(decode(seq,1,wait,null)) first
     , round(max(decode(seq,2,pct,null)),2) spct
     , max(decode(seq,2,wait,null)) second
     , round(max(decode(seq,3,pct,null)),2) tpct
     , max(decode(seq,3,wait,null)) third
  from
  (
     select
            sql_id
          , address
          , wait
          , cnt
          , row_number() over ( partition by sql_id order by cnt desc ) seq
          , ratio_to_report( sum(cnt)) over ( partition by sql_id ) pct
     from (
            select
               ash.SQL_ID sql_id
             , ash.sql_address address
             , count(*) cnt
             , substr(decode(ash.session_state,'ON CPU','ON CPU',n.name),0,15) wait
            from v$ash ash,
                 v$ash_event_names n
            where SQL_ID is not NULL
              and n.event# = ash.event#
              and SQL_ID > 0
              and sample_time >= sysdate - (&minutes/(24*60))
            group by sql_id, sql_address, decode(ash.session_state,'ON CPU','ON CPU',n.name)
           )
      group by sql_id,address, wait,cnt
  )
  where seq <= 3
  group by sql_id,address
  order by sum(cnt)
/



select * from (
   select
        to_char(trunc(sample_time,'HH')+
             (trunc((sample_time-trunc(sample_time,'HH'))*24/(&v_mins/60))+1)/(60/&v_mins)/24 ,'MON-DD HH:MI') start
_time,
        count(*),
        sql_id,
        fixed_table_sequence,
        rank()
          over (
            partition by
                    trunc(sample_time,'HH')+
             (trunc((sample_time-trunc(sample_time,'HH'))*24/(&v_mins/60))+1)/(60/&v_mins)/24
            order by count(*) desc ) topn
   from v$ash
   where
          sql_id > 0
     and  sql_id is not null
     and  sample_time > sysdate - (&minutes)/(24*60)
   group by
            sql_id,
            fixed_table_sequence,
            trunc(sample_time,'HH')+
            (trunc((sample_time-trunc(sample_time,'HH'))*24/(&v_mins/60))+1)/(60/&v_mins)/24
)
where  topn < 3
/