-- ********************************************************************
-- * Copyright Notice   : (c)2001,2002,2003 OraPub, Inc.
-- * Filename           : rtsess.sql
-- * Author             : Craig A. Shallahamer
-- * Original           : 10-may-01
-- * Last Update        : 12-jul-03
-- * Description        : rtsess.sql - Response time profile -
-- *                                   Interactive, session level
-- * Usage              : start rtsess.sql ‹SID›
-- ********************************************************************

-- rt  = st + qt
-- qt  = qiot + qit + qot
-- rt  = st + qiot + qit + qot
--
-- where:	rt  = response time
--		qt  = queue time (wait event time + cpu system time)
--		st  = service time (cpu user time)
--		qiot= queue io time
--		qit = queue idle time (timing error, think time, network delay, client prog)
--		qot = queue other time (various wait events)

--  A large or strange IDLE time could be the result of network delays,
--  client/server communication issues, client processing time,
--  O/S timing error or, user/dba start/stop timing error.

def sid=&1

alter session set timed_os_statistics=1 ;

prompt
prompt If the numbers looks strange, you can:
prompt 1. Examine the trace file or
prompt 2. Examine the raw data collection tables (o$rtsess_ev, o$rtsess_time)
prompt

set termout off
col val1 new_val serial noprint 
select	serial# val1
from	v$session
where	sid = '&sid';

col val1 new_val user_dump_dest noprint
select	value val1
from	v$parameter
where	name like 'user_dump_dest';

col val1 new_val server_pid noprint
select	spid val1
from	v$process p,
	v$session s
where	s.sid  = '&sid'
  and	p.addr = s.paddr;

col val1 new_val cpu_count noprint
select  value val1
        from   v$parameter
        where  name = 'cpu_count';


set termout on

prompt About to begin response time statistics collection for SID &sid 
prompt A level 12 trace will be created and is named 
prompt &user_dump_dest/ora_&server_pid..trc
prompt
prompt After you press ENTER, initial session data will be loaded and
prompt
prompt THEN you will be prompted to tell the USER to start their trx.
prompt
prompt Get the user ready to begin their transaction.
prompt
prompt Press ENTER to begin to load initial session data.
accept x

set termout off

-- CREATE EMPTY TABLES

drop table o$rtsess_ev_&sid
/
set termout on
prompt ...1/6
set termout off

create table o$rtsess_ev_&sid as
	select	name event, to_number(0) tw_t0, to_number(0) tw_t1
	from	v$event_name
/
set termout on
prompt ...2/6  READY
set termout off

drop table o$rtsess_time_&sid
/
set termout on
prompt ...3/6 
set termout off

create table o$rtsess_time_&sid as
	select	to_number(0) cpu_begin,
		to_number(0) cpu_end,
		to_number(0) clock_begin,
		to_number(0) clock_end
	from	dual
/
set termout on
prompt ...5/6
set termout off

update	o$rtsess_time_&sid
set	cpu_begin = (	select value
                  	from   v$sesstat
                  	where  statistic# = 248 -- OS User time used
                    	and  sid = '&sid'
                    ),
	clock_begin = ( select dbms_utility.get_time 
                  	from   dual
                      )
/

set termout on
prompt ...4/6  SET
set termout off

col x new_val bg_procs
select count(*) x
from   v$session 
where  username is null;

-- LOAD (i.e., update at time T0) TABLES

declare
	cursor sw is
                select  event, sum(time_waited) time_waited
                from    v$session_event
                where   sid = '&sid'
                   or   sid ‹= &bg_procs
		group by event;
begin
	for sw_rec in sw loop
	  update  o$rtsess_ev_&sid
	  set     tw_t0 = sw_rec.time_waited
	  where   o$rtsess_ev_&sid..event = sw_rec.event;
	end loop;
	commit;
end;
/

set termout on
select * from o$rtsess_ev_&sid where event like 'latch%';

set termout on
prompt ...6/6

