rem /******************************************************************************* 
rem Script:  IOWeightsByDrive
rem          Purpose: to determine the hottest files and sort them by drive.  
rem          This script will determine each datafiles I/O and express it's
rem          weight as a percentage against the hottest datfile in your database.
rem Note:    This script gathers data from the time of instance startup, so the SYSTEM
rem          tablespace will be higher than it's everyday value.
rem AlsoNote:The weights are against the hottest datafile, not against total IO of
rem          the system.  
rem ********************************************************************************/

set pagesize 60 linesize 80 newpage 0 feedback off
ttitle skip center "Database File IO Weights" skip center "Ordered by Drive" skip 2
column Total_IO format 999999999
column Weight format 999.99
column File_name format A40
break on Drive skip 2
compute sum of Weight on Drive

select substr(DF.Name,1,5) Drive, DF.Name File_Name,
       FS.Phyblkrd+FS.Phyblkwrt Total_IO,
       100*(FS.Phyblkrd+FS.Phyblkwrt)/MaxIO Weight
from V$FILESTAT FS, V$DATAFILE DF,
     (select MAX(phyblkrd+phyblkwrt) MaxIO
        from v$filestat)
where DF.File# = FS.File#
order by Drive, Weight desc

spool io_weights
/
spool off