Topsesl.sql

-- (c) Kyle Hailey 2007

column status  format a6
column sid  format 99999
column name  format a14
column PROGRAM  format a28
column CPU  format 99999
column WAIT  format 99999
column TOTAL  format 99999

select
       /* if  sid not found in v$session then  disconnected */
       topsession.session_id             "SID",
       u.username  "NAME",
       topsession.program                  "PROGRAM",
       max(topsession.CPU)              "CPU",
       max(topsession.WAITING)       "WAIT",
       max(topsession.TOTAL)           "TOTAL"
       from (
           select
                ash.session_id,
                ash.session_serial#,
                ash.user_id,
                ash.program,
                sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
                sum(decode(ash.session_state,'WAITING',1,0))    "WAITING" ,
                count(*)    "TOTAL"
           from sash ash,
                sash_event_names en
           where en.event# = ash.event#
           group by session_id,user_id,session_serial#,program
           order by sum(decode(session_state,'ON CPU',1,1))
             )        topsession,
                      sash_users u
  where
                   u.user_id =topsession.user_id
  group by
             topsession.session_id,
             topsession.session_serial#,
             topsession.user_id,
             topsession.program, u.username
  order by max(topsession.TOTAL)

/