--# ----------------------------------------------------------------------
--# Script  : wrh_sysstat_ioworkload.sql
--# Author  : thecheapdba
--# Tested  : Oracle 10.2
--# Version : 2007/08/07
--# Purpose : Report on IOPS & MBPS over a period of time as seen by DB.
--# ----------------------------------------------------------------------
--# NOTES   : Assumes no database restart between snap IDs selected
--# ----------------------------------------------------------------------

set echo     off
set feedback off
set heading  off
set linesize 132
set pagesize 55
set verify   off

prompt
prompt
prompt Enter the number of days to look for snapshot IDs
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SELECT dba_hist_database_instance.instance_name,
       dba_hist_database_instance.db_name,
       dba_hist_snapshot.snap_id,
       to_char(dba_hist_snapshot.begin_interval_time,'MM/DD/YYYY:HH24:MI') begin_snap_time,
       to_char(dba_hist_snapshot.end_interval_time,'MM/DD/YYYY:HH24:MI')   end_snap_time,
       decode(dba_hist_snapshot.startup_time,dba_hist_snapshot.begin_interval_time,'**** database restart ****',null) db_bounce
  from dba_hist_snapshot,
       dba_hist_database_instance
 where dba_hist_database_instance.dbid             = dba_hist_snapshot.dbid
   and dba_hist_database_instance.instance_number  = dba_hist_snapshot.instance_number
   and dba_hist_database_instance.startup_time     = dba_hist_snapshot.startup_time
   and dba_hist_snapshot.end_interval_time >= to_date(sysdate - &&num_days_back)
 order by db_name, instance_name, snap_id;

prompt
prompt
prompt Enter Begining snapshot ID
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt &&beg_snap_id
prompt
prompt Enter Ending   snapshot ID
prompt ^^^^^^^^^^^^^^^^^^^^^^^^^^
prompt &&end_snap_id

set termout off
column rpt new_value rpt
SELECT instance_name||'_wrh_ioworkload_'||&&beg_snap_id||'_'||&&end_snap_id||'.LST' rpt from v$instance;
set termout on
prompt
prompt
prompt ^^^^^^^^^^^^^
prompt Report Name : ../LST/&&rpt
prompt ^^^^^^^^^^^^^
spool ../LST/&&rpt

column db_bounce new_value db_bounce
column sr1       new_value sr1
column sw1       new_value sw1
column lr1       new_value lr1
column lw1       new_value lw1
column tbr1      new_value tbr1
column tbw1      new_value tbw1
set termout off
SELECT 
sum(decode(stat_name,'physical read total IO requests',value,0)- decode(stat_name,'physical read total multi block requests',value,0)) sr1,
sum(decode(stat_name,'physical write total IO requests',value,0)- decode(stat_name,'physical write total multi block requests',value,0)) sw1,
sum(decode(stat_name,'physical read total multi block requests',value,0)) lr1,
sum(decode(stat_name,'physical write total multi block requests',value,0)) lw1,
sum(decode(stat_name,'physical read total bytes',value,0)) tbr1,
sum(decode(stat_name,'physical write total bytes',value,0)) tbw1
FROM wrh$_sysstat, wrh$_stat_name
WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
AND wrh$_sysstat.snap_id = &&beg_snap_id ;

SELECT decode(startup_time,begin_interval_time,1,0) db_bounce
from dba_hist_snapshot 
where snap_id = &&end_snap_id;
SELECT 
decode(&&db_bounce,1,0,&&sr1) sr1,
decode(&&db_bounce,1,0,&&sw1) sw1,
decode(&&db_bounce,1,0,&&lr1) lr1,
decode(&&db_bounce,1,0,&&lw1) lw1,
decode(&&db_bounce,1,0,&&tbr1) tbr1,
decode(&&db_bounce,1,0,&&tbw1) tbw1
FROM dual;
set termout on

prompt
prompt
prompt ^^^^^^^^^^^^
prompt First Sample
prompt ^^^^^^^^^^^^
prompt Number of Small Reads : &&sr1
prompt Number of Small Writes: &&sw1
prompt Number of Large Reads : &&lr1
prompt Number of Large Writes: &&lw1
prompt Total Bytes Read      : &&tbr1
prompt Total Bytes Written   : &&tbw1
prompt
prompt

