This discussion is archived
8 Replies Latest reply: Jan 29, 2013 3:54 AM by Aman.... RSS

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

AnkitAshokAggarwal Explorer
Currently Being Moderated
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??

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points