Oracle I/O throughput scripts Oracle Database Tips by Donald Burleson

The This script queries the v$filestat view in a similar way to the scripts on the Oracle Scripts download has over 50 scripts for organizing I/O throughput reports, but we always see novel applications of queries against v$filestat and dba_hist_filestatxs.

Franck Pachot has published an useful and sophisticated query to display Oracle I/O throughput.  Note his use of temporary tables in lieu of a subquery:

SET linesize 132 pagesize 1000
column file_name format A60
DROP TABLE tmp_iostats_1;

REM *** get first snapshot ***
create table tmp_iostats_1 as select SYSDATE snaptime,'D' filetype,t.* from v$filestat t union all select SYSDATE snaptime,'T' filetype,t.* from v$tempstat t;

REM *** wait 30 seconds ***
EXEC Dbms_Lock.sleep(30);

REM *** calculates the average io per second ***
WITH tmp_iostats_2 as (select SYSDATE snaptime,'D' filetype,t.* from v$filestat t union all select SYSDATE snaptime,'T' filetype,t.* from v$tempstat t)
,delta AS (
 SELECT
  filetype,file# file_id,(e.snaptime-b.snaptime)*24*60*60 seconds,(e.phyrds-b.phyrds)+(e.phywrts-b.phywrts) ios
 FROM tmp_iostats_1 b join tmp_iostats_2 e using (file#,filetype)
)
,files AS (SELECT 'D' filetype,file_id,file_name FROM dba_data_files t UNION ALL SELECT 'T' filetype,file_id,file_name FROM dba_temp_files t)
SELECT
filetype,
file_name,
ios "io calls",
Round(ios/seconds,2) "io/sec"
FROM files JOIN delta USING(file_id,filetype)
ORDER BY ios desc
 

This script queries the v$filestat view in a similar way to the scripts on the Oracle Scripts download, another good source of Oracle I/O throughput scripts.
 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.