exec sys.dbms_system.set_ev(&sid,&serial,10046,12,'');

prompt
prompt USER GO, that is, start what is painfully slow now!
prompt
prompt DBA, to end statistics collection press ENTER.

set termout off

accept x

-- LOAD (i.e., update at time T1) TABLES

-- I did the timing setting FIRST to keep as close as
-- possible to the real time.
--
update  o$rtsess_time_&sid
set     cpu_end   = (   select value
                        from   v$sesstat
                        where  statistic# = 248 -- OS User time used
                        and  sid = '&sid'
                    ),
        clock_end = ( select dbms_utility.get_time
                      from   dual
                    )
/
-- Now update the wait event numbers.
declare
        cursor sw is
                select  event, sum(time_waited) time_waited
                from    v$session_event
                where   sid = '&sid'
                   or   sid ‹= &bg_procs
		group by event;
begin
        for sw_rec in sw loop
          update  o$rtsess_ev_&sid
          set     tw_t1 = sw_rec.time_waited
          where   o$rtsess_ev_&sid..event = sw_rec.event;
        end loop;
        commit;
end;
/
set termout on
select * from o$rtsess_ev_&sid where event like 'latch%';

exec sys.dbms_system.set_ev(&sid,&serial,10046,0,'');

-- I had to create a table instead of using a view because of some Oracle
-- bug.  The view would create but a variety of bogus output resulted.
-- Don't believe me?...Just change the table stuff to a view stuff.

drop table oo$i_rt_sum_&sid
/
create table oo$i_rt_sum_&sid as
SELECT  
	(g.clock_end-g.clock_begin)/100 resp_time_tot,
	(g.clock_end-g.clock_begin)/100 wall_time_sec,
	(g.cpu_end-g.cpu_begin)/100 service_time_tot, 
        ((g.clock_end-g.clock_begin)/100)-((g.cpu_end-g.cpu_begin)/100) queue_time_tot,
	c.io_wtime_tot queue_io_tot,
	d.other_wtime_sec queue_other_tot,
        9999 queue_idle_tot,
	e.io_wtime_write_sec queue_io_write_sec,
	f.io_wtime_read_sec queue_io_read_sec,
        (g.clock_end-g.clock_begin)-((g.cpu_end-g.cpu_begin)/100)-(c.io_wtime_tot-d.other_wtime_sec) idle_sec
FROM    (
        select  sum(tw_t1-tw_t0)/100 io_wtime_tot
        from    o$rtsess_ev_&sid
        where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('iow','ior')
                         )
	) c,
	(
        select  sum(tw_t1-tw_t0)/100 other_wtime_sec
        from    o$rtsess_ev_&sid
        where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('other')
                         )
	) d,
	(
        select  sum(tw_t1-tw_t0)/100 io_wtime_write_sec
        from    o$rtsess_ev_&sid
        where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('iow')
                         )
	) e,
	(
        select  sum(tw_t1-tw_t0)/100 io_wtime_read_sec
        from    o$rtsess_ev_&sid
        where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('ior')
                         )
	) f,
	o$rtsess_time_&sid g,
	(
        select  sum(tw_t1-tw_t0)/100 idle_sec
        from    o$rtsess_ev_&sid
        where   event in ( select a.event
                           from   o$event_type a
                           where  a.type in ('idle')
                         )
        ) h
/
commit;

set termout on
set feedback off
set heading on

---------------------------------------------------------------------------
-- REPORTS : Interactive Session Level Response Time 
---------------------------------------------------------------------------

set linesize 150
set feedback off verify off

prompt Session level response time details for SID &sid


col cpr   format       90.00 heading "%|CPU|RT|[st/rt]"
col qpr   format       90.00 heading "%|Queue|RT|[qt/rt]"
col rt    forma     9,990.00 heading "Response|Time(sec)|[rt=st+qt]"
col qt    forma     9,990.00 heading "Queue|Time(sec)|[qt]"
col st    forma     9,990.00 heading "Service|Time(sec)|[st]"

