-- ********************************************************************
-- * Copyright Notice   : (c)2008,2009,2011 OraPub, Inc.
-- * Filename		: iosumx.sql - based completely on v$sysstat
-- * Author		: Craig A. Shallahamer
-- * Original		: 23-DEC-2008
-- * Last Update	: 20-DEC-2011
-- * Description	: Oracle IO summary (blue line)
-- * Usage		: start iosumx.sql
-- ********************************************************************

prompt Remember: This report must be run twice so both the initial and
prompt final values are available. If no output, press ENTER a few times.

def osm_prog	= 'iosumx.sql'
def osm_title	= 'Oracle IO Interval Summary'
def osm_title2	= '(v$sysstat, excl ARCH)'

set termout off
col interval new_val interval
col srvr_r_iop_t0 new_val srvr_r_iop_t0
col tot_w_iop_t0 new_val tot_w_iop_t0
col srvr_r_mb_t0 new_val srvr_r_mb_t0
col tot_w_mb_t0  new_val tot_w_mb_t0
col tot_r_w_mb_t0 new_val tot_r_w_mb_t0
col dbwr_srvr_w_iop_t0 new_val dbwr_srvr_w_iop_t0
col dbwr_srvr_w_mb_t0 new_val dbwr_srvr_w_mb_t0
col lgwr_w_iop_t0 new_val lgwr_w_iop_t0
col lgwr_w_mb_t0 new_val lgwr_w_mb_t0
select 	(sysdate-to_date(load_date,'YYYYMMDDHH24MISS'))*24*60*60 interval,
	srvr_r_iop srvr_r_iop_t0,
	tot_w_iop tot_w_iop_t0,
	srvr_r_mb srvr_r_mb_t0,
	tot_w_mb tot_w_mb_t0,
	tot_r_w_mb tot_r_w_mb_t0,
	dbwr_srvr_w_iop dbwr_srvr_w_iop_t0,
	dbwr_srvr_w_mb  dbwr_srvr_w_mb_t0,
	lgwr_w_iop lgwr_w_iop_t0,
	lgwr_w_mb  lgwr_w_mb_t0
from   iosumx
/

start osmtitle
set linesize 100
col x format a80
set termout on
set heading off verify off
select
        'IOP/s and IOP' x,
	'   Total Read                 : '||lpad(round(((srvr_r_iop.value)-&srvr_r_iop_t0)/&interval,3),15)||lpad(round(((srvr_r_iop.value)-&srvr_r_iop_t0),3),15) x,
        '   Total Write                : '||lpad(round((((dbwr_srvr_w_iop.value)+(lgwr_w_iop.value)-&tot_w_iop_t0)/&interval),3),15)||lpad(round(((dbwr_srvr_w_iop.value)+(lgwr_w_iop.value)-&tot_w_iop_t0),3),15) x,
        '   Total R+W                  : '||lpad(round((((srvr_r_iop.value)+(dbwr_srvr_w_iop.value)+(lgwr_w_iop.value)-&srvr_r_iop_t0-&tot_w_iop_t0)/&interval),3),15)||lpad(round(((srvr_r_iop.value)+(dbwr_srvr_w_iop.value)+(lgwr_w_iop.value)-&srvr_r_iop_t0-&tot_w_iop_t0),3),15)  x,
        'MB/s and MB' x,
	'   Total Read                 : '||lpad(round(((srvr_r_mb.v)-&srvr_r_mb_t0)/&interval,3),15)||lpad(round((srvr_r_mb.v)-&srvr_r_mb_t0,3),15) x,
        '   Total Write                : '||lpad(round((((dbwr_srvr_w_mb.v)+(lgwr_w_mb.v)-&tot_w_mb_t0)/&interval),3),15)||lpad(round((dbwr_srvr_w_mb.v)+(lgwr_w_mb.v)-&tot_w_mb_t0,3),15) x,
        '   Total R+W                  : '||lpad(round((((srvr_r_mb.v)+(dbwr_srvr_w_mb.v)+(lgwr_w_mb.v)-&tot_r_w_mb_t0)/&interval),3),15)||lpad(round((srvr_r_mb.v)+(dbwr_srvr_w_mb.v)+(lgwr_w_mb.v)-&tot_r_w_mb_t0,3),15) x,
        'Detailed Component Data' x,
	'   Interval (s)               : '||lpad(&interval,15) x,
	'   SRVR Read IOP/s, IOP       : '||lpad(round(((srvr_r_iop.value)-&srvr_r_iop_t0)/&interval,3),15)||lpad(round((srvr_r_iop.value)-&srvr_r_iop_t0,3),15) x,
        '   SRVR Read MB/s, MB         : '||lpad(round(((srvr_r_mb.v)-&srvr_r_mb_t0)/&interval,3),15)||lpad(round((srvr_r_mb.v)-&srvr_r_mb_t0,3),15) x,
        '   DBWR+SRVR Write IOP/s, IOP : '||lpad(round(((dbwr_srvr_w_iop.value)-&dbwr_srvr_w_iop_t0)/&interval,3),15)||lpad(round((dbwr_srvr_w_iop.value)-&dbwr_srvr_w_iop_t0,3),15) x,
        '   DBWR+SRVR Write MB/s, MB   : '||lpad(round(((dbwr_srvr_w_mb.v)-&dbwr_srvr_w_mb_t0)/&interval,3),15)||lpad(round((dbwr_srvr_w_mb.v)-&dbwr_srvr_w_mb_t0,3),15) x,
        '   LGWR Write IOP/s, IOP      : '||lpad(round(((lgwr_w_iop.value)-&lgwr_w_iop_t0)/&interval,3),15)||lpad(round((lgwr_w_iop.value)-&lgwr_w_iop_t0,3),15) x,
        '   LGWR Write MB/s, MB        : '||lpad(round(((lgwr_w_mb.v)-&lgwr_w_mb_t0)/&interval,3),15)||lpad(round((lgwr_w_mb.v)-&lgwr_w_mb_t0,3),15) x
