Wednesday, February 18, 2009

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, 9) 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 TOTAL DESC) A
          GROUP BY FILESYSTEM) B
ORDER BY PERC_TOTAL DESC;

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

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;

No comments:

Post a Comment