top sessions

 http://db.geeksinsight.com/tools-scripts/

REF:

set pagesize 100
set linesize 250
col INSTANCE_NUMBER format 99
col SESSION_ID format 99999
col BLOCKING_INST_ID format 99
col event format a20
col WAIT_CLASS format a15
col PROGRAM format a20
col MODULE format a16
col p3 format 99999999999999999999
select sql_id,to_char(SAMPLE_TIME,'YYYYMMDD-HH24:MI:SS'),
       INSTANCE_NUMBER,SESSION_ID,PROGRAM,MODULE,EVENT,
       CURRENT_OBJ#,WAIT_CLASS,BLOCKING_INST_ID,
       BLOCKING_SESSION, p3
from dba_hist_active_sess_history
where INSTANCE_NUMBER= 3 
and SESSION_ID = 2437 
and sample_time between to_date('16-06-18 11:00:00','DD-MM-YY HH24:MI:SS')
and to_date('16-06-18 11:04:00','DD-MM-YY HH24:MI:SS')
and event ='library cache lock'
order by sample_id;

col USERNAME for a10
col machine for a30
col Text for a50 wrap on
select 
se.SID, ss.serial#,
 ss.username, 
 ss.machine,
 ss.sql_id,
 to_char(s.last_active_time,'DD-MON-YY HH:MI:SS'),
 s.last_load_time, 
 VALUE/100 cpu_secs ,
 substr(s.sql_text,1,50) Text
from 
 v$session ss, 
 v$sesstat se, 
 v$statname sn,
 v$sql s
where 
 se.STATISTIC# = sn.STATISTIC# 
and 
 NAME like '%CPU used by this session%' 
and 
 se.SID = ss.SID 
--and 
 --ss.status='ACTIVE' 
--and 
-- ss.username is not null  and ss.username!='DBMANAGER'
and 
 ss.sql_id=s.sql_id
order by VALUE desc;


SH TOP Queries
 
select
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
"STATUS",
topsession.session_id             "SESSION_ID",
topsession.sample_time    "SAMPLE_TIME",
u.name                                         "NAME",
topsession.program                  "PROGRAM",
max(topsession.CPU)               "CPU",
max(topsession.WAITING)       "WAITING",
max(topsession.IO)                   "IO",
max(topsession.TOTAL)            "TOTAL"
from (   select
ash.session_id,
to_char(ash.sample_time,'DD-MON-YYYY HH24:MI') ,
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))    -
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0))    "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0))    "IO" ,
sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash,
v$event_name 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,
v$session s,
user$ u
where
u.user# =topsession.user_id and
/* outer join to v$session because the session might be disconnected */
topsession.session_id         = s.sid         (+) and
topsession.session_serial# = s.serial#   (+) and  to_char(topsession.sample_time,'DD-MON-YYYY HH24:MI')  between '19-AUG-2016 03' and '19-AUG-2016 04'
group by  topsession.session_id, topsession.session_serial#, topsession.user_id,
topsession.program, s.username,s.sid,s.paddr,u.name,topsession.sample_time
order by max(topsession.TOTAL) desc
Top Session consuming CPU last N minutes
Select
session_id,
count(*)
from
v$active_session_history
where
session_state= 'ON CPU' and
SAMPLE_TIME > sysdate – (&minutes/(24*60))
group by
session_id
order by
count(*) desc;
 
Top Waiting Session  in last 5 minutes
Select                        session_id,
count(*)
from
v$active_session_history
where
session_state='WAITING'  and
SAMPLE_TIME >  SYSDATE - (&minutes/(24*60))
group by
session_id
order by
count(*) desc;
 
Top Session Consuming CPU, IO and Waiting last n minutes
select
ash.session_id,
ash.session_serial#,ash.sql_id,
ash.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(en.wait_class,'User I/O',1, 0 ), 0))    "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0))    "IO" ,
sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash,
v$event_name en
where en.event# = ash.event# and  SAMPLE_TIME >  SYSDATE - (&minutes/(24*60))
group by sql_id,session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1));