from    (
                select  value
                from    v$sysstat
                where   name = 'physical read total IO requests'
        ) srvr_r_iop,
        (
                select  value/(1024*1024) v
                from    v$sysstat
                where   name = 'physical read total bytes'
        ) srvr_r_mb,
        (
                select  value
                from    v$sysstat
                where   name = 'physical write total IO requests'
        ) dbwr_srvr_w_iop,
        (
                select  value/(1024*1024) v
                from    v$sysstat
                where   name = 'physical write total bytes'
        ) dbwr_srvr_w_mb,
        (
                select  value
                from    v$sysstat
                where   name = 'redo writes'
        ) lgwr_w_iop,
        (
                select  value/(1024*1024) v
                from    v$sysstat
                where   name = 'redo size'
        ) lgwr_w_mb,
	(
		select	to_date(load_date,'YYYYMMDDHH24MISS') load_date,
			srvr_r_iop, tot_w_iop, tot_r_w_iop,
			srvr_r_mb, tot_w_mb, tot_r_w_mb,
			dbwr_srvr_w_iop,
			dbwr_srvr_w_mb,
			lgwr_w_iop,
			lgwr_w_mb
		from	iosumx
	) t0
/

set termout off
drop table iosumx;
create table iosumx nologging as
select
	to_char(sysdate,'YYYYMMDDHH24MISS') load_date,
	(srvr_r_iop.value) srvr_r_iop,
	(dbwr_srvr_w_iop.value)+(lgwr_w_iop.value) tot_w_iop,
	(srvr_r_iop.value)+(dbwr_srvr_w_iop.value)+(lgwr_w_iop.value) tot_r_w_iop,
	(srvr_r_mb.v) srvr_r_mb,
	(dbwr_srvr_w_mb.v)+(lgwr_w_mb.v) tot_w_mb,
	(srvr_r_mb.v)+(dbwr_srvr_w_mb.v)+(lgwr_w_mb.v) tot_r_w_mb,
	(dbwr_srvr_w_iop.value) dbwr_srvr_w_iop,
	(dbwr_srvr_w_mb.v) dbwr_srvr_w_mb,
	(lgwr_w_iop.value) lgwr_w_iop,
	(lgwr_w_mb.v) lgwr_w_mb
from	(
		select 	value
		from 	v$sysstat
		where	name = 'physical read total IO requests'
	) srvr_r_iop,
	(
		select 	value/(1024*1024) v
		from 	v$sysstat
		where	name = 'physical read total bytes'
	) srvr_r_mb,
	(
		select 	value
		from 	v$sysstat
		where	name = 'physical write total IO requests'
	) dbwr_srvr_w_iop,
	(
		select 	value/(1024*1024) v
		from 	v$sysstat
		where	name = 'physical write total bytes'
	) dbwr_srvr_w_mb,
	(
		select 	value
		from 	v$sysstat
		where	name = 'redo writes'
	) lgwr_w_iop,
	(
		select 	value/(1024*1024) v
		from 	v$sysstat
		where	name = 'redo size'
	) lgwr_w_mb
/
set termout on

start osmclear