Following script will find out all datafiles OR first 5 datafiles with highest Input-Output in terms of physical reads and write on datafiles along with read/write time.
SQL> col name format a50 SQL> set linesize 200 SQL> select * from ( select name,phyrds, phywrts, readtim, writetim from v$filestat a, v$datafile b where a.file#=b.file# order by readtim desc) where rownum < 6;
O/P:
NAME PHYRDS PHYWRTS READTIM WRITETIM --------------------------------------- --------- -------- -------- --------- /DATA/database/prod/prod_app_data.dbf 958846 231710 55197 12248 /DATA/database/prod/system01.dbf 1113495 69047 46798 1677 /DATA/database/prod/prod_app_lob.dbf 11226086 57018 44491 469 /DATA/database/prod/prod_app_index.dbf 389124 134121 44330 6719 /DATA/database/prod/sysaux01.dbf 396043 115628 29089 5735
Stay Tune 🙂
Very usefull script !! is there a way to find which session causing that much IO ??