Oracle Tablespace IO detail

IO time by tablespace

column name format a30
SELECT
ts.name,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,1,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI1,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,2,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI2,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,4,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI4,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,8,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI8,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,16,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI16,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,32,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI32,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,64,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI64,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,128,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI128,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,256,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI256,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,512,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI512,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,1024,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI1024,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,2048,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI2048,
round (100*sum(FH.SINGLEBLKRDS)/sum(sum(FH.SINGLEBLKRDS)) over(),2) AS total
FROM V$FILE_HISTOGRAM fh
join v$datafile df on df.file#=fh.file# and df.ENABLED='READ WRITE'
join v$tablespace ts on TS.TS#=df.ts#
GROUP BY ts.name ORDER BY total;



NAME MILLI1 MILLI2 MILLI4 MILLI8 MILLI16 MILLI32 MILLI64 MILLI128 MILLI256 MILLI512 MILLI1024 MILLI2048 TOTAL
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ANUJTEST 0 0 0 0 0 0 100 0 0 0 0 0 0
DROP1 0 0 0 0 0 0 0 100 0 0 0 0 0
EXAMPLE 0 0 20 20 40 20 0 0 0 0 0 0 0
PERFSTAT 0 0 0 0 0 0 0 100 0 0 0 0 0
TSAPEXF 0 0 0 0 0 0 0 100 0 0 0 0 0
RMAN 0 0 0 0 0 0 0 100 0 0 0 0 0
TEST 0 0 0 0 0 0 0 100 0 0 0 0 0
TSAPEXU 8.696 0 13.043 21.739 43.478 4.348 4.348 4.348 0 0 0 0 .02
UNDOTBS1 59.375 0 3.125 6.25 15.625 12.5 0 3.125 0 0 0 0 .03
SYSAUX 60.946 1.789 2.288 7.128 13.123 10.906 3.139 .573 .1 .004 0 0 23.62
SYSTEM 80.917 .588 .908 3.911 6.091 4.106 1.798 1.25 .386 .033 .011 0 26.68
USERS 89.227 .883 1.867 2.821 2.661 1.887 .565 .071 .018 0 0 0 49.64