See disk file disk I / O number of

Posted on 2009-06-25 filed under: Oracle (RSS) 27 views

Can query V $ FILESTAT find disk files for each disk I / O quantity, each summary data files for each disk of all the I / O to identify the most likely bottlenecks caused by disk data files
V $ FILESTAT contains the following column

Help
FILE # file number in the V $ DATAFILE in Canada can be obtained after the FILE # Name
PHYRDS has completed the number of physical reads
PHYWRTS has completed the number of physical write
PHYBLKRD read the number of physical block
PHYBLKWRT write the number of physical block
The time to read READTIM
The time to write WRITETIM

Note the last two parameters contain 0 unless TIMED_STATISTICS set to TRUE
Use the following query to monitor these values

SQL> col name format a40
SQL> SELECT phyrds, phywrts, d.name
2 FROM v $ datafile d, v $ filestat f
3 WHERE d.file # = f.file # order by d.name;

PHYRDS PHYWRTS NAME
---------- ---------- ------------------------------ ----------
46317 179816 / data1/oradata/dbnms/sysaux01.dbf
147921 131974 / data1/oradata/dbnms/sysaux02.dbf
98538 85339 / data1/oradata/dbnms/system01.dbf
5979 10033 / data1/oradata/dbnms/system02.dbf
511 203266 / data1/oradata/dbnms/undotbs01.dbf
14633943 424908 / data1/oradata/dbnms/user02.dbf
2305255 2752296 / data1/oradata/dbnms/users01.dbf
179712 30608 / data2/oradata/dbnms/GOLDPEN.dbf
58854 5099 / data2/oradata/dbnms/INFOCASES.dbf
2046999 55947 / data2/oradata/dbnms/MARKET_NEW.dbf
2026600 64013 / data2/oradata/dbnms/MARKET_NEW1.dbf

PHYRDS PHYWRTS NAME
---------- ---------- ------------------------------ ----------
2319092 102828 / data2/oradata/dbnms/MARKET_NEW2.dbf
2052449 68187 / data2/oradata/dbnms/MARKET_NEW3.dbf
333 299 / data2/oradata/dbnms/ccidnet.dbf
337 299 / data2/oradata/dbnms/ccidnetindex.dbf

15 rows selected.