------------------------------------------------------------
-- file		ashsqlpcte.sql
-- desc		ASH system   level Find SQL by event (‹min› ‹partial event›)
-- author	Craig A. Shallahamer, craig@orapub.com
-- orig		02-April-07
-- lst upt	20-Feb-09 
-- copyright	(c)2007,2009 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
------------------------------------------------------------


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

def sleep_time_min=&1
def partial_event=&2

col val1 new_val tot_count

select	count(*) val1
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%'
  and   ash.session_state = 'WAITING'
  and	ash.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    = 'ashsqlpcte.sql'
def osm_title   = 'System Level ASH: Find SQL by event (last &sleep_time_min min)'

start osmtitle

col event       format a38      heading "Wait Event" trunc
col time_pct    format 990.00   heading "% Time|Waited"
	
set linesize 90

select	ash.event,
		ash.sql_id,
		sa.address,
		sa.hash_value,
		100*(count(*)/&tot_count) time_pct
from	v$active_session_history ash,
		v$sqlarea sa
where	ash.sample_time between (sysdate-&sleep_time_min/(60*24)) and sysdate
  and   ash.session_state = 'WAITING'
  and	ash.event not in
		(	select a.event
            from   o$event_type a
            where  a.type in ('bogus','idle')
        )
  and 	ash.sql_id = sa.sql_id
  and	ash.event like '&partial_event%'
group by ash.event,ash.sql_id,sa.address,sa.hash_value
having 100*(count(*)/&tot_count) › 1
order by time_pct desc
/



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;