/*
 * Title  : sho_tpd.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Shows transactions per day.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on

set linesize 120
set pagesize 24

set heading on
set headsep on
set underline on
set colsep " "

set verify off
set escape off
set embedded on

col "tpd"	heading "Transactions/Day"	format 999,999,999,999
col "rpd"	heading "Reads/Day"		format 999,999,999,999
col "wpd"	heading "Writes/Day"		format 999,999,999,999

spool &&rpt_dir/sho_tpd.rpt

select
	distinct
	reads/days		as rpd,
	writes/days		as wpd,
	(reads+writes)/days	as tpd
from
	(
		select 
			value as reads 
		from 
			v$sysstat 
		where 
			name='user calls'
	),
	(
		select 
			sum(value) as writes 
		from 
			v$sysstat 
		where 
			name in ('user commits','user rollbacks')
	),
	(
		select 
			sysdate-startup_time as days 
		from 
			v$instance
	);


spool off;

clear columns

set embedded off
set escape off
set verify on

set colsep " "
set underline on
set headsep on
set heading on

set pagesize 24
set linesize 80

set serverout off
set feedback on
set echo off