prompt
prompt *** Response Time Summary

select	resp_time_tot rt,
	service_time_tot st,
	queue_time_tot qt,
	100*service_time_tot/(resp_time_tot+0.0000001) cpr,
	100*queue_time_tot/(resp_time_tot+0.0000001) qpr
from	oo$i_rt_sum_&sid
/

prompt
prompt *** Queue Time Summary

col qt    forma       990.00 heading "Queue Time(sec)|[qio+qidle+qot+dno]"
col qio   format      990.00 heading "QT|I/O(sec)|[qio]"
col qidle format      990.00 heading "QT|Idle(sec)|[qidle]"
col qot   format      990.00 heading "QT|Other(sec)|[qot]"

select	queue_time_tot qt,
	queue_io_tot qio,
        QUEUE_TIME_TOT-QUEUE_IO_TOT-QUEUE_OTHER_TOT qidle,
	queue_other_tot qot
from	oo$i_rt_sum_&sid
/

--	queue_idle_tot qidle,
prompt
prompt *** Queue Time IO Timing Detail

col qio   format      990.00 heading "QT|I/O(sec)|[tio=wio+rio]"
col wio	  format      990.00 heading "QT|Write I/O(sec)|[wio]"
col rio	  format      990.00 heading "QT|Read I/O(sec)|[wio]"
col wpct  format      990.00 heading "% Writes Time|[wio/tio]"
col rpct  format      990.00 heading "% Read Time|[rio/tio]"

select	queue_io_tot qio,
	queue_io_write_sec wio,
	queue_io_read_sec  rio,
	100*queue_io_write_sec/(queue_io_tot+0.00001) wpct,
	100*queue_io_read_sec/(queue_io_tot+0.00001) rpct
from	oo$i_rt_sum_&sid
/

prompt
prompt *** Queue Time IO Event Timing Detail

col event  format    a40 heading "Wait Event Name"
col et     format 990.00 heading "Wait Time|(sec)"

select  event,
        (tw_t1-tw_t0)/100 et -- io_wtime_events_sec
from    o$rtsess_ev_&sid
where
        (tw_t1-tw_t0) › 0
  and   event in ( select a.event
                   from   o$event_type a
                   where  a.type in ('iow','ior')
                 )
order by 2 desc
/

prompt
prompt *** Queue Time Other Event Timing Detail

col event  format    a40 heading "Wait Event Name"
col et     format 990.00 heading "Wait Time|(sec)"

select  event,
        sum(tw_t1-tw_t0)/100 et -- other_wtime_events_sec
from    o$rtsess_ev_&sid
where
        (tw_t1-tw_t0) › 0
  and   event in ( select a.event
                   from   o$event_type a
                   where  a.type in ('other')
                 )
group by event
order by 2 desc
/

prompt
prompt *** Time Not Accounted For Details (for QA)

col event  format    a40 heading "Wait Event Name"
col et     format 990.00 heading "Wait Time|(sec)"

select  event,
        sum(tw_t1-tw_t0)/100 et -- other_wtime_events_sec
from    o$rtsess_ev_&sid
where
	(tw_t1-tw_t0) › 0
  and   event in ( select a.event
                   from   o$event_type a
                   where  a.type not in ('other','ior','iow','idle')
                 )
group by event
order by 2 desc
/


prompt
prompt
prompt A large or strange IDLE time could be the result of network delays,
prompt client/server communication issues, client processing time,
prompt O/S timing error or, user/dba start/stop timing error.
prompt
prompt You may want to review and/or delete the working tables:
prompt drop table o$rtsess_ev_&sid ;
prompt drop table o$rtsess_time_&sid ;
prompt drop table oo$i_rt_sum_&sid ;
prompt
prompt A level 12 trace was created and is named 
prompt &user_dump_dest/ora_&server_pid..trc
prompt