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
/
