Wednesday, February 18, 2009

Tablespace 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 tablespace
SELECT T.NAME,
         SUM (PHYSICAL_READS) PHYSICAL_READS,
         ROUND ( (RATIO_TO_REPORT (SUM (PHYSICAL_READS)) OVER ()) * 100, 2)
         || '%'
            PERC_READS,
         SUM (PHYSICAL_WRITES) PHYSICAL_WRITES,
         ROUND ( (RATIO_TO_REPORT (SUM (PHYSICAL_WRITES)) OVER ()) * 100, 2)
         || '%'
            PERC_WRITES,
         SUM (TOTAL) TOTAL,
         ROUND ( (RATIO_TO_REPORT (SUM (TOTAL)) OVER ()) * 100, 2) || '%'
            PERC_TOTAL
    FROM (  SELECT TS#,
                   NAME,
                   PHYRDS PHYSICAL_READS,
                   PHYWRTS PHYSICAL_WRITES,
                   PHYRDS + PHYWRTS TOTAL
              FROM V$DATAFILE DF, V$FILESTAT FS
             WHERE DF.FILE# = FS.FILE#
          ORDER BY PHYRDS DESC) A,
         SYS.TS$ T
   WHERE A.TS# = T.TS#
GROUP BY T.NAME
ORDER BY PHYSICAL_READS DESC;

No comments:

Post a Comment