--
-- File:	irtviews.sql - Create's interactive response time views
-- Author:	Craig Shallahamer (craig@orapub.com)
-- Orig Date:	May, 2001
-- Last Updt:	17-March-07
--

prompt
prompt file: irtviews.sql
prompt
prompt This script will create OraPub's interactive reporting 
prompt response time views.  
prompt
prompt Press ENTER to continue.
accept x

create or replace view o$i_cpu_time_tot_sec as
        select  sum(value/100) cpu_time_tot_sec
        from    o$sysstat
        where   name = 'CPU used by this session'
/

start snap_stats.sql  -- The o$i_system_event table must first be created.

create or replace view o$i_io_wtime_tot_sec as
		select	sum(time_waited)/100 io_wtime_tot_sec
		from	o$i_system_event
		where	event in ( select a.event
				   from   o$event_type a
				   where  a.type in ('ior','iow')
				 )
/
create or replace view o$i_io_wtime_write_sec as
		select	sum(time_waited)/100 io_wtime_write_sec
		from	o$i_system_event
		where	event in ( select a.event
				   from   o$event_type a
				   where  a.type in ('iow')
				 )
/
create or replace view o$i_io_wtime_read_sec as
		select	sum(time_waited)/100 io_wtime_read_sec
		from	o$i_system_event
		where   event in ( select a.event
                                   from   o$event_type a
                                   where  a.type in ('ior')
                                 )
/
create or replace view o$i_io_wtime_events_sec as
		select	event,
			time_waited/100 io_wtime_events_sec,
			total_waits io_wtime_events_count
		from	o$i_system_event
		where   event in ( select a.event
                                   from   o$event_type a
                                   where  a.type in ('ior','iow')
                                 )
/
create or replace view o$i_rt_idle_time_sec as
        select  sum(time_waited/100) idle_time_sec
        from    o$i_system_event
	where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('idle')
                         )
/
create or replace view o$i_other_wtime_sec as
        select  nvl(sum(time_waited)/100,0) other_wtime_sec
        from    o$i_system_event
	where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('other')
                         )
/
create or replace view o$i_other_wtime_events_sec as
        select  event,
                nvl(time_waited/100,0) other_wtime_events_sec,
		total_waits other_wtime_events_count
        from    o$i_system_event
	where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('other')
                         )
/
col val1 new_val real_elapsed_time_sec
select 	a.cpu_count*(sysdate-b.startup_time)*24*60*60 val1
from	( select value cpu_count
	  from   o$parameter
	  where  name = 'cpu_count'
	) a,
	( select startup_time
	  from   o$instance
	) b
/
create or replace view o$i_rt_sum as
     SELECT  
        a.idle_time_sec+(b.cpu_time_tot_sec+c.io_wtime_tot_sec+d.other_wtime_sec) elapsed_time_sec,
        b.cpu_time_tot_sec+c.io_wtime_tot_sec+d.other_wtime_sec response_time_sec,
        b.cpu_time_tot_sec,
        c.io_wtime_tot_sec+d.other_wtime_sec wait_time_tot,
        c.io_wtime_tot_sec,
        d.other_wtime_sec,
        a.idle_time_sec,
        100*b.cpu_time_tot_sec/(b.cpu_time_tot_sec+c.io_wtime_tot_sec+d.other_wtime_sec+0.000001) cpu_pct_resp,
        100*(c.io_wtime_tot_sec+d.other_wtime_sec)/(b.cpu_time_tot_sec+c.io_wtime_tot_sec+d.other_wtime_sec+0.000001) wait_pct_resp,
	e.cpu_capacity_sec cpu_capacity_sec,
	100*(b.cpu_time_tot_sec/e.cpu_capacity_sec) cpu_pct_used,
        e.wall_time_sec
     FROM       o$i_rt_idle_time_sec a,
                o$i_cpu_time_tot_sec b,
                o$i_io_wtime_tot_sec c,
                o$i_other_wtime_sec  d,
		(
		 select	y.cpu_count*(sysdate-z.startup_time)*24*60*60 
			  cpu_capacity_sec,
		        (sysdate-z.startup_time)*24*60*60 wall_time_sec
		 from	( select value cpu_count
	  		  from   o$parameter
	  		  where  name = 'cpu_count'
			) y,
			( select startup_time
	  		  from   o$instance
			) z
		) e
/