REM Author: Alexander Geldutes, Nov 2001
REM E-mail: info@dbaoncall.net	
REM W-page: http://www.dbaoncall.net
REM -----------------------------------------------
REM  Shows disk I/O per disk file (datafile)
REM -----------------------------------------------

SET LINES 100 PAGES 999 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF

SPOOL c:\temp\dm.log

column name format a50
column phyrds format 999,999,999
column phywrts format 999,999,999
column read_pct format 999.99
column write_pct format 999.99

select name, 
  phyrds,
  phyrds * 100 / trw.phys_reads read_pct,
  phywrts, phywrts * 100 / trw.phys_wrts write_pct
from (select Sum(phyrds) phys_reads, 
      Sum(phywrts) phys_wrts
      from v$filestat) trw,
  v$datafile df,
  v$filestat fs
where df.file# = fs.file#
order by phyrds desc;


SPOOL OFF
SET LINES 80 PAGES 24 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON

PROMPT
PROMPT ******************************************************
PROMPT
PROMPT Output saved at c:\temp\dm.log
PROMPT



*************************************************************
REM -----------------------------------------------
REM  Shows disk I/O per disk file (tempfile)
REM -----------------------------------------------

select name, 
  phyrds,
  phyrds * 100 / trw.phys_reads read_pct,
  phywrts, phywrts * 100 / trw.phys_wrts write_pct
from (select Sum(phyrds) phys_reads, 
      Sum(phywrts) phys_wrts
      from v$tempstat) trw,
  v$tempfile tf,
  v$tempstat ts
where tf.file# = ts.file#
order by phyrds desc;