Oracle active session report (ASH report ) for IO ..
Oracle IO from ASH
from https://github.com/khailey-zz/ashmasters/blob/master/io.sql
define minutes=15 ---<<<<<<min
set linesize 300 pagesize 300
prompt db file s
col block_type for a18
col objn for a40
col obj for a30
col otype for a15
col event for a15
col blockn for 999999
col p3 for 999
col p1 for 9999999999999
select
substr(event,0,15) event,
ash.p1,
ash.p2,
ash.p3 p3,
CURRENT_OBJ#||' '||o.object_name objn,
nvl(o.object_name,CURRENT_OBJ#) obj,
o.object_type otype,
--CURRENT_FILE# filen,
--CURRENT_BLOCK# blockn,
ash.SQL_ID
--,blocking_session bsid
from gv$active_session_history ash,
all_objects o
where 1=1
and event like 'db file s%'
and o.object_id (+)= ash.CURRENT_OBJ#
and sample_time > sysdate - &minutes/(60*24)
Order by sample_time
/
col block_type for a18
col objn for a25
col otype for a15
col event for a25
col p3 for 999
col fn for 999
col sid for 9999999
col qsid for 9999
col BLOCKN for 99999999999
col kill for a18
select
'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' kill,
-- session_id sid,
substr(event,0,15) event,
QC_SESSION_ID qsid,
--event,
--ash.p1,
--ash.p2,
ash.p3,
CURRENT_OBJ#||' '||o.object_name objn,
o.object_type otype,
CURRENT_FILE# fn,
CURRENT_BLOCK# blockn,
ash.SQL_ID
from gv$active_session_history ash,
all_objects o
where event like 'direct path read'
and o.object_id (+)= ash.CURRENT_OBJ#
and sample_time > sysdate - &minutes/(60*24)
Order by sample_time
/
https://github.com/khailey-zz/ashmasters/blob/master/io_pqo.sql
select
'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' kill,
ash.SQL_ID,
QC_SESSION_ID qsid,
count(*) cnt,
count (distinct session_id) deg,
nvl(o.object_name,to_char(CURRENT_OBJ#)) obj,
o.object_type otype,
decode(session_state, 'WAITING',event,'CPU') event
from gv$active_session_history ash,
all_objects o
where o.object_id (+)= ash.CURRENT_OBJ#
and qc_session_id is not null
and sample_time > sysdate - &minutes/(60*24)
group by '''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''',qc_session_id, sql_id, o.object_name,
o.object_type, CURRENT_OBJ#, event, session_state
Order by qc_session_id, sql_id
/
col block_type for a18
col objn for a35
col otype for a15
col event for a25
col kill for a18
select
'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' kill,
event,
ash.p3,
CURRENT_OBJ#||' '||o.object_name objn,
o.object_type otype,
CURRENT_FILE# filen,
CURRENT_BLOCK# blockn,
ash.SQL_ID
from gv$active_session_history ash,
all_objects o
where event like 'db file scattered read'
and o.object_id (+)= ash.CURRENT_OBJ#
and sample_time > sysdate - &minutes/(60*24)
Order by sample_time
/
col block_type for a18
col objn for a35
col otype for a15
col event for a25
select
'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' kill,
event,
ash.p3,
CURRENT_OBJ#||' '||o.object_name objn,
o.object_type otype,
CURRENT_FILE# filen,
CURRENT_BLOCK# blockn,
ash.SQL_ID
from gv$active_session_history ash,
all_objects o
where event like 'db file sequential read'
and o.object_id (+)= ash.CURRENT_OBJ#
and sample_time > sysdate - &minutes/(60*24)
Order by sample_time
/
col AAS for 99.999
col SQL_ID for A13
col CNT for 9999
col PCT for 999
col OBJ for A35
col SUB_OBJ for A10
col OTYPE for A10
col EVENT for A10
col FILE# for 999
col TABLESPACE_NAME for A15
col CONTENTS for A15
break on sql_id on aas
col f_minutes new_value v_minutes
select &minutes f_minutes from dual;
--select &v_minutes from dual;
select
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,
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,
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 aas, sql_id, cnt
/
col block_type for a18
col objn for a35
col otype for a15
col event for a15
col blockn for 999999
col aas for 999999
col delta for a15
col delta for 999.9999
col mnt for a17
col mxt for a17
col cnt for 99999
/*
*/
select
count(*) cnt,
cast(max(sample_time) as date) - cast(min(sample_time) as date) delta,
to_char(cast(min(sample_time) as date),'DD/YY/MM HH24:mi:ss') mnt,
to_char(cast(max(sample_time) as date),'DD/YY/MM HH24:mi:ss') mxt,
substr(event,0,15) event,
CURRENT_OBJ#||' '||o.object_name objn,
o.object_type otype
from gv$active_session_history ash,
all_objects o
where ( event like 'db file s%' or event like 'direct%' )
and o.object_id (+)= ash.CURRENT_OBJ#
and sample_time > sysdate - &minutes/(60*24)
--and rownum < 10
group by
substr(event,0,15) ,
CURRENT_OBJ#, o.object_name ,
o.object_type
Order by cnt
/
col block_type for a18
col obj for a20
col otype for a15
col event for a15
col blockn for 999999
col f_minutes new_value v_minutes
col p1 for 9999
col tablespace_name for a15
col aas for 99.999
select &minutes f_minutes from dual;
select
io.cnt cnt,
io.aas aas,
io.event event,
substr(io.obj,1,20) obj,
io.p1 p1,
f.tablespace_name tablespace_name
from
(
select
count(*) cnt,
round(count(*)/(&v_minutes*60),2) aas,
substr(event,0,15) event,
nvl(o.object_name,decode(CURRENT_OBJ#,-1,0,CURRENT_OBJ#)) obj,
ash.p1,
o.object_type otype
from gv$active_session_history ash,
all_objects o
where ( event like 'db file s%' or event like 'direct%' )
and o.object_id (+)= ash.CURRENT_OBJ#
and sample_time > sysdate - &v_minutes/(60*24)
group by
substr(event,0,15) ,
CURRENT_OBJ#, o.object_name ,
o.object_type ,
ash.p1
) io,
dba_data_files f
where
f.file_id = io.p1
Order by io.cnt
/
===
define minutes=15
set linesize 300 pagesize 300
col AAS for 99.999
col SQL_ID for A13
col CNT for 9999
col PCT for 999
col OBJ for A30
col SUB_OBJ for A10
col OTYPE for A10
col EVENT for A30
col FILE# for 999999
col TABLESPACE_NAME for A15
col CONTENTS for A15
break on sql_id on aas
col f_minutes new_value v_minutes
select &minutes f_minutes from dual;
--select &v_minutes from dual;
select
--round(sum(cnt) over ( partition by io.sql_id order by sql_id ) / (&v_minutes*60),4) aas,
sum(cnt) over ( partition by io.sql_id order by sql_id ) / (&v_minutes*60) aas,
io.sql_id,
io.cnt cnt,
100*cnt/sum(cnt) over ( partition by io.sql_id order by sql_id ) pct,
o.object_name obj,
o.subobject_name sub_obj,
o.object_type otype,
io.event 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 gv$active_session_history ash
where 1=1
and ( event like 'db file s%' or event like 'direct%' )
and sample_time > sysdate - &v_minutes/(60*24)
group by
CURRENT_OBJ#,
event,
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 aas, sql_id, cnt
/
====
define minutes=15
col block_type for a18
col objn for a25
col otype for a15
col event for a15
col blockn for 999999
col TABLESPACE_NAME for a30
select
tf.cnt,
tf.event,
f.tablespace_name
from (
select
count(*) cnt,
substr(event,0,15) event,
ash.p1 p1
from gv$active_session_history ash
where ( event like 'db file s%' or event like 'direct%' )
and sample_time > sysdate - &minutes/(60*24)
group by
substr(event,0,15) ,
ash.p1
) tf,
dba_data_files f
where
f.file_id = tf.p1
Order by tf.cnt
/
for tablespace ..
define minutes=15
set linesize 300 pagesize 300
col AAS for 99.999
col SQL_ID for A13
col CNT for 9999
col PCT for 999
col OBJ for A30
col SUB_OBJ for A10
col OTYPE for A10
col EVENT for A30
col FILE# for 999999
col TABLESPACE_NAME for A15
col CONTENTS for A15
break on sql_id on aas
col f_minutes new_value v_minutes
select &minutes f_minutes from dual;
--select &v_minutes from dual;
select
--round(sum(cnt) over ( partition by io.sql_id order by sql_id ) / (&v_minutes*60),4) aas,
sum(cnt) over ( partition by io.sql_id order by sql_id ) / (&v_minutes*60) aas,
io.sql_id,
io.cnt cnt,
100*cnt/sum(cnt) over ( partition by io.sql_id order by sql_id ) pct,
o.object_name obj,
o.subobject_name sub_obj,
o.object_type otype,
io.event 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 gv$active_session_history ash
where 1=1
and ( event like 'db file s%' or event like 'direct%' )
and sample_time > sysdate - &v_minutes/(60*24)
group by
CURRENT_OBJ#,
event,
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 aas, sql_id, cnt
/
