Add new comment

Read Speed by Datafile
These numbers are derived from v$filestat which is reset upon instance restart.

select FILE_NAME as "Name",
       READTIM as "Read time",
       PHYRDS as "Phys Rds Done",
       round(READTIM*10/(PHYRDS+1),2) "Phys Read AVG (ms)",
       PHYBLKRD as "Phys Blocks Read",
       round(READTIM*10/(PHYBLKRD+1),2) "Block Read AVG (ms)",
       WRITETIM "Write time",
       PHYWRTS "Phys Wrts Done",
       round(WRITETIM*10/(PHYWRTS+1),2) "Phys Write AVG (ms)",
       PHYBLKWRT "Phys Blocks Wrt",
       round(WRITETIM*10/(PHYBLKWRT+1),2) "Block Write AVG (ms)",
       PHYRDS+PHYWRTS "TOTAL IO"
from V$FILESTAT s,
     DBA_DATA_FILES f
where s.FILE# = f.FILE_ID
order by 4 desc, 8 desc;

WITH MainQuery AS (
   SELECT f.file_name, 
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,1,SINGLEBLKRDS,0)) MILLI1,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,2,SINGLEBLKRDS,0)) MILLI2,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,4,SINGLEBLKRDS,0)) MILLI4,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,8,SINGLEBLKRDS,0)) MILLI8,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,16,SINGLEBLKRDS,0)) MILLI16,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,32,SINGLEBLKRDS,0)) MILLI32,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,64,SINGLEBLKRDS,0)) MILLI64,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,128,SINGLEBLKRDS,0)) MILLI128,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,256,SINGLEBLKRDS,0)) MILLI256,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,512,SINGLEBLKRDS,0)) MILLI512,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,1024,SINGLEBLKRDS,0)) MILLI1024,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,2048,SINGLEBLKRDS,0)) MILLI2048,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,4096,SINGLEBLKRDS,0)) MILLI4096
  FROM V$FILE_HISTOGRAM h,
       DBA_DATA_FILES f
 WHERE h.FILE# = f.FILE_ID
 GROUP BY FILE_NAME
 ORDER BY FILE_NAME),
Pass2 AS (SELECT MainQuery.*,
       (MILLI1+MILLI2+MILLI4+MILLI8+MILLI16) AS Fast,
       (MILLI32+MILLI64+MILLI128+MILLI256+MILLI512+MILLI1024+MILLI2048+MILLI4096)AS Slow
  FROM MainQuery ORDER BY FILE_NAME)
SELECT Pass2.*, ROUND(FAST/(FAST+SLOW) * 100,2)as FASTRatio FROM Pass2 ;

Totals Megs by Tablespace

-- Totals Megs by Tablespace
SELECT TABLESPACE_NAME, 
       SUM(dba_data_files.bytes/1024/1024) as total_megs
FROM dba_data_files 
  group by TABLESPACE_NAME;

Megs Used by Tablespace

-- Megs Used by Tablespace
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 as MEGS_USED 
FROM DBA_SEGMENTS 
GROUP BY TABLESPACE_NAME;