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

def osm_prog	= 'iosum.sql'
def osm_title	= 'Oracle IO Summary Since Instance Startup'
def osm_title2  = '(v$sysstat, excl ARCH)'

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 	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 'read:  '||sum(total_waits) from v$system_event where event like '%read%';
--select 'write: '||sum(total_waits) from v$system_event where event like '%write%';

start osmclear