Top SESSION

-- (c) Kyle Hailey 2007

col name for a12
col program for a25
col CPU for 9999
col IO for 9999
col TOTAL for 99999
col WAIT for 9999
col user_id for 99999
col sid for 9999

set linesize 120

select
       decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
                                                       "STATUS",
       topsession.sid             "SID",
       u.username  "NAME",
       topsession.program                  "PROGRAM",
       max(topsession.CPU)              "CPU",
       max(topsession.WAIT)       "WAITING",
       max(topsession.IO)                  "IO",
       max(topsession.TOTAL)           "TOTAL"
       from (
select * from (
select
    ash.session_id sid,
    ash.session_serial# serial#,
    ash.user_id user_id,
    ash.program,
    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(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10
  )    topsession,
       v$session s,
       all_users u
  where
       u.user_id =topsession.user_id and
       /* outer join to v$session because the session might be disconnected */
       topsession.sid         = s.sid         (+) and
       topsession.serial# = s.serial#   (+)
  group by  topsession.sid, topsession.serial#,
            topsession.user_id, topsession.program, s.username,
            s.sid,s.paddr,u.username
  order by max(topsession.TOTAL) desc
/