@@header

set term off
/*
*
*  Author  : Vishal Gupta
*  Purpose : Display Tablespace IO statistics from statspack data
*  To Do   : 1 - Ajust script for 
*  Arguments: 1 - Tablespace Name, you can password wild characters like %
*             2 - Number of days of data to consider
*             3 - DBID
*             4 - instance_number
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Jan-09  Vishal Gupta  First Draft
*/
set term on


define tablespace_name="&&1"
define days="&&2"
define dbid="&&3"
define instance_number="&&4"


WITH filestatxs
as
(select f.dbid, f.instance_number, f.snap_id, s.snap_time
     , f.tsname, f.file#, f.filename
     , DECODE( f.singleblkrds  - LAG (f.singleblkrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#)
             , 0, 0
             , (   ( f.singleblkrdtim - LAG (f.singleblkrdtim) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
                 / ( f.singleblkrds   - LAG (f.singleblkrds  ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )  
               ) * 10 
             ) singleblkreadtime
     , DECODE( f.phyrds  - LAG (f.phyrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#)
             , 0, 0
             , ( ( f.readtim - LAG (f.readtim) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
               / ( f.phyrds  - LAG (f.phyrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) ) 
               ) * 10 
             ) readtime
     , DECODE( f.phywrts  - LAG (f.phywrts ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#)
             , 0, 0
             , (  ( f.writetim - LAG (f.writetim) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )
                / ( f.phywrts  - LAG (f.phywrts ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) )  
               ) * 10 
             ) writetime
     , f.singleblkrds  - LAG (f.singleblkrds ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) singleblkreads
     , f.phyrds        - LAG (f.phyrds       ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) reads
     , f.phywrts       - LAG (f.phywrts      ) OVER (ORDER BY f.dbid, f.instance_number, f.snap_id, f.tsname, f.file#) writes
from stats$database_instance i
    , stats$snapshot s
    , stats$filestatxs f
where (i.dbid, i.instance_number,i.startup_time) in (select max(dbid), instance_number, max(startup_time) 
                                                       from stats$database_instance i2
                                                       where i2.dbid = &dbid
                                                         and i2.instance_number = &instance_number
                                                      group by instance_name, instance_number
                                                      )
and s.dbid = i.dbid
and s.instance_number = i.instance_number
and f.dbid = s.dbid
and f.instance_number = s.instance_number
and f.snap_id = s.snap_id
and s.dbid = &dbid
and s.instance_number = &instance_number
and s.snap_time > sysdate - &days
and f.tsname like '&tablespace_name' )
select f2.snap_time
     , f2.tsname
     , ROUND(avg(singleblkreadtime),2) "AvgSingleBlockRead(ms)"  
     , ROUND(avg(readtime),2)          "AvgRead(ms)"  
     , ROUND(avg(writetime),2)         "AvgWrite(ms)"  
     , SUM(singleblkreads)             tot_singleblkreads
     , SUM(reads) - SUM(singleblkreads) tot_multiblkreads
     , SUM(reads)                      tot_reads
     , SUM(writes)                     tot_writes
from filestatxs f2
group by f2.snap_time, f2.tsname
order by f2.snap_time desc
;


@@footer