3 Replies Latest reply: Nov 15, 2012 1:04 PM by Facundo RSS

    How to view "File IO Stats" through database views

    Facundo
      Hello Guys,

      I need to view the "File IO Stats" (information of datafile that I view at AWR frecuently) through database views. Which are the view that I must use?

      Thanks in advanced.
        • 1. Re: How to view "File IO Stats" through database views
          LaserSoft
          Hi

          The below query will display regular IO on Data files as well as Temporary IO.

          select a.file#,b.name, a.phyrds, a.phywrts, (100*(a.phyrds+a.phywrts)/&divide_by) Percent,
          a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio,
          (a.phyblkwrt/greatest(a.phywrts,1)) bwratio
          from sys.v_$filestat a, sys.v_$dbfile b
          where a.file#=b.file#
          union
          select c.file#,d.name, c.phyrds, c.phywrts,
          (100*(c.phyrds+c.phywrts)/&divide_by) Percent,
          c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio,
          (c.phyblkwrt/greatest(c.phywrts,1)) bwratio
          from sys.v_$tempstat c, sys.v_$tempfile d
          where c.file#=d.file#
          order by 1

          Thanks
          LaserSoft
          • 2. Re: How to view "File IO Stats" through database views
            vlethakula
            If you have license for AWR,


            dba_hist_filestatxs---to get datafile i/o stats
            dba_hist_tempstatxs--to get temp datafile stats

            Join these 2 views dba_hist_snapshot to get filestats for particular interval

            you can find the queries in statspack report.
            spreport.sql calls sprepins.sql search for Tablespace IO section and File IO section
            replace sys$ with dba_hist

            Edited by: vlethakula on Nov 15, 2012 9:30 AM
            • 3. Re: How to view "File IO Stats" through database views
              Facundo
              Thank you very much!!!