Tuesday, August 27, 2013

Tablespace Disk I/O

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