Prompt Top Waiting Session (within last 5 min)
Prompt ========================================
Select
session_id,
count(*)
from
v$active_session_history
where
session_state=‘WAITING’ and
SAMPLE_TIME > SYSDATE - (5/(24*60))
group by
session_id
order by
count(*) desc;
Prompt Top CPU Session (within last 5 min)
Prompt ========================================
Select
session_id,
count(*)
from
v$active_session_history
where
session_state= ‘ON CPU‘ and
SAMPLE_TIME > sysdate – (5/(24*60))
group by
session_id
order by
count(*) desc;
Prompt Top SQL from ASH
Prompt ========================================
select
ash.SQL_ID ,
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)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,
v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1)) desc;
Prompt Top Session
Prompt ========================================
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)) -
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;
Prompt Top Session with username
Prompt ========================================
select
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
"STATUS",
topsession.session_id "SESSION_ID",
u.name "NAME",
topsession.program "PROGRAM",
max(topsession.CPU) "CPU",
max(topsession.WAITING) "WAITING",
max(topsession.IO) "IO",
max(topsession.TOTAL) "TOTAL"
from ( {previous query} ) 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# (+)
group by topsession.session_id, topsession.session_serial#, topsession.user_id,
topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc;
Prompt Top IO with object
Prompt ========================================
col tcnt for 9999
col aas for 999.99
col sql_id for a14
col cnt for 999
col pct for 999
col obj for a20
col sub_obj for a10
col otype for a15
col event for a15
col file# for 9999
col tablespace_name for a15
col f_minutes new_value v_minutes
select &minutes f_minutes from dual;
break on sql_id on aas on tcnt
select
-- sum(cnt) over ( partition by io.sql_id order by sql_id ) tcnt,
round(sum(cnt) over ( partition by io.sql_id order by sql_id ) / (&v_minutes*60),2) aas,
io.sql_id,
-- io.cnt cnt,
100*cnt/sum(cnt) over ( partition by io.sql_id order by sql_id ) pct,
--CURRENT_OBJ# obj#,
o.object_name obj,
o.subobject_name sub_obj,
o.object_type otype,
substr(io.event,8,10) event,
io.p1 file#,
f.tablespace_name tablespace_name,
tbs.contents
from
(
select
sql_id,
event,
count(*) cnt,
count(*) / (&v_minutes*60) aas,
CURRENT_OBJ# ,
ash.p1
from v$active_session_history ash
where ( event like 'db file s%' or event like 'direct%' )
and sample_time > sysdate - &v_minutes/(60*24)
group by
CURRENT_OBJ#,
event,
--o.object_name ,
--o.object_type ,
ash.p1,
sql_id
) io
, dba_data_files f
, all_objects o
, dba_tablespaces tbs
where
f.file_id = io.p1
and o.object_id (+)= io.CURRENT_OBJ#
and tbs.tablespace_name= f.tablespace_name
Order by tcnt, sql_id, cnt
/
clear breaks
Prompt : Longest running queries(11g)
============================
col 1 for 99999
col 2 for 99999
col 3 for 9999
col 4 for 999
col 5 for 99
col av for 99999
col ct for 99999
col mn for 999
col av for 99999.9
col longest_sql_exec_id for A10
WITH pivot_data AS (
SELECT
sql_id,
ct,
mxdelta mx,
mndelta mn,
round(avdelta) av,
WIDTH_BUCKET(delta_in_seconds,mndelta,mxdelta+.1,5) AS bucket ,
SUBSTR(times,12) max_run_time,
SUBSTR(longest_sql_exec_id, 12) longest_sql_exec_id
FROM (
SELECT
sql_id,
delta_in_seconds,
COUNT(*) OVER (PARTITION BY sql_id) ct,
MAX(delta_in_seconds) OVER (PARTITION BY sql_id) mxdelta,
MIN(delta_in_seconds) OVER (PARTITION BY sql_id) mndelta,
AVG(delta_in_seconds) OVER (PARTITION BY sql_id) avdelta,
MAX(times) OVER (PARTITION BY sql_id) times,
MAX(longest_sql_exec_id) OVER (PARTITION BY sql_id) longest_sql_exec_id
FROM (
SELECT
sql_id,
sql_exec_id,
MAX(delta_in_seconds) delta_in_seconds ,
LPAD(ROUND(MAX(delta_in_seconds),0),10) || ' ' ||
TO_CHAR(MIN(start_time),'YY-MM-DD HH24:MI:SS') || ' ' ||
TO_CHAR(MAX(end_time),'YY-MM-DD HH24:MI:SS') times,
LPAD(ROUND(MAX(delta_in_seconds),0),10) || ' ' ||
TO_CHAR(MAX(sql_exec_id)) longest_sql_exec_id
FROM ( SELECT
sql_id,
sql_exec_id,
CAST(sample_time AS DATE) end_time,
CAST(sql_exec_start AS DATE) start_time,
((CAST(sample_time AS DATE)) -
(CAST(sql_exec_start AS DATE))) * (3600*24) delta_in_seconds
FROM
dba_hist_active_sess_history
WHERE sql_exec_id IS NOT NULL
)
GROUP BY sql_id,sql_exec_id
)
)
where ct > &min_repeat_executions_filter
and mxdelta > &min_elapsed_time
)
SELECT * FROM pivot_data
PIVOT ( COUNT(*) FOR bucket IN (1,2,3,4,5))
ORDER BY mx DESC,av DESC
;
Queries which ran between specified time
============================
select distinct a.sql_id,dbms_lob.substr(b.sql_text,4000,1) from dba_hist_active_sess_history a, dba_hist_sqltext b
where sample_time between to_date('20130219:17:00','yyyymmdd:hh24:mi') and to_date('20130219:19:20','yyyymmdd:hh24:mi')
and b.sql_id=a.sql_id
union all
select a.sql_id ,dbms_lob.substr(b.sql_text,4000,1)from v$active_session_history a ,v$sqlarea b
where sample_time between to_date('20130219:17:00','yyyymmdd:hh24:mi') and to_date('20130219:19:20','yyyymmdd:hh24:mi')
and b.sql_id=a.sql_id
select a.SAMPLE_TIME,
SQL_ID,
SQL_PLAN_HASH_VALUE ,
-- a.SQL_OPNAME, -- include in 11gr2
-- a.SQL_EXEC_START, -- include in 11gr2
a.program,
a.client_id,
b.SQL_TEXT,
(case when c.executions_delta > 0 then c.elapsed_time_delta/ c.executions_delta/ 1000000 else 0 end) seconds_per_exe,
e.username
from DBA_HIST_ACTIVE_SESS_HISTORY a
join dba_hist_sqltext b on (a.SQL_ID = b.SQL_ID)
join dba_users e on (a.user_id = e.user_id)
left outer join dba_hist_sqlstat c on (a.sql_id = c.sql_id)
left outer join dba_hist_snapshot d on (c.snap_id = d.snap_id and
c.dbid = d.dbid and
c.instance_number = d.instance_number and
a.sample_time between d.begin_interval_time and d.end_interval_time)
where b.sql_text like '%FCT_TIME_DIMENSION_sysdate2%'
and a.sample_time between trunc(sysdate) -9 and trunc(sysdate)
Top 10 CPU consumers in last 5 minutes
SQL> select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
Top 10 waiting sessions in last 5 minutes
SQL> select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10; SQL> select serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info
from v$session where sid=&sid;
What the sql_id using sidSQL> select distinct sql_id, session_serial# from v$active_session_history
where sample_time > sysdate - interval '5' minute
and session_id=&sid;
Getthe SQL from the Library Cache using sqlid:
col sql_text for a80
SQL> select sql_text from v$sql where sql_id='&sqlid';
Identify User running OS process
select s.sid, s.serial#, s.username, s.osuser, s.program, s.status
from v$session s, v$process p where s.PADDR=p.ADDR and SPID=
Find suspicious sql that may need tuning (8.1.6 and above )
column load format a6 justify right
column executes format 9999999
break on load on executes
select
substr(to_char(s.pct, '99.00'), 2) || '%' load,
s.executions executes,
p.sql_text
from
(
select
address,
buffer_gets,
executions,
pct,
rank() over (order by buffer_gets desc) ranking
from
(
select
address,
buffer_gets,
executions,
100 * ratio_to_report(buffer_gets) over () pct
from
sys.v_$sql
where
command_type != 47
)
where
buffer_gets > 50 * executions
) s,
sys.v_$sqltext p
where
s.ranking <= 5 and
p.address = s.address
order by
1, s.address, p.piece
Expensive disk read queries ( 8.1.6 and above)
column load format a6 justify right
column executes format 9999999
break on load on executes
select
substr(to_char(s.pct, '99.00'), 2) || '%' load,
s.executions executes,
p.sql_text
from
(
select
address,
disk_reads,
executions,
pct,
rank() over (order by disk_reads desc) ranking
from
(
select
address,
disk_reads,
executions,
100 * ratio_to_report(disk_reads) over () pct
from
sys.v_$sql
where
command_type != 47
)
where
disk_reads > 50 * executions
) s,
sys.v_$sqltext p
where
s.ranking <= 5 and p.address = s.address order by 1, s.address, p.piece
Current Running SQLs
------------------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select S.USERNAME,sid,serial#,a.sql_id,a.SQL_TEXT, --i.host_name,machine,
S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/
col EVENT for a29
col usr for a20
col SID_SER for a9
select S.USERNAME||'-'||S.status usr,sid||','||serial# sid_ser,a.sql_id,--a.SQL_TEXT, --i.host_name,machine,
S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login ,
--(sysdate - logon_time)*24*60*60
(sysdate - logon_time)*24*60 Min_waits
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
order by Min_waits asc
Current Running SQLs
------------------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/
