Oracle IO from ASH

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
/