Easy way to find file causing IO problems in 10g

select event,wait_time_milli,wait_count

from v$event_histogram

where event in ('db file sequential read','db file scattered read')



   EVENT                             WAIT_TIME_MILLI    WAIT_COUNT

1 db file sequential read      1                                       3064

2 db file sequential read      2                                      183

3 db file sequential read      4                                      161

4 db file sequential read      8                                      513

5 db file sequential read      16                                    1227

6 db file sequential read      32                                    1121

7 db file sequential read      64                                     700

8 db file sequential read      128                                   153

9 db file sequential read      256                                   10

10 db file sequential read    512                                   12


Let's assume we want to check those 12 that had 512ms



select file_name,

singleblkrdtim_milli, singleblkrds

from v$file_histogram v, dba_data_files d

where v.file# = d.file_id and singleblkrdtim_milli=512;



FILE_NAME SINGLEBLKRDTIM_MILLI SINGLEBLKRDS

1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\REDIK\SYSTEM01.DBF 512 12