-- ********************************************************************
-- * Copyright Notice   : (c)2008,2009 OraPub, Inc.
-- * Filename		: iosum9.sql - based on v$filestat, v$sysstat
-- * Author		: Craig A. Shallahamer
-- * Original		: 23-DEC-2008
-- * Last Update	: 23-DEC-2008
-- * Description	: Oracle IO summary (red line)
-- * Usage		: start iosum9.sql
-- * Note  		: I know the code is VERY bad, but it is very easy to
-- *			  modify for various Oracle releases.
-- *			  This report is based on v$filestat as much as possible.
-- ********************************************************************

def osm_prog	= 'iosum9.sql'
def osm_title	= 'IO Summary (v$filestat,v$sysstat) Since Instance Startup'

set termout off
col blksize new_value blksize
select value blksize
from v$parameter
where name = 'db_block_size';
set termout on

start osmtitle
set linesize 60

col x format a50

set heading off

select
	'KIOPs Total Read        : '||lpad(round((srvr_r_iop.value)/1000,3),15) x,
	'      Total Write       : '||lpad(round((((dbwr_srvr_w_iop.value)+(lgwr_w_iop.value))/1000),3),15) x,
	'      Total R+W         : '||lpad(round((((srvr_r_iop.value)+(dbwr_srvr_w_iop.value)+(lgwr_w_iop.value))/1000),3),15) x,
	'MBs   Total Read        : '||lpad(round((srvr_r_mb.v),3),15) x,
	'      Total Write       : '||lpad(round(((dbwr_srvr_w_mb.v)+(lgwr_w_mb.v)),3),15) x,
	'      Total R+W         : '||lpad(round(((srvr_r_mb.v)+(dbwr_srvr_w_mb.v)+(lgwr_w_mb.v)),3),15) x,
	'Component Details' x,
	'  SRVR Read  KIOPs      : '||lpad(round((srvr_r_iop.value)/1000,3),15) x,
	'  SRVR Read  MBs        : '||lpad(round((srvr_r_mb.v),3),15) x,
	'  DBWR+SRVR Write KIOPs : '||lpad(round((dbwr_srvr_w_iop.value)/1000,3),15) x,
	'  DBWR+SRVR Write MBs   : '||lpad(round((dbwr_srvr_w_mb.v),3),15) x,
	'  LGWR Write KIOPs      : '||lpad(round((lgwr_w_iop.value)/1000,3),15) x,
	'  LGWR Write MBs        : '||lpad(round((lgwr_w_mb.v),3),15) x
from	(
		select 	sum(phyrds) value
		from 	v$filestat
	) srvr_r_iop,
	(
		select 	sum((phyblkrd*&blksize))/(1024*1024) v
		from 	v$filestat
	) srvr_r_mb,
	(
		select 	sum(phywrts) value
		from 	v$filestat
	) dbwr_srvr_w_iop,
	(
		select 	sum((phyblkwrt*&blksize))/(1024*1024) v
		from 	v$filestat
	) 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
/

start osmclear