------------------------------------------------------------
-- file		swpctx.sql
-- desc		Session wait statitics CHANGE percentage reporting.
-- author	Craig A. Shallahamer, craig@orapub.com
-- orig		03-aug-00
-- lst upt	03-April-07 
-- copyright	(c)2000,2001,2005,2006, 2007 OraPub, Inc.

-- It is possible to get percentages greater/lesser than 100% because there
-- is a time lag between the total calculations (which queries
-- from v$system_event) and the report query (which also queries from
-- v$system_event).
------------------------------------------------------------

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

set echo off
set feedback off
set heading off
set verify off
set termout off

def old_tot_time_waited=&tot_time_waited noprint
def old_tot_total_waits=&tot_total_waits noprint

col val1 new_val tot_time_waited 
col val2 new_val tot_total_waits

select	sum(b.time_waited) val1,
		sum(b.total_waits) val2
from	v$system_event b
where	b.event not in ( select a.event
			         from   o$event_type a
			         where  a.type in ('bogus','idle')
		 	       )
/

set echo off
set feedback off
set heading on
set verify off
set termout on

def osm_prog	= 'swpctx.sql'
def osm_title	= 'System Event CHANGE (blue line) Activity By PERCENT'

start osmtitle

col event	format a38	 heading "Wait Event" trunc
col tw	 	format 9990.000	 heading "Time Waited|(sec)"
col time_pct 	format 990.00	 heading "% Time|Waited"
col avg_time 	format 99990.000 heading "Avg Time|Waited (ms)"
col wc 		format 9990	 heading "Wait|Count(k)"

select 	b.event,
	((b.time_waited-a.time_waited)/100) tw,
	100*((b.time_waited-a.time_waited)/(&tot_time_waited-&old_tot_time_waited+0.0001)) time_pct,
	1000*((b.time_waited-a.time_waited)/100)/(b.total_waits-a.total_waits) avg_time,
	(b.total_waits-a.total_waits)/1000 wc
from   v$system_event b,
       system_event_snap a
where  b.event = a.event
  and  b.event not in ( select x.event
		        from   o$event_type x
		        where  x.type in ('bogus','idle')
		      )
  and  (b.total_waits-a.total_waits) › 0
order by time_pct desc
/

truncate table system_event_snap
/
insert /*+ APPEND */ into system_event_snap nologging
select * 
from   v$system_event b
where  b.event not in ( select a.event
			from   o$event_type a
			where  a.type in ('bogus','idle')
		       )
/
commit;

start osmclear