-----------------------------------------------------------
-- sessions Waits profile (in seconds)
--  (view sysmaster:sysseswts)
--      IDS 7.3+  9.2+
--
-- V.Shulzhenko  DBA Tools   04'02 - 05'04
-----------------------------------------------------------
set isolation to dirty read;
select current year to second - 
    EXTEND(dbinfo('utc_to_datetime',sh_pfclrtime),year to second)  ____statistic_time
    ,sh_curtime-sh_pfclrtime    __stat_time_in_sec
from sysshmvals;
----------------------------------------
select  a.sid                           sid
        ,c.txt[ 1 , 14 ]                    reason
        ,round(b.wcumtime/ 1000000 )      cumtime_sec
        ,round(b.wmaxtime/ 1000000 )      maxtime_sec
        ,b.wnum                         num_waits
from sysrstcb a, systwaits b, flags_text c
where a.tid = b.tid
      and b.wreason = c.flags
      and c.tabname = 'systwaits'
      and b.wnum > 0 
      and b.wreason !=  10 
      and a.sid != dbinfo('sessionid')           -- Exclude this session
order by  3  desc, 5  desc, 1 
into temp _temp_dbatools
;
---------------------------------------
Insert into _temp_dbatools
        values 
        (
          0  
        ,'--------------'
        , 0 
        , 0 
        , 0 
        )
;
---------------------------------------
Insert into _temp_dbatools
select  sid
        ,'-- all reason'        reason
        ,sum(cumtime_sec)       cumtime_sec
        ,sum(maxtime_sec)       maxtime_sec
        ,sum(num_waits)         num_waits
from _temp_dbatools
where sid != 0 
group by  1 
;
---------------------------------------
select * from _temp_dbatools;
---------------------------------------
{
It's not the amount of waits, but the amount of time, threads were waiting for an event.
aio:            waits for handling AIO-requests
lock:           waits for release of locks
mt ready:       waits inside ready queue
checkpoint:     waits for completion of checkpoints
buffer:         waits for buffer access
running:        that's not a wait - it's the running time
}