Oracle IO Analysis basic


TCNT SQL_ID        CNT AAS  OBJN OBJ             P1 TABLESPAC
---- ------------- --- --- ----- --------------- -- ----------
  30 0yas01u2p9ch4   1 .01 53113 ITEM_PRODUCT_IX  7 SOEINDEX
                     1 .01 53079 ORDER_ITEMS_UK   7 SOEINDEX 
                    28 .16 53112 ITEM_ORDER_IX    7 SOEINDEX
  58 6v6gm0fd1rgrz   4 .02 54533 WAIT_OBJECTS     1 SYSTEM
                    54 .30     0 0                2 UNDOTBS1

DEF v_minutes=60

col block_type for a18

col obj for a20

col objn for 999999

col otype for a15

col event for a15

col blockn for 999999

col p1 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 tcnt

select

       sum(cnt) over ( partition by io.sql_id order by sql_id ) tcnt,

       io.sql_id,

       io.cnt cnt,

       io.aas aas,

       --io.event event,

       io.objn objn,

       io.obj obj,

       io.p1 p1,

       f.tablespace_name tablespace_name

from

(

  select

        sql_id,

        count(*) cnt,

        round(count(*)/(&v_minutes*60),2) aas,

        CURRENT_OBJ# objn,

        nvl(o.object_name,decode(CURRENT_OBJ#,-1,0,CURRENT_OBJ#)) obj,

        o.object_type otype,

        ash.p1

   from v$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

       CURRENT_OBJ#,

       o.object_name ,

       o.object_type ,

       ash.p1,

       sql_id

) io,

   dba_data_files f

where

   f.file_id = io.p1

Order by tcnt, io.sql_id, io.cnt

/



Comments