Tuesday, October 16, 2012

RAC: AWR File IO Stats report

The following query, basically, shows you the same information you get from the File IO Stats section of an AWR Report.
In an AWR Roport this information is sorted by tablespace name and file name and if you have thousands of datafiles, you'll have a hard time to figure out which of them have, for instance, the highest reads.
With this query, you can get your information sorted by any column you want:

  SELECT SNAP1.INSTANCE_NUMBER "INSTANCE",
         SNAP1.FILENAME "DATAFILE",
         SNAP1.TABLESPACE_NAME "TABLESPACE",
         SNAP1.CONTENTS "CONTENTS",
         ROUND (SNAP1.BYTES / 1024 / 1024, 2) "SIZE MB",
         SNAP2.PHYRDS - SNAP1.PHYRDS "PHYSICAL READS",
         ROUND ((SNAP2.PHYRDS - SNAP1.PHYRDS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE READS/SEC",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND ((SNAP2.PHYBLKRD - SNAP1.PHYBLKRD) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE BLOCKS/READ",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND (((SNAP2.READTIM - SNAP1.READTIM) * 10) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE READ (MS)",
         SNAP2.PHYWRTS - SNAP1.PHYWRTS "PHYSICAL WRITES",
         ROUND ((SNAP2.PHYWRTS - SNAP1.PHYWRTS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE WRITES/SEC",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND ((SNAP2.PHYBLKWRT - SNAP1.PHYBLKWRT) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE BLOCKS/WRITE",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND (((SNAP2.WRITETIM - SNAP1.WRITETIM) * 10) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE WRITE (MS)",
         SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT "BUFFER WAITS",
         DECODE (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT, 0, 0, ROUND (((SNAP2.TIME - SNAP1.TIME) * 10) / (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT), 2)) "AVERAGE BUFFER WAIT (MS)"
    FROM (SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_FILESTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_DATA_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :BEGIN_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME
          UNION
          SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_TEMPSTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_TEMP_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :BEGIN_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME) SNAP1,
         (SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_FILESTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_DATA_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :END_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME
          UNION
          SELECT A.INSTANCE_NUMBER,
                 A.FILENAME,
                 D.TABLESPACE_NAME,
                 D.CONTENTS,
                 E.BYTES,
                 TO_NUMBER(TO_CHAR(S.END_INTERVAL_TIME, 'SSSSS')) "SECS",
                 A.PHYRDS,
                 A.PHYWRTS,
                 A.WAIT_COUNT,
                 A.PHYBLKRD,
                 A.PHYBLKWRT,
                 A.READTIM,
                 A.WRITETIM,
                 A.TIME
            FROM DBA_HIST_TEMPSTATXS A,
                 V$TABLESPACE C,
                 DBA_TABLESPACES D,
                 DBA_TEMP_FILES E,
                 DBA_HIST_SNAPSHOT S
           WHERE     A.SNAP_ID = :END_SNAP
                 AND A.TS# = C.TS#
                 AND C.NAME = D.TABLESPACE_NAME
                 AND A.SNAP_ID = S.SNAP_ID
                 AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                 AND A.FILENAME = E.FILE_NAME) SNAP2
   WHERE     SNAP1.FILENAME = SNAP2.FILENAME
         AND SNAP1.INSTANCE_NUMBER = SNAP2.INSTANCE_NUMBER
ORDER BY 3, 2;

Also, AWR reports are per instance. It does not give you an option to get those metrics aggregated for all your instances.
The following query does that:
SELECT SNAP1.FILENAME "DATAFILE",
         SNAP1.TABLESPACE_NAME "TABLESPACE",
         SNAP1.CONTENTS "CONTENTS",
         ROUND (SNAP1.BYTES / 1024 / 1024, 2) "SIZE MB",
         SNAP2.PHYRDS - SNAP1.PHYRDS "PHYSICAL READS",
         ROUND ((SNAP2.PHYRDS - SNAP1.PHYRDS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE READS/SEC",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND ((SNAP2.PHYBLKRD - SNAP1.PHYBLKRD) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE BLOCKS/READ",
         DECODE (SNAP2.PHYRDS - SNAP1.PHYRDS, 0, 0, ROUND (((SNAP2.READTIM - SNAP1.READTIM) * 10) / (SNAP2.PHYRDS - SNAP1.PHYRDS), 2)) "AVERAGE READ (MS)",
         SNAP2.PHYWRTS - SNAP1.PHYWRTS "PHYSICAL WRITES",
         ROUND ((SNAP2.PHYWRTS - SNAP1.PHYWRTS) / (SNAP2.SECS - SNAP1.SECS), 2) "AVERAGE WRITES/SEC",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND ((SNAP2.PHYBLKWRT - SNAP1.PHYBLKWRT) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE BLOCKS/WRITE",
         DECODE (SNAP2.PHYWRTS - SNAP1.PHYWRTS, 0, 0, ROUND (((SNAP2.WRITETIM - SNAP1.WRITETIM) * 10) / (SNAP2.PHYWRTS - SNAP1.PHYWRTS), 2)) "AVERAGE WRITE (MS)",
         SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT "BUFFER WAITS",
         DECODE (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT, 0, 0, ROUND (((SNAP2.TIME - SNAP1.TIME) * 10) / (SNAP2.WAIT_COUNT - SNAP1.WAIT_COUNT), 2)) "AVERAGE BUFFER WAIT (MS)"
    FROM (  SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_FILESTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_DATA_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :BEGIN_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES
          UNION
            SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_TEMPSTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_TEMP_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :BEGIN_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES) SNAP1,
         (  SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_FILESTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_DATA_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :END_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES
          UNION
            SELECT A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES,
                   SUM (TO_NUMBER (TO_CHAR (S.END_INTERVAL_TIME, 'SSSSS'))) "SECS",
                   SUM (A.PHYRDS) "PHYRDS",
                   SUM (A.PHYWRTS) "PHYWRTS",
                   SUM (A.WAIT_COUNT) "WAIT_COUNT",
                   SUM (A.PHYBLKRD) "PHYBLKRD",
                   SUM (A.PHYBLKWRT) "PHYBLKWRT",
                   SUM (A.READTIM) "READTIM",
                   SUM (A.WRITETIM) "WRITETIM",
                   SUM (A.TIME) "TIME"
              FROM DBA_HIST_TEMPSTATXS A,
                   V$TABLESPACE C,
                   DBA_TABLESPACES D,
                   DBA_TEMP_FILES E,
                   DBA_HIST_SNAPSHOT S
             WHERE     A.SNAP_ID = :END_SNAP
                   AND A.TS# = C.TS#
                   AND C.NAME = D.TABLESPACE_NAME
                   AND A.SNAP_ID = S.SNAP_ID
                   AND A.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND A.FILENAME = E.FILE_NAME
          GROUP BY A.FILENAME,
                   D.TABLESPACE_NAME,
                   D.CONTENTS,
                   E.BYTES) SNAP2
   WHERE SNAP1.FILENAME = SNAP2.FILENAME
ORDER BY 2, 1;

No comments:

Post a Comment