def backMin=3

*** Profile
***

def backMin=3
def ashcolkey=flags
def ashcolval='%'

def ashcolkey=sql_id
def ashcolval='abcdef'

def ashcolkey=session_id
def ashcolval='7'


set tab off
set echo off
set verify off
set linesize 200
col event format a35 trunc
col program format a25 trunc

select totAS,
       oncpu/(oncpu+waiting) cpuPct,
       waiting/(oncpu+waiting) waitingPct,
       (userIO+sysIO)/waiting ioPct,
       (waiting-userIO-sysIO)/waiting otherPct
from   (
         select count(*) totAS,
                sum(decode(session_state,'ON CPU',1,0)) oncpu,
                sum(decode(session_state,'WAITING',1,0)) waiting,
                sum(decode(session_state,'WAITING',decode(wait_class,'User I/O',1,0))) userIO,
                sum(decode(session_state,'WAITING',decode(wait_class,'System I/O',1,0))) sysIO
         from   v$active_session_history
         where  sample_time >= current_timestamp - interval '&backMin' minute
           and  &ashcolkey like '&ashcolval%'
       ) a
/
select count(*), event
from   v$active_session_history
where  sample_time >= current_timestamp - interval '&backMin' minute
  and  session_state = 'WAITING'
  and  &ashcolkey like '&ashcolval%'
group by event
order by 1 desc
/



*** Let's get the TOP ANYTHING
***

def ashCols='SQL_ID'
def ashCols='session_id,session_type'
def ashCols='program'

def ashstate='ON CPU'
def ashstate='WAITING'

select count(*), &ashCols
from   v$active_session_history
where  sample_time >= current_timestamp - interval '&backMin' minute
  and  session_state = '&ashstate'
group by &ashCols
order by 1 desc
/


*** Let's watch (timeline) an indiviual session!
***

col sid format 9999
col blocking_session heading 'BLK SID'
def ashCols='sql_id,blocking_session,program'
def ashWhere='session_id=131’

select sample_id, to_char(sample_time,'DD HH24:MI:SS') DT,
       session_id sid, session_state,
       &ashCols
from   v$active_session_history
where  sample_time >= current_timestamp - interval '&backMin' minute
  and  &ashWhere
order by 1,3
/