This discussion is archived
3 Replies Latest reply: Nov 15, 2012 11:04 AM by Facundo RSS

How to view "File IO Stats" through database views

Facundo Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you very much!!!

Legend

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