column sr2  new_value sr2
column sw2  new_value sw2
column lr2  new_value lr2
column lw2  new_value lw2
column tbr2 new_value tbr2
column tbw2 new_value tbw2
set termout off
SELECT 
sum(decode(stat_name,'physical read total IO requests',value,0)- decode(stat_name,'physical read total multi block requests',value,0)) sr2,
sum(decode(stat_name,'physical write total IO requests',value,0)- decode(stat_name,'physical write total multi block requests',value,0)) sw2,
sum(decode(stat_name,'physical read total multi block requests',value,0)) lr2,
sum(decode(stat_name,'physical write total multi block requests',value,0)) lw2,
sum(decode(stat_name,'physical read total bytes',value,0)) tbr2,
sum(decode(stat_name,'physical write total bytes',value,0)) tbw2
FROM wrh$_sysstat, wrh$_stat_name
WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
AND wrh$_sysstat.snap_id = &&end_snap_id;
set termout on

prompt
prompt
prompt ^^^^^^^^^^^^^
prompt Second Sample
prompt ^^^^^^^^^^^^^
prompt Number of Small Reads : &&sr2
prompt Number of Small Writes: &&sw2
prompt Number of Large Reads : &&lr2
prompt Number of Large Writes: &&lw2
prompt Total Bytes Read      : &&tbr2
prompt Total Bytes Written   : &&tbw2
prompt
prompt
prompt ^^^^^^^^^
prompt Results :
prompt ^^^^^^^^^

column inttime new_value inttime
column sri new_value sri
column swi new_value swi
column tsi new_value tsi
column srp new_value srp
column swp new_value swp
column lri new_value lri
column lwi new_value lwi
column tli new_value tli
column lrp new_value lrp
column lwp new_value lwp
column tr  new_value tr 
column tw  new_value tw 
column tm  new_value tm 
set termout off
SELECT 
(extract(day    from (end.end_interval_time - beg.end_interval_time))*86400)+
(extract(hour   from (end.end_interval_time - beg.end_interval_time))*3600)+
(extract(minute from (end.end_interval_time - beg.end_interval_time))*60)+
(extract(second from (end.end_interval_time - beg.end_interval_time))*01) inttime
from (select end_interval_time from dba_hist_snapshot where snap_id = &&beg_snap_id) beg,
(select end_interval_time from dba_hist_snapshot where snap_id = &&end_snap_id) end;

SELECT
ROUND((&&sr2-&&sr1)/(&&inttime)),3) sri,
ROUND((&&sw2-&&sw1)/(&&inttime)),3) swi,
ROUND(((&&sr2-&&sr1)+(&&sw2-&&sw1))/(&&inttime)),3) tsi,
ROUND((&&sr2-&&sr1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) srp,
ROUND((&&sw2-&&sw1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) swp,
ROUND((&&lr2-&&lr1)/(&&inttime)),3) lri,
ROUND((&&lw2-&&lw1)/(&&inttime)),3) lwi,
ROUND(((&&lr2-&&lr1)+(&&lw2-&&lw1))/(&&inttime)),3) tli,
ROUND((&&lr2-&&lr1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lrp,
ROUND((&&lw2-&&lw1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lwp,
ROUND(((&&tbr2-&&tbr1)/(&&inttime)))/1048576,3) tr,
ROUND(((&&tbw2-&&tbw1)/(&&inttime)))/1048576,3) tw,
ROUND((((&&tbr2-&&tbr1)+(&&tbw2-&&tbw1))/(&&inttime)))/1048576,3) tm
FROM dual;
set termout on

prompt Small Read  IOPS  = &&sri IOPS
prompt Small Write IOPS  = &&swi IOPS
prompt Total Small IOPS  = &&tsi IOPS
prompt Small Read  I/O % = &&srp %
prompt Small Write I/O % = &&swp %
prompt Large Read  IOPS  = &&lri IOPS
prompt Large Write IOPS  = &&lwi IOPS
prompt Total Large IOPS  = &&tli IOPS
prompt Large Read  I/O % = &&lrp %
prompt Large Write I/O % = &&lwp %
prompt Total Read        = &&tr MBPS
prompt Total Written     = &&tw MBPS
prompt Total MBPS        = &&tm MBPS

spool off
undefine sleeptime
undefine num_days_back
undefine beg_snap_id
undefine end_snap_id
undefine db_bounce