Oracle Wait last 5 Min ..

-----------------------------------------
--
-- Top 10 CPU consumers in last 5 minutes
--
-----------------------------------------
select * from
(
select inst_id,session_id, session_serial#, count(*) from gv$active_session_history
where session_state= 'ON CPU' 
and sample_time > sysdate - interval '5' minute
-- and USER_ID not in ( select user_id from dba_users where username not in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
--                    )
group by inst_id,session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;

--------------------------------------------
--
-- Top 10 waiting sessions in last 5 minutes
--
--------------------------------------------

select * from
(
select inst_id ,session_id, session_serial#,count(*) from gv$active_session_history
where session_state='WAITING'  
and sample_time >  sysdate - interval '5' minute
-- and USER_ID not in ( select user_id from dba_users where username not in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' ,'PUBLIC','WWV_FLOW_PLATFORM' )
--                    )
group by inst_id,session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;







set lines 200 pagesize 200 
col username for a10
col osuser   for a10
col machine for a10
col program for a20
col client_info for a10

col resource_consumer_group for a10

select  

inst_id ,
serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info,
EVENT,
sql_id 
from gv$session 
where sid=&sid;


=======

set pagesize 200 linesize 200

col sql_id format a15

col inst_id format '9'

col sql_text format a50

col module format a10

col blocker_ses format '999999'

col blocker_ser format '999999'

col kill for a17

SELECT distinct

'''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''' kill ,

a.sql_id ,

--a.inst_id,

a.blocking_session blocker_ses,

a.blocking_session_serial# blocker_ser,

a.user_id,

s.sql_text,

a.module,a.sample_time

FROM GV$ACTIVE_SESSION_HISTORY a,gv$sql s

where a.sql_id=s.sql_id

and blocking_session is not null

and a.user_id <> 0 -- exclude SYS user

and sample_time > sysdate - interval '5' minute

--and a.sample_time >= SYSDATE - 1/24

;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history

WHERE 1=1

--and sample_time > SYSDATE - 1/24

and sample_time > sysdate - interval '5' minute

AND session_type = 'BACKGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history

WHERE 1=1

--and sample_time > SYSDATE - 1/24

and sample_time > sysdate - interval '5' minute

AND session_type = 'FOREGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

SELECT sql_id, COUNT(*) FROM gv$active_session_history ash, gv$event_name evt 

WHERE 1=1

--and ash.sample_time > SYSDATE - 1/24

and sample_time > sysdate - interval '5' minute

AND ash.session_state = 'WAITING'

AND ash.event_id = evt.event_id

AND evt.wait_class = 'User I/O'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

select * from (

        select   SQL_ID ,

                 sum(decode(session_state,'ON CPU',1,0)) as CPU,

                 sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,

                 sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,

                 sum(decode(session_state,'ON CPU',1,1)) as TOTAL

        from gv$active_session_history

        where SQL_ID is not NULL

and sample_time > sysdate - interval '5' minute

        group by sql_id

        order by sum(decode(session_state,'ON CPU',1,1))   desc

        )

where rownum <11

===

 

 prompt  Top Sessions

set linesize 200

col MACHINE for a40

col USERNAME for a20

col kill for a16

select * from (

select

'''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''' kill ,

     u.username,

     a.machine,

 --sum the session states

     sum(decode(a.session_state,'ON CPU',1,0)) CPU,

     sum(decode(a.session_state,'WAITING',1,0)) - sum(decode(a.session_state,'WAITING',decode(wait_class,'User I/O',1, 0 ), 0)) WAIT ,

     sum(decode(a.session_state,'WAITING',decode(wait_class,'User I/O',1, 0 ), 0)) IO,

     sum(decode(session_state,'ON CPU',1,1)) TOTAL

from gv$active_session_history a, dba_users u

where a.user_id = u.user_id

and a.sample_time > sysdate - 5/24/60         --< in the last n minutes

and u.username not in ('DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )

--group by inst_id,session_id,username,session_serial#,machine

group by  '''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''',username,machine

order by sum(decode(session_state,'ON CPU',1,1)) desc

) where rownum < 10

;

prompt Top SQL

set linesize 200

select * from (

select

  --sql details

   '''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''' kill ,

     a.sql_id , 

     a.sql_plan_hash_value phv,

  --sum the session states

     sum(decode(a.session_state,'ON CPU',1,0)) CPU,

     sum(decode(a.session_state,'WAITING',1,0)) - sum(decode(a.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) WAIT,

     sum(decode(a.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) IO,

     sum(decode(a.session_state,'ON CPU',1,1)) TOTAL

from gv$active_session_history a

where sql_id is not null

and a.sample_time > sysdate - 5/24/60        --< in the last n minutes

and user_id not in (select user_id from dba_users where username in ('SYS','SYSTEMS', 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ))

group by '''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''',sql_id, sql_plan_hash_value 

order by sum(decode(session_state,'ON CPU',1,1))   desc

) where  rownum < 10

;

col type for a15

col usernm for a16

col event for a40

col program for a30

col totales for a 10

col info_sesion FOR A20 HEAD '(Inst,SID,SERIAL)'

col totales noprint

select inst_id||' - ('||session_id||'/'||session_serial#||')' as info_sesion,

sql_id,

(select username from dba_users where user_id = a.user_id) usernm,

PROGRAM,

type,

cpu,

wait,

io,

sum(total) over (partition by session_id, session_serial#, sql_id) totales, 

event

from (select ash.inst_id,

               ash.session_id,

               ash.session_serial#,

               ash.user_id,

               ash.SQL_ID,

               substr(ash.program,1,30) as program,

               aud.name type,

               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(ash.session_state, 'ON CPU', 1, 1)) total,

               decode(ash.event, NULL, 'db_cpu', ash.event) event

          from gv$active_session_history ash, audit_actions aud

         where SQL_ID is not NULL

           and ash.sql_opcode = aud.action

           and ash.sample_time > sysdate - 5 / (60 * 24)

   and ash.user_id not in (select user_id from dba_users where username in ('SYS','SYSTEMS', 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ))

         group by inst_id,   ash.session_id,  ash.session_serial#,  user_id,  sql_id,     PROGRAM,    aud.name,   event

         order by sql_id ) a

 where rownum <= 50 

 order by totales desc

;

set linesize 500 pagesize 300

col kill for a17

select

'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' KILL,

ash.con_id,

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",

event

from gv$active_session_history ash,   v$event_name en

where en.event# = ash.event#

-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second

-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY

and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '5' minute

group by '''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''',ash.con_id,ash.sql_id,user_id,program,event

order by sum(decode(session_state,'ON CPU',1,1));

col DB_NAME for A40

col BEGIN_INTERVAL_TIME for a22

col idle for 999

col sys for 999

col user1 for 999

col nice for 999

set lin 1000 pages 1000

col db_name for a40

col cpus1 for 9999

select (select instance_name||'@'||substr(host_name,-(length(host_name))) from v$instance)db_name ,cpus1,sess,date1, idle,sys,user1,nice,sql.cnt ,sql.CPU,sql.ELAPSED,sql.iowait,sql.exec

from

(

select count(1)sess,os.snap_ID ,cpus1, to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi')date1, idle,sys,user1,nice

 from

(

select

SNAP_ID,

 cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,

 round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,

 round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,

 round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice

from

(SELECT SNAP_ID,

sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,

sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,

sum(decode(stat_name,'USER_TIME',value,0))   user1,

sum(decode(stat_name,'SYS_TIME',value,0))    sys1,

sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,

sum(decode(stat_name,'NICE_TIME',value,0))   nice1

FROM   DBA_HIST_OSSTAT group by SNAP_ID))os

     , DBA_HIST_snapshot sn

     , (select * from dba_hist_active_sess_history where user_id in (select user_id from dba_users where username in ('SYS','SYSTEMS'))) ash  

where os.SNAP_ID=sn.SNAP_ID

and sn.SNAP_ID=ash.SNAP_ID

and ash.snap_id=os.snap_id

group by os.SNAP_ID,cpus1,to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi'), idle,sys,user1,nice

)aa, (select snap_id,count(1)cnt,round(sum(CPU_TIME_TOTAL/1000000),0)CPU,round(sum(ELAPSED_TIME_TOTAL/1000000),0)ELAPSED

,round(sum(IOWAIT_TOTAL/1000000),0)IOWAIT,round(sum(EXECUTIONS_TOTAL),0)exec from DBA_HIST_SQLSTAT group by snap_id) sql

where sql.snap_id=aa.snap_id order by 3;

============

-------------------------
--
-- What did that SID do?
--
-------------------------

select distinct inst_id, sql_id, session_serial#,sql_id  from gv$active_session_history
where sample_time >  sysdate - interval '5' minute
and session_id=&sid;

----------------------------------------------
--
-- Retrieve the SQL from the Library Cache:
--
----------------------------------------------

col sql_text for a80
select sql_text from gv$sql where sql_id='&sqlid';