------------------------------------------------------------
-- file		ashrt.sql
-- desc		ASH system level RTA report, last x min (‹min›)
-- author	Craig A. Shallahamer, craig@orapub.com
-- orig		20-Feb-2009
-- lst upt	03-Sep-2009 
-- copyright	(c)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 sql_id=&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.session_state in ('WAITING','ON CPU')
  and   ash.sql_id like '&sql_id'
  and	ash.event not in
		(	select a.event
            from   o$event_type a
            where  a.type in ('bogus','idle')
        )
/
col val1 new_val cpu_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.session_state in ('ON CPU')
  and   ash.sql_id like '&sql_id'
  and	ash.event not in
		(	select a.event
            from   o$event_type a
            where  a.type in ('bogus','idle')
        )
/
col val1 new_val io_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.session_state in ('WAITING')
  and   ash.sql_id like '&sql_id'
  and	ash.event in
		(	select a.event
            from   o$event_type a
            where  a.type in ('ior','iow')
        )
/

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




def osm_prog    = 'ashrt.sql'
def osm_title   = 'ASH Response Time Analysis Report (last &sleep_time_min min, SQL_ID=&sql_id)'

start osmtitle

def small=0.0000000000010

col st 		format 9990.00 heading "% CPU Time"
col qt_tot 	format 9990.00 heading "% Wait Time"
col qt_io 	format 9990.00 heading "% Wait IO"
col qt_ot 	format 9990.00 heading "% Wait Other"

select 100*(&cpu_count/&tot_count) st,
       100*(1-(&cpu_count/&tot_count)) qt_tot,
       100*(&io_count/(&tot_count-&cpu_count)) qt_io,
       100*((&tot_count-&cpu_count-&io_count)/(&io_count+&small)) qt_ot
from   dual;

col event       format a38      heading "Activity Detail" trunc
col time_pct    format 990.00   heading "% Time"

start osmclear

select	decode(ash.session_state,'WAITING',ash.event,'ON CPU','CPU','Dunno') event,
	100*(count(*)/&tot_count) time_pct
from	v$active_session_history ash
where	ash.sample_time between (sysdate-&sleep_time_min/(60*24)) and sysdate
  and	ash.session_state in ('WAITING','ON CPU')
  and   ash.sql_id like '&sql_id'
  and	ash.event not in
		(	select a.event
            		from   o$event_type a
            		where  a.type in ('bogus','idle')
        	)
group by decode(ash.session_state,'WAITING',ash.event,'ON CPU','CPU','Dunno')
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;