# ASH script

-- Top 3 wait evnet
      
select ashh.ashtime, inst_id,
max(decode(seq,1, ashh.wait_event||' -> ' ,''))|| max(decode(seq,2, ashh.wait_event||' -> ',''))|| max(decode(seq,3, ashh.wait_event||' ','')) wait_evnt ,
max(decode(seq,1, ashh.session_cnt||' -> ' ,''))|| max(decode(seq,2, ashh.session_cnt||' -> ',''))|| max(decode(seq,3, ashh.session_cnt||' ','')) wait_evnt_cnt
  from (select /*+ parallel 4 */
         to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI') ashtime,
         ash.instance_number inst_id,
         decode(ash.event, null, 'CPU Time', ash.event) wait_event,
         ash.session_state session_state,
         count(*) session_cnt,
         row_number() over(partition by instance_number, to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI') order by count(*) desc) seq
          from dba_hist_active_sess_history ash
         where ash.sample_time between
               to_date('04/17 19:30', 'MM/DD HH24:MI') and
               to_date('04/18 06:00', 'MM/DD HH24:MI')
         group by to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI'),
                  ash.instance_number,
                  session_state,
                  ash.event) ashh
 where ashh.seq <= 3
 group by ashh.ashtime, inst_id
 order by 2,1   ;
 
 
-- Top SQL
 
select A.*
from (
select to_char(sample_time, 'YYYY-MM-DD HH24:MI') HIS_TIME, instance_number inst_id,
       row_number () over (partition by  to_char(sample_time, 'YYYY-MM-DD HH24:MI'),instance_number  order by sum(decode(ash.session_state,'ON CPU',1,1)) desc ) as SEQ,
       decode(ash.sql_id,null, program, ash.sql_id)  sql_id,ash.SQL_PLAN_HASH_VALUE Plan_hash,
       ash.sql_opname type,
       sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
       sum(decode(ash.session_state,'WAITING',1,0))    -
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "WAIT" ,
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "IO" ,
       sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from dba_hist_active_sess_history ash 
 where ash.sample_time between to_date('04/17 20:00', 'MM/DD HH24:MI') and to_date('04/18 06:00', 'MM/DD HH24:MI')
group by instance_number,   to_char(sample_time, 'YYYY-MM-DD HH24:MI') ,  decode(ash.sql_id,null, program, ash.sql_id)  ,ash.SQL_PLAN_HASH_VALUE ,ash.sql_opname ) A
where A.seq <=5
order by inst_id, HIS_TIME ;
  
 
-- gc wait event Top object
 
select A.*
from (
select instance_number inst_id , substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0' ash_time,
      decode(o.owner||'.'||object_name||'.'||object_type,'..',ash.program,o.owner||'.'||object_name||'.'||object_type) objname ,
--    ash.event, 
       round(ratio_to_report(count(*))  over(partition by substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0'  ) * 100,1) as raitio ,
       row_number () over (partition by substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0' order by sum(decode(ash.session_state,'ON CPU',1,1))  desc ) as SEQ,
       sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
       sum(decode(ash.session_state,'WAITING',1,0))    -
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "WAIT" ,
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "IO" ,
       sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from dba_hist_active_sess_history  ash , dba_objects o
where sample_time between to_date('04/18 02:00', 'MM/DD HH24:MI') and to_date('04/18 05:30', 'MM/DD HH24:MI')
and ash.current_obj#=o.object_id(+)
and ash.event like 'gc%'
group by instance_number ,  substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0' ,decode(o.owner||'.'||object_name||'.'||object_type,'..',ash.program,o.owner||'.'||object_name||'.'||object_type) 
order by substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0'  , sum(decode(ash.session_state,'ON CPU',1,1)) desc ) A
where A.seq <= 3
 
 
 
-- gc wait event Top sql by object
select A.*
from (
select substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0' HIS_TIME, instance_number inst_id,
       row_number () over (partition by substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0'  order by sum(decode(ash.session_state,'ON CPU',1,1)) desc ) as SEQ,
       decode(ash.sql_id,null, program, ash.sql_id)  sql_id,ash.SQL_PLAN_HASH_VALUE Plan_hash,
       ash.sql_opname type,
       sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
       sum(decode(ash.session_state,'WAITING',1,0))    -
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "WAIT" ,
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "IO" ,
       sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from dba_hist_active_sess_history ash   , dba_objects o
 where sample_time between to_date('04/18 02:00', 'MM/DD HH24:MI') and to_date('04/18 05:30', 'MM/DD HH24:MI')
 and ash.current_obj#=o.object_id(+)
 and o.owner||'.'||object_name||'.'||object_type='MFGDEV.FUT_PL_MESSAGE.TABLE'
group by instance_number, substr(to_char(sample_time, 'YYYY-MM-DD HH24:MI'),1,15)||'0',  decode(ash.sql_id,null, program, ash.sql_id)  ,ash.SQL_PLAN_HASH_VALUE ,ash.sql_opname ) A
where A.seq <=3
order by inst_id, HIS_TIME
 
 
 
-- Event Top Session
 
 
select MIN(to_char(ash.sample_time, 'MM-DD_HH24:MI:SS')) sess_start_time,
       MAX(to_char(ash.sample_time, 'MM-DD_HH24:MI:SS')) sess_end_time,
       ((CAST(MAX(ash.sample_time) AS DATE)) - (CAST(MIN(ash.sample_time) AS DATE))) * (3600 * 24) active_secs,
        ash.SESSION_ID||','||ash.SESSION_SERIAL#||'@'||ash.instance_number  ash_sess_info,u.username username ,
        ash.sql_id SQL_ID,  ash.SQL_OPNAME sql_type, o.owner||'.'||o.object_name||'.'||o.object_type curobj, ash.current_file# curfile, ash.current_block# curblock,
        ash.blocking_SESSION||','||ash.blocking_SESSION_SERIAL#||'@'||ash.blocking_inst_id  blocking_sess_info ,
       sum(decode(ash.session_state,'ON CPU',1,0))     CPU_TIME,
       sum(decode(ash.session_state,'WAITING',1,0))    -
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    WAIT_TIME ,
       sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    IO_TIME ,
       sum(decode(ash.session_state,'ON CPU',1,1))    DB_TIME --- select *
 from dba_hist_active_sess_history ash ,dba_users u , dba_objects o
 where ash.sample_time between to_date('04/17 20:00', 'MM/DD HH24:MI') and to_date('04/18 05:40', 'MM/DD HH24:MI')
   -- and instance_number=4
   and event like 'gc remaster%'
   and u.user_id=ash.user_id
   and ash.current_obj#=o.object_id
group by ash.SESSION_ID||','||ash.SESSION_SERIAL#||'@'||ash.instance_number ,
ash.blocking_SESSION||','||ash.blocking_SESSION_SERIAL#||'@'||ash.blocking_inst_id,
ash.sql_id,  ash.SQL_OPNAME ,u.username ,o.owner||'.'||o.object_name||'.'||o.object_type , ash.current_file#, ash.current_block#
 
 
 
-- Top blocked
select t.lv,
       t.iscycle,
       t.dbid,
       t.sample_id,
       to_char(t.sample_time,'YYYY-DD-MM HH24:MI:SS') ash_time,
       t.instance_number,
       t.session_id,
       t.sql_id,
       t.session_type,
       t.event,
       t.seq#,
       t.session_state,
       t.blocking_inst_id,
       t.blocking_session,
       t.blocking_session_status,
       t.c blocking_session_count
  from (select t.*,
               row_number() over(partition by dbid, instance_number, sample_time order by c desc) r
          from (select t.*,
                       count(*) over(partition by dbid, instance_number, sample_time, session_id) c,
                       row_number() over(partition by dbid, instance_number, sample_time, session_id order by 1) r1
                  from (select /*+ parallel 8 */
                         level              lv,   
                         connect_by_isleaf  isleaf,
                         connect_by_iscycle iscycle,
                         t.*
                          from dba_hist_active_sess_history t
                          where t.sample_time between to_date('04/17 08:00', 'MM/DD HH24:MI') and to_date('04/18 05:40', 'MM/DD HH24:MI')
                            -- and t.p1=1618 and t.p2=488394
                            --  and t.instance_number =4
                         start with blocking_session is not null
                        connect by nocycle
                         prior dbid = dbid
                               and prior sample_time = sample_time
                       and ((prior sample_time) - sample_time between interval '-1' second and interval '1' second)
                               and prior blocking_inst_id = instance_number
                               and prior blocking_session = session_id
                               and prior blocking_session_serial# = session_serial#) t
                 where t.isleaf = 1) t
         where r1 = 1) t
 where r < 3
 order by dbid, sample_time, r;