Script to find out datafiles with highest Input-Output activity

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 🙂

Leave a Reply

%d bloggers like this: