REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_dbf_io.sql
REM
REM <SQLDIR_GRP>DBF MOST TABSP STATS</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	This script will show IO by mount point
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_dbf_io.sql
REM 
REM Example:
REM	s_dbf_io.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

spool s_dbf_io.log

clear columns
clear breaks
col drive format a5
col file_name format a35
col blocks_read format 9,999,999,999
col blocks_written format 9,999,999,999
col total_ios format 9,999,999,999
col total_reads format 9,999,999,999
col total_writes format 9,999,999,999
set lines 132 pages 60 newpage 0 feedback off trimspool on
ttitle skip center "Database File I/O Information" skip 2
break on report
compute sum of total_reads on report
compute sum of blocks_read on report
compute sum of total_writes on report
compute sum of blocks_written on report
compute sum of total_ios on report

select          substr(df.name,1,5)       drive
,               sum(fs.phyrds)            total_reads
,               sum(fs.phyblkrd)          blocks_read
,               sum(fs.phywrts)           total_writes
,               sum(fs.phyblkwrt)         blocks_written
,               sum(fs.phyrds+fs.phywrts) total_ios
from            v$filestat      fs
,               v$datafile      df
where           df.file# = fs.file#
group by        substr(df.name,1,5)
order by        total_ios desc
/


clear breaks
clear computes
break on drive skip 1 on report
compute sum of total_reads on report
compute sum of blocks_read on report
compute sum of total_writes on report
compute sum of blocks_written on report
compute sum of total_ios on report
compute sum of total_reads on drive
compute sum of blocks_read on drive
compute sum of total_writes on drive
compute sum of blocks_written on drive
compute sum of total_ios on drive
ttitle skip center "Database File I/O by Drive" skip 2

select          substr(df.name,1,5)             drive
,               df.name                         file_name
,               fs.phyrds                       total_reads
,               fs.phyblkrd                     blocks_read
,               fs.phywrts                      total_writes
,               fs.phyblkwrt                    blocks_written
,               fs.phyrds+fs.phywrts            total_ios
from            v$filestat      fs
,               v$datafile      df
where           df.file# = fs.file#
order by        drive
,               file_name desc
/

spool off