8 Replies Latest reply: Jan 29, 2013 5:54 AM by Aman.... RSS

    how to check instance datafile I/O? resolve high physical reads/writes?

    Ankit Ashok Aggarwal
      Method-1
      identify any "hot spots" or I/O contention
      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


      Another Method -
      On Oracle10g, AWR also provides the dba_hist_filestatxs table to track disk I/O:

      break on begin_interval_time skip 2

      column phyrds format 999,999,999
      column begin_interval_time format a25

      select
      begin_interval_time,
      filename,
      phyrds
      from
      dba_hist_filestatxs
      natural join
      dba_hist_snapshot;

      Question is - which method you are using to check datafile I/O errors ? AND how to resolve high physical reads and writes??