Datafiles Disk I/O

Summary 
The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O.

How I/O is spread per datafile 

SELECT NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,

phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,

phyrds + phywrts total

FROM v$datafile df, v$filestat fs

WHERE df.FILE# = fs.FILE#

ORDER BY phyrds DESC;

Tip: ORDER BY phyrds, order by physical reads descending. ORDER BY phywrts, order by physical writes descending.

How I/O is spread per filesystem 

SELECT filesystem, ROUND((RATIO_TO_REPORT(READS) OVER ())*100, 2) || '%' PERC_READS,

ROUND((RATIO_TO_REPORT(WRITES) OVER ())*100, 2) || '%' PERC_WRITES,

ROUND((RATIO_TO_REPORT(TOTAL) OVER ())*100, 2) || '%' PERC_TOTAL

FROM (SELECT filesystem, SUM(Physical_READS) READS, SUM(Physical_WRITES) WRITES, SUM(total) TOTAL

FROM (SELECT SUBSTR(NAME, 0, 25) filesystem, phyrds Physical_READS,

ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,

phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,

phyrds + phywrts total

FROM v$datafile df, v$filestat fs

WHERE df.FILE# = fs.FILE#

ORDER BY phyrds DESC) A

GROUP BY filesystem) B

ORDER BY ROUND((RATIO_TO_REPORT(total) OVER ())*100, 2) DESC;

Tip: To see the filesystems correct experiment with the SUBSTR(NAME, 0, 25)

How I/O is spread for the datafiles of a specific tablespace

SELECT df.NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,

phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,

phyrds + phywrts total

FROM v$datafile df, v$filestat fs, ts$ t

WHERE df.FILE# = fs.FILE#

AND df.ts# = t.ts#

AND t.NAME = 'TABLESPACE_NAME'

ORDER BY phyrds DESC;


Source: Internet