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;

Procedure to automate datafiles' resizing taking into account filesystems' free space

I wanted to create a mechanism for our operators to be able to give more space to tablespaces, automatically, when there is a need to.
The operators don't know the datafiles' location and the filesystems' free space, so the only thing they will do is to run a schell script with the tablespace name as a parameter.

Unfortunately, oracle database does not store any information about the filesystems and their space usage, so if you are not careful you may end up with completely full filesystems.
We can only get this info, using OS commands like bdf and df, so to make it available to our database, we will create a text file in our server, containing the output of those commands and an external table in the database to read that file.

I created this example in HP-UX, so you will need to do a few modifications to make it run in Linux or other Unix.
For example, bdf is an HP-UX command and you should use df in other Unix/Linux.

First, create a directory pointing to the location, where our scripts and output files will be stored, e.g. /oradb/orabin/scripts:

CREATE DIRECTORY SCRIPT_DIR AS '/oradb/orabin/scripts';

Now, create the external table, called df, which reads /oradb/orabin/scripts/bdf.txt:

CREATE TABLE DF
                   (FILESYSTEM VARCHAR2(200),
                    KBYTES       NUMBER,
                    USED         NUMBER,
                    AVAIL        NUMBER,
                    USED_PER     VARCHAR2(20),
                    MOUNTED      VARCHAR2(100)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY SCRIPT_DIR
       ACCESS PARAMETERS
       (
         RECORDS DELIMITED BY NEWLINE
         BADFILE SCRIPT_DIR:'bdf%a_%p.bad'
         LOGFILE SCRIPT_DIR:'bdf%a_%p.log'
         FIELDS TERMINATED BY ' '
         MISSING FIELD VALUES ARE NULL
       )
       LOCATION ('bdf.txt')
     )
     REJECT LIMIT UNLIMITED;

Our script will make some of the given tablespace's datafiles autoextensible, taking into account the filesystems' free space.
We discard any datafiles there are in filesystems with > 99% of used space.
For all others, we autoextend them to a size equal to:
CURRENT DATAFILE SIZE + 10% OF FILESYSTEM'S FREE SPACE
If the above size is greater than tablespace's block size * 4194302, then we set MAXSIZE UNLIMITED, to avoid to get an ORA error.
So, create file /oradb/orabin/scripts/extend.sql:

SET SERVEROUTPUT ON
SPOOL OUT.TXT

DECLARE
   V_STMT    VARCHAR2 (1000);
   V_BSIZE   NUMBER;

   CURSOR C1
   IS
      SELECT A.FILE_NAME,
             A.BYTES,
             B.AVAIL,
             (BYTES / 1024) + (AVAIL * 0.1) NEW_SIZE
        FROM DBA_DATA_FILES A, DF B
       WHERE     A.TABLESPACE_NAME = UPPER ('&1')
             AND SUBSTR (A.FILE_NAME, 1, INSTR (A.FILE_NAME, '/', -1, 2) - 1) = B.MOUNTED
             AND SUBSTR (B.USED_PER, 1, INSTR (B.USED_PER, '%') - 1) < 99;
BEGIN
   SELECT BLOCK_SIZE
     INTO V_BSIZE
     FROM DBA_TABLESPACES
    WHERE TABLESPACE_NAME = UPPER ('&1');

   FOR DFILE IN C1
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Filename: ' || DFILE.FILE_NAME);
      DBMS_OUTPUT.PUT_LINE ('Size (KB): ' || DFILE.BYTES / 1024);
      DBMS_OUTPUT.PUT_LINE ('Avail (KB): ' || DFILE.AVAIL);

      IF DFILE.NEW_SIZE < V_BSIZE * 4194302 / 1024
      THEN
         V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE ' || DFILE.NEW_SIZE || 'K';
      ELSE
         V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
      END IF;

      DBMS_OUTPUT.PUT_LINE (V_STMT);
      EXECUTE IMMEDIATE V_STMT;
      DBMS_OUTPUT.PUT_LINE ('--------------------------');
   END LOOP;
END;
/

SPOOL OFF

Finally, we create the shell script /oradb/orabin/scripts/extend.sh, which creates the bdf.txt with the appropriate format for our external table. It also sends an email to the DBAs, so they get informed that the procedure run and which datafiles were affected.

#!/bin/sh
ORACLE_HOME=/oradb/orabin/app/product/db1
export ORACLE_HOME
ORACLE_SID=OTE
export ORACLE_SID

rm bdf*
rm original.txt
bdf|grep -v File|grep -v vol|grep -v odm|grep -v shared>original.txt
sed -e "s/  */ /g" original.txt > bdf.txt

cd /oradb/orabin/scripts
sqlplus -s /nolog << EOF
connect / as sysdba
@extend.sql $1
exit
EOF

cat OUT.TXT|mailx -s "Autoextend script run for $ORACLE_SID" name@ote.gr

Now, the only thing the operator has to do is run extend.sh with the tablespace name as a parameter:

extend.sh user_data