Basic statement IO analysis

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
   /