------------------------------------------------------------
-- file		ashepctes.sql
-- desc		ASH system level by event, sid, wait pct (‹min› ‹partial event›)
-- author	Craig A. Shallahamer, craig@orapub.com
-- orig		02-April-07
-- lst upt	09-May-07 
-- copyright	(c)2007 OraPub, Inc.

-- It is possible to get percentages greater/lesser than 100% because there
-- is a time lag between the total calculations and the report query
------------------------------------------------------------

def sleep_time_min=&1
def partial_event=&2

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

def osm_prog    = 'ashpctes.sql'
def osm_title   = 'System Level ASH By Event, SID, Wait Percent (last &sleep_time_min min)'

start osmtitle

col event       format a30      heading "Wait Event" trunc
col time_pct    format 990.00   heading "% Time|Waited"
col session_id	format 99999	heading "SID"	
col ser		format 99999	heading "Serial"	

-- select	by_sess.event,by_sess.session_id,by_sess.wait_time,by_event.wait_time,by_sess.wait_time/by_event.wait_time pct_thing

select	by_sess.event,
	by_sess.session_id,
	by_sess.session_serial# ser,
	100*(by_sess.tw_time/by_event.tw_time) time_pct
from	(
			select	ash.event,ash.session_id,ash.session_serial#,
				sum(ash.wait_time+ash.time_waited) tw_time
			from	v$active_session_history ash
			where	ash.sample_time between (sysdate-&sleep_time_min/(60*24)) and sysdate
			  and	ash.event like '%&partial_event%'
			group by ash.event,ash.session_id,ash.session_serial#
		) by_sess,
		(
			select	tot.event,sum(tot.wait_time+tot.time_waited) tw_time
			from	v$active_session_history tot
			where	tot.sample_time between (sysdate-&sleep_time_min/(60*24)) and sysdate
			  and	tot.event like '%&partial_event%'
			group by tot.event
		) by_event
where	by_sess.event = by_event.event
  and	by_event.tw_time › 0
  and   100*(by_sess.tw_time/by_event.tw_time) › 0.001
order by 1,4 desc,2,3
/


start osmclear

--This may be helpful
--select	to_char(sysdate,'DD-Mon-YY HH24:MI:SS') now,
--		to_char(sysdate-24/24,'DD-Mon-YY HH24:MI:SS') one_day_prior,
--		to_char(sysdate-1/24,'DD-Mon-YY HH24:MI:SS') one_hour_prior,
--		to_char(sysdate-1/(60*24),'DD-Mon-YY HH24:MI:SS') one_min_prior,
--		to_char(sysdate-30/(60*24),'DD-Mon-YY HH24:MI:SS') thirty_min_prior
--from dual;