I/O information for datafiles

extract how much I/O is performed on datafiles.

set lines 180

col name format A50

select   NAME,

  PHYRDS "Physical Reads",

  round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",

  PHYWRTS "Physical Writes",

  round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",

  fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"

from (

  select   sum(PHYRDS) PHYS_READS,

    sum(PHYWRTS) PHYS_WRTS

  from    v$filestat

  ) pd,

  v$datafile df,

  v$filestat fs

where   df.FILE# = fs.FILE#

order   by fs.PHYBLKRD+fs.PHYBLKWRT desc;

Sample output:

NAME                                               Physical Reads     Read % Physical Writes    Write % Total Block I/O's

-------------------------------------------------- -------------- ---------- --------------- ---------- -----------------

/p07/oradata/padwsdpr/SGDATA_PART_DATA_03.dbf            46817222        4.2          526942        .66         242239138

/p07/oradata/padwsdpr/SGDATA_PART_DATA_01.dbf            45504990       4.09          570122        .72         227943429

/p03/oradata/padwsdpr/SGDATA_PART_DATA_02.dbf            48710514       4.37          547700        .69         226105668

/p03/oradata/padwsdpr/SGDATA_PART_DATA_08.dbf            39217397       3.52          503078        .63         208712510

/p07/oradata/padwsdpr/SGDATA_PART_DATA_09.dbf            40487221       3.64          487099        .61         204213299