-- ********************************************************************
-- * Copyright Notice   : (c)2004 OraPub, Inc.
-- * Filename           : cr_rtsp.sql 
-- * Author             : Craig A. Shallahamer
-- * Original           : 11-oct-04
-- * Last Update        : 11-oct-04
-- * Description        : cr_rtsp.sql  - Create RT session profile procedure
-- * Usage              : start cr_rtsp.sql
-- ********************************************************************

-- rt  = st + qt + uat
-- qt  = qiot + qnc + qot
-- rt  = st + qiot + qnc + qot + uat
--
-- where:	rt  = response time
--		qt  = queue time (wait event time + cpu system time)
--		st  = service time (cpu user time)
--		uat = unaccounted for time (tool usage error, uninstrumented time, etc.)
--		qiot= queue io time
--		qnc = queue idle time (network delay, client prog processing)
--		qot = queue other time (various wait events)

connect sys
grant select on v_$mystat to osmmon;
grant select on v_$sesstat to osmmon;
grant select on v_$session_event to osmmon;
grant select on v_$event_name to osmmon;

----------------------------------------
-- Create the supporting objects
----------------------------------------
connect osmmon/osmmon

drop table o$oprtsp_ev;
/
create table o$oprtsp_ev (
  run_name varchar2(2000),
  key varchar2(2000), 
  event varchar(64), 
  t0 number, 
  t1 number
)
/
drop table o$oprtsp_time;
/
create table o$oprtsp_time (
  run_name varchar2(2000),
  key varchar2(2000), 
  start_date date,
  start_time number,
  stop_time  number
)
/

----------------------------------------
-- Create the main procedure
----------------------------------------

create or replace procedure oprtsp 
  (run_name_in in varchar2, situation in number)
   -- name: The name of the run, function, or whatever
   -- situation: 1=on 0=off
is
  u_key  varchar2(2000);
  the_sid    number;

  cursor sid_cur is
	select sid from v$mystat;
  sid_cur_rec sid_cur%rowtype;

  cursor sw_cur is
  	select 	sid,event, time_waited
	from	v$session_event;
  sw_cur_rec sw_cur%rowtype;

begin
  u_key := dbms_session.unique_session_id;

  open sid_cur;
  fetch sid_cur into sid_cur_rec.sid;
  the_sid := sid_cur_rec.sid ;
  close sid_cur;
  --insert into msg values (sysdate,'SID='||sid||' u_key='||u_key);commit;

  if situation = 1
  then
    delete from o$oprtsp_ev  where u_key = u_key and run_name = run_name_in;
    insert into o$oprtsp_ev  select run_name_in,u_key,name,0,0 from v$event_name;
    insert into o$oprtsp_time values (run_name_in,u_key,sysdate,dbms_utility.get_time,dbms_utility.get_time);

    for sw_cur_rec in sw_cur loop
      if sw_cur_rec.sid = the_sid 
      then
        update o$oprtsp_ev 
        set    t0 = sw_cur_rec.time_waited,
               t1 = sw_cur_rec.time_waited
        where  run_name = run_name_in
          and  key      = u_key
          and  event    = sw_cur_rec.event;
      end if;
    end loop ;
    insert into o$oprtsp_ev
      select run_name_in,u_key,'cpu_user',value,value
      from   v$sesstat
      where  statistic# = 316
        and  sid = the_sid;
    insert into o$oprtsp_ev
      select run_name_in,u_key,'cpu_system',value,value
      from   v$sesstat
      where  statistic# = 317
        and  sid = the_sid;

  elsif situation = 0
  then
    for sw_cur_rec in sw_cur loop
      if sw_cur_rec.sid = the_sid
      then
        update o$oprtsp_ev 
        set    t1 = sw_cur_rec.time_waited
        where  key = u_key
          and  run_name = run_name_in
          and  event = sw_cur_rec.event;
      end if;
    end loop ;
    update o$oprtsp_ev
      set  t1 = ( select value from v$sesstat where statistic# = 316 and sid = the_sid)
    where  key = u_key
     and   run_name = run_name_in
     and   event = 'cpu_user';
    update o$oprtsp_ev
      set  t1 = ( select value from v$sesstat where statistic# = 317 and sid = the_sid)
    where  key = u_key
     and   run_name = run_name_in
     and   event = 'cpu_system';
    update o$oprtsp_time
    set    stop_time = dbms_utility.get_time
    where  key = u_key
     and   run_name = run_name_in;
  end if;

  commit;
end;
/

exit;

truncate table o$oprtsp_ev;
truncate table o$oprtsp_time;
exec oprtsp('t0',1);
@mkodo
exec oprtsp('t0',0);
exec oprtsp('t1',1);
@tss
@chr
exec oprtsp('t1',0);
exec oprtsp('t2',1);
select count(*) from odo;
exec oprtsp('t2',0);
exec oprtsp('t3',1);
update odo set c4=0 where c4+0=2;
exec oprtsp('t3',0);

-- This is used to determine the script overhead
exec oprtsp('t4',1);
exec oprtsp('t4',0);
exec oprtsp('t5',1);
exec oprtsp('t5',0);

-- Here is some SQL to get the information

set linesize 130
col key format a20
col run_name a5
col event format a35 trunc
select run_name,key,event,t0,t1,t1-t0 from o$oprtsp_ev where t1›0 order by run_name,key,event;
select run_name,key,to_char(start_date,'DD-Mon-YY HH24:MI:SS'),stop_time-start_time 
from o$oprtsp_time 
order by run_name;