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';
