Oracle Database tablespace I/O statistics for the last 2 hours

I/O statistics for tablespace in the oracle database during the last two hours

--------------------------

------For single instance----------

Select e. tsname,
Sum (e. phyrds-nvl (B. phyrds, 0) reads,
Sum (e. phyrds-nvl (B. phyrds, 0 ))/
(Select extract (day from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME )*
86400 +
EXTRACT (hour from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 3600 +
EXTRACT (minute from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 60 +
EXTRACT (second from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
Where B. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-3/24 and end_interval_time <sysdate-2/24 and rownum <2)
And e. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-1/24 and rownum <2)
And B. STARTUP_TIME = E. STARTUP_TIME
And B. END_INTERVAL_TIME <E. END_INTERVAL_TIME) rps,
Decode (sum (e. phyrds-nvl (B. phyrds, 0 )),
0,
0,
10 * (sum (e. readtim-nvl (B. readtim, 0 ))/
Sum (e. phyrds-nvl (B. phyrds, 0) atpr,
Decode (sum (e. phyrds-nvl (B. phyrds, 0 )),
0,
0,
Sum (e. phyblkrd-nvl (B. phyblkrd, 0 ))/
Sum (e. phyrds-nvl (B. phyrds, 0) bpr,
Sum (e. phywrts-nvl (B. phywrts, 0) writes,
Sum (e. phywrts-nvl (B. phywrts, 0 ))/
(Select extract (day from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME )*
86400 +
EXTRACT (hour from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 3600 +
EXTRACT (minute from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 60 +
EXTRACT (second from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
Where B. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-3/24 and end_interval_time <sysdate-2/24 and rownum <2)
And e. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-1/24 and rownum <2)
And B. STARTUP_TIME = E. STARTUP_TIME
And B. END_INTERVAL_TIME <E. END_INTERVAL_TIME) wps,
Sum (e. wait_count-nvl (B. wait_count, 0) waits,
Decode (sum (e. wait_count-nvl (B. wait_count, 0 )),
0,
0,
10 * (sum (e. time-nvl (B. time, 0 ))/
Sum (e. wait_count-nvl (B. wait_count, 0) atpwt,
Sum (e. phyrds-nvl (B. phyrds, 0) +
Sum (e. phywrts-nvl (B. phywrts, 0) ios
From dba_hist_filestatxs e, dba_hist_filestatxs B
Where B. snap_id = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-3/24 and end_interval_time <sysdate-2/24 and rownum <2)
And e. snap_id = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-1/24 and rownum <2)
And B. dbid = e. dbid
And B. instance_number = e. instance_number
And B. tsname = e. tsname
And B. file # = e. file #
And B. creation_change # = e. creation_change #
And (e. phyrds-nvl (B. phyrds, 0) + (e. phywrts-nvl (B. phywrts, 0)> 0
Group by e. tsname
Union all
Select e. tsname,
Sum (e. phyrds-nvl (B. phyrds, 0) reads,
Sum (e. phyrds-nvl (B. phyrds, 0 ))/
(Select extract (day from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME )*
86400 +
EXTRACT (hour from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 3600 +
EXTRACT (minute from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 60 +
EXTRACT (second from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
Where B. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-3/24 and end_interval_time <sysdate-2/24 and rownum <2)
And e. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-1/24 and rownum <2)
And B. END_INTERVAL_TIME <E. END_INTERVAL_TIME) rps,
Decode (sum (e. phyrds-nvl (B. phyrds, 0 )),
0,
0,
(Sum (e. readtim-nvl (B. readtim, 0 ))/
Sum (e. phyrds-nvl (B. phyrds, 0) * 10) atpr,
Decode (sum (e. phyrds-nvl (B. phyrds, 0 )),
0,
To_number (NULL ),
Sum (e. phyblkrd-nvl (B. phyblkrd, 0 ))/
Sum (e. phyrds-nvl (B. phyrds, 0) bpr,
Sum (e. phywrts-nvl (B. phywrts, 0) writes,
Sum (e. phywrts-nvl (B. phywrts, 0 ))/
(Select extract (day from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME )*
86400 +
EXTRACT (hour from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 3600 +
EXTRACT (minute from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 60 +
EXTRACT (second from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME)
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E
Where B. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-3/24 and end_interval_time <sysdate-2/24 and rownum <2)
And e. SNAP_ID = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-1/24 and rownum <2)
And B. STARTUP_TIME = E. STARTUP_TIME
And B. END_INTERVAL_TIME <E. END_INTERVAL_TIME) wps,
Sum (e. wait_count-nvl (B. wait_count, 0) waits,
Decode (sum (e. wait_count-nvl (B. wait_count, 0 )),
0,
0,
(Sum (e. time-nvl (B. time, 0 ))/
Sum (e. wait_count-nvl (B. wait_count, 0) * 10) atpwt,
Sum (e. phyrds-nvl (B. phyrds, 0) +
Sum (e. phywrts-nvl (B. phywrts, 0) ios
From dba_hist_tempstatxs e, dba_hist_tempstatxs B
Where B. snap_id = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-3/24 and end_interval_time <sysdate-2/24 and rownum <2)
And e. snap_id = (select snap_id from DBA_HIST_SNAPSHOT where end_interval_time> sysdate-1/24 and rownum <2)
And B. dbid = e. dbid
And B. instance_number = e. instance_number
And B. tsname = e. tsname
And B. file # = e. file #
And B. creation_change # = e. creation_change #
And (e. phyrds-nvl (B. phyrds, 0) + (e. phywrts-nvl (B. phywrts, 0)> 0
Group by e. tsname
Order by ios desc, tsname;