-- ********************************************************************
-- * Copyright Notice   : (c)2001,2002,2004,2007,2008,2009 OraPub, Inc.
-- * Filename           : rtsysx.sql
-- * Author             : Craig A. Shallahamer
-- * Original           : 07-oct-01
-- * Last Update        : 14-May-2009
-- * Description        : rtsysx.sql - Response Time Summary for Oracle 10,11,+
-- *                                  (delta - interactive - system level)
-- * 			  Must be at least Ora 10 or above becuase use of v$sys_time_model
-- * Usage              : start rtsysx.sql ‹interval (sec)›  ‹# similar sql chars›
-- ********************************************************************

start osmclear

def interval=&&1
def dur_s=&&1 -- used by oscpux2.sql
def simsql=&&2

prompt
prompt OraPub's Response Time Analysis (RTA) interactive system level delta report
prompt
prompt Initializing response time delta objects...

--set feedback off
--set verify off

set termout off

-- Create time T0 tables

truncate table o$system_event_snap;
insert into o$system_event_snap nologging select * from v$system_event;

truncate table o$rtsysx_sql;

col val1 new_val db_block_changes_t0 
select value val1
from   v$sysstat
where  name = 'db block changes'
/
col val1 new_val user_calls_t0 
select value val1
from   v$sysstat
where  name = 'user calls'
/
col val1 new_val ora_trx_t0 
select sum(value) val1
from   v$sysstat
where  name in ('user rollbacks','user commits')
/
col val1 new_val exec_count_t0 
select value val1
from   v$sysstat
where  name = 'execute count'
/

col val1 new_val cpu_parse_sec_t0 
select value/100 val1
from   v$sysstat
where  name = 'parse time cpu'
/

col val1 new_val cpu_recur_sec_t0 
select value/100 val1
from   v$sysstat
where  name = 'recursive cpu usage'
/

-- db cpu is non background cpu time
col val1 new_val cpu_db_sec_t0 
select value/1000000 val1
from   v$sys_time_model
where  stat_name = 'DB CPU'
/
col val1 new_val cpu_bg_sec_t0 
select value/1000000 val1
from   v$sys_time_model
where  stat_name = 'background cpu time'
/

start oscpux1.sql  -- v$osstat

set termout on
prompt Sleeping and probing active SQL for next &interval seconds...
set termout off

declare
  
  stop_time number ;

begin

  select dbms_utility.get_time + (&interval*100)
  into   stop_time
  from   dual;

  while ( dbms_utility.get_time ‹ stop_time )
  loop

    insert into o$rtsysx_sql nologging
      select distinct b.sql_id,b.address,b.cpu_time, b.elapsed_time, b.sorts, b.executions, 
             b.parse_calls, b.disk_reads, b.buffer_gets, b.rows_processed, b.sql_text 
      from   v$session a,
	         v$sqlarea b 
      where  a.sql_id = b.sql_id
	    and  a.sql_id not in (select c.sql_id from o$rtsysx_sql c) ;	    
	    
    sys.dbms_lock.sleep(5); -- This sleep will cause some timing error
			    -- calcs are based upon the interval/sleep time
  end loop;
  commit;
  
end;
/

--set echo off feedback off
set termout on

prompt Done sleeping...gathering and storing current values...

set echo off feedback off
set termout off


-- Immediately gather CPU time values
--
col val1 new_val cpu_db_sec 
select 	(sum(t1.value)/1000000)-&cpu_db_sec_t0 val1
from	v$sys_time_model t1
where   t1.stat_name = 'DB CPU'
/
col val1 new_val cpu_bg_sec 
select 	(sum(t1.value)/1000000)-&cpu_bg_sec_t0+0.0000010 val1
from	v$sys_time_model t1
where   t1.stat_name = 'background cpu time'
/
col val1 new_val cpu_parse_sec 
select 	(t1.value/100)-&cpu_parse_sec_t0 val1
from	v$sysstat t1
where  name = 'parse time cpu'
/
col val1 new_val cpu_recur_sec 
select 	(t1.value/100)-&cpu_recur_sec_t0 val1
from	v$sysstat t1
where  name = 'recursive cpu usage'
/
col val1 new_val cpu_st_sec 
select 	&cpu_db_sec+&cpu_bg_sec val1
from	dual
/

--select &cpu_st_sec , &cpu_recur_sec , &cpu_parse_sec , &cpu_other_sec , &cpu_bg_sec , &cpu_db_sec
--from dual
--/

col val1 new_val db_block_changes 
select 	value-&db_block_changes_t0 val1
from	v$sysstat
where  name = 'db block changes'
/
col val1 new_val user_calls 
select 	value-&user_calls_t0 val1
from	v$sysstat
where  name = 'user calls'
/
col val1 new_val exec_count 
select 	value-&exec_count_t0 val1
from	v$sysstat
where  name = 'execute count'
/
col val1 new_val ora_trx 
select sum(value)-&ora_trx_t0 val1
from   v$sysstat
where  name in ('user rollbacks','user commits')
/

col val1 new_val wt_sec  
select 	(sum(t1.time_waited-t0.time_waited))/100 val1
from	v$system_event t1,
		o$system_event_snap t0
where	t1.event = t0.event
  and	t1.event not in
  			(
  				select x.event
	        	from   o$event_type x
	        	where  x.type in ('bogus','idle')
	      	)
/	
col val1 new_val wt_io_sec 
select 	(sum(t1.time_waited-t0.time_waited))/100 val1
from	v$system_event t1,
		o$system_event_snap t0,
		o$event_type et
where	t1.event = t0.event
  and	t1.event = et.event
  and	et.type in ('iow','ior')
/
col val1 new_val wt_ior_sec 
select 	(sum(t1.time_waited-t0.time_waited))/100 val1
from	v$system_event t1,
		o$system_event_snap t0,
		o$event_type et
where	t1.event = t0.event
  and	t1.event = et.event
  and	et.type = 'ior'
/
col val1 new_val wt_iow_sec 
select 	(sum(t1.time_waited-t0.time_waited))/100 val1
from	v$system_event t1,
		o$system_event_snap t0,
		o$event_type et
where	t1.event = t0.event
  and	t1.event = et.event
  and	et.type = 'iow'
/
col val1 new_val cpu_capac  
col val2 new_val cpu_count 
select	&interval*value val1,
		value val2
from   	v$parameter
where  	name = 'cpu_count'
/

--set echo off feedback off
set termout on

prompt
prompt *** Response Time Ratio and Workload Metrics

set echo off feedback off verify off
set heading on

col rtr   		format     990.000 heading "RT Ratio"
col block_chg_s format   999990.00 heading "Block Changes/s"
col ora_trx_s 	format   999990.00 heading "Ora Trx/s"
col uc_s		format	 999990.00 heading "User Calls/s"
col exec_s		format   99999990.00 heading "Execs/s"

select	greatest(
		&cpu_st_sec/&cpu_capac,
		&wt_sec/(&cpu_st_sec+&wt_sec)
		) rtr,
	&ora_trx/&interval ora_trx_s,
	&db_block_changes/&interval block_chg_s,
	&user_calls/&interval uc_s,
	&exec_count/&interval exec_s
from	dual
/

prompt
prompt *** Response Time System Summary (delta - interactive - system level)

col rt    format   9,999,990 heading "Response|Time|(sec)"
col ctt   format   9,999,990 heading "Tot CPU|Time|(sec)"
col capac format   9,999,990 heading "CPU|Capac|(sec)"
col util  format       990.0 heading "Ora CPU|Util %"
col wtt   format     999,990 heading "Tot Wait|Time|(sec)"
col iwt   format     999,990 heading "IO Wait|Time|(sec)"
col owt   format     999,990 heading "Other Wait|Time|(sec)"
col pctio format      990 heading "%|IO Wait"
col pctnio format      990 heading "%|Other Wait"
col pt		format	999,990 heading "CPU Parse|Time|(sec)"
col ot		format	9,999,990 heading "CPU Other|Time|(sec)"
col rc		format	999,990 heading "CPU Recur|Time|(sec)"
col sp		format	999,990 heading "CPU SP|Time|(sec)"
col bg		format	999,990 heading "CPU BG|Time|(sec)"


set echo off verify off
set linesize 150
--	&cpu_st_sec+&wt_sec rt,
SELECT	
	&cpu_st_sec ctt,
	&cpu_db_sec sp,
	&cpu_bg_sec bg,
	&cpu_parse_sec pt,
	&cpu_recur_sec rc,
	100*(&cpu_st_sec/&cpu_capac) util,
	&wt_sec wtt,
	&wt_io_sec iwt,
	&wt_sec-&wt_io_sec owt,
	100*(&wt_io_sec/&wt_sec) pctio,
	100-(100*(&wt_io_sec/&wt_sec)) pctnio
FROM	
	dual
/

prompt
prompt *** I/O Wait Time Summary w/Event Details (delta - interactive - system level)

set linesize 150

--set feedback off

col wtt   format     999,999 heading "Tot Wait|Time|(sec)"
col iwt   format     999,999 heading "IO Wait|Time|(sec)"
col pctio format      990.00 heading "% IO|Wait Time|"
col iow   format     999,999 heading "IO WRITE|Wait Time|(sec)"
col ior   format     999,999 heading "IO READ| Wait Time|(sec)"
col iow_pct format      990 heading "% IO|Write"
col ior_pct format      990 heading "% IO|Read"

select
        &wt_io_sec iwt,
        &wt_iow_sec iow,
        &wt_ior_sec ior,
        100*&wt_iow_sec/&wt_io_sec iow_pct,
        100*&wt_ior_sec/&wt_io_sec ior_pct
from	dual
/

col eve   format     a60     heading "IO Wait Event"
col wtt   format     999,990.00 heading "Tot Call|Wait Time|(sec)"
col twt   format     999,990 heading "Tot Waits"
col wta   format     99,9990.00  heading "Avg Call|Wait Time|(ms)"
col wr    format     a3      heading "R,W"
col io_pct	format	9990 heading "%"

--        t1.event||' ('||en.wait_class||')' eve,
--      v$event_name en
-- and en.name = t1.event

select
        t1.event eve,
                decode(et.type,'ior','R','iow','W','OT') wr,
        100*((t1.time_waited-t0.time_waited)/(0.000000000010+100*&wt_io_sec)) io_pct,
        (t1.time_waited-t0.time_waited)/100 wtt,
        10*(t1.time_waited-t0.time_waited)/(0.000000000010+t1.total_waits-t0.total_waits) wta,
        t1.total_waits-t0.total_waits+0.00000010 twt
from    v$system_event t1,
                o$system_event_snap t0,
                o$event_type et
where   t1.event = t0.event
  and   t1.event = et.event
  and   et.type in ('ior','iow')
  and   (t1.total_waits-t0.total_waits) › 1
  and   100*((t1.time_waited-t0.time_waited)/(100*&wt_io_sec)) › 3
order by 3 desc
/

prompt
prompt *** Other Wait Time (non-I/O) Event Detail (delta - interactive - system level)

col eve         format     a60     heading "Non IO (other) Wait Event"
col nio_pct     format    9990     heading "%"

-- t5.event||' ('||en.wait_class||')' eve,
--              v$event_name en
--  and t5.event = en.name 
select
        t5.event eve,
        100*((t5.time_waited-t0.time_waited)/(0.000000000010+100*(&wt_sec-&wt_io_sec))) nio_pct,
        (t5.time_waited-t0.time_waited)/100 wtt,
        10*(t5.time_waited-t0.time_waited)/(0.000000000010+t5.total_waits-t0.total_waits) wta,
        t5.total_waits-t0.total_waits+0.000000000010 twt
from    v$system_event t5,
                o$system_event_snap t0,
                o$event_type et
where   t5.event = t0.event
  and   t5.event = et.event
  and   et.type = 'other'
  and   (t5.total_waits-t0.total_waits) › 1
  and   100*((t5.time_waited-t0.time_waited)/(100*(&wt_sec-&wt_io_sec))) › 3
order by 2 desc
/

prompt
prompt
prompt *** SQL Activity Details During Probe

col mod         heading 'SQL ID'             format          a16
col dr          heading 'Phys Rds|(k)'              format  999,999
col bg          heading 'Log Rds|(k)'               format  999,999
col cpu         heading 'CPU Time|(sec)'        format      999,990.0
col sr          heading 'Sorts'                 format        9,999
col exe         heading 'Runs'                  format      999,999
col rows_p      heading 'Rows|(k)'              format       99,990
col load        heading 'Load Factor'           format      999,999 noprint
col pio_time    heading 'Sec/PIO'               format           90.000
col lio_time    heading 'Sec/LIO'               format           90.000
col exe_time    heading 'Sec/EXE'               format        9,990.00
col etime       heading 'Tot Time|(sec)'        format       99,990.0
col stype       heading 'Stmt|Type'             format           a5

select
  a.sql_id                       mod,
  ((b.elapsed_time-a.elapsed_time)/1000000)/((b.executions-a.executions)+0.0000001)       exe_time,
  (b.disk_reads-a.disk_reads)/1000                    dr,
  (b.buffer_gets-a.buffer_gets)/1000                   bg,
  (b.elapsed_time-a.elapsed_time)/1000000       etime,
  (b.cpu_time-a.cpu_time)/1000000           cpu,
  ((b.elapsed_time-a.elapsed_time)/1000000)/((b.disk_reads-a.disk_reads)+0.00000001)      pio_time,
  ((b.elapsed_time-a.elapsed_time)/1000000)/((b.buffer_gets-a.buffer_gets)+0.00000001)     lio_time,
  (b.executions-a.executions)                    exe,
  (b.rows_processed-a.rows_processed)/1000           ROWS_p,
  (b.sorts-a.sorts)                 sr,
  substr(a.sql_text,1,5)                  stype
from
  o$rtsysx_sql a,
  v$sql b
where
  a.sql_id = b.sql_id
  and b.executions › a.executions
order by
  etime desc
/

prompt
prompt
prompt *** SQL Similar Statements During Delta

col x noprint

col stmt        heading 'SQL Statement (shown if first &simsql chars)'   format      a70 trunc
col the_count   heading 'Count'                                          format  999,990

select
  substr(sql_text,1,&simsql)       stmt,
  count(*)                    the_count
from
  o$rtsysx_sql 
group by substr(sql_text,1,&simsql)
having count(*) › 1
order by 2 desc
/

prompt
prompt *** OS CPU Breakdown During Delta
start oscpux2.sql
set heading off
set feedback off
--select 'Delta is '||&interval||' seconds' from dual;
--select 'Number of CPU cores is '||&cpu_count from dual;

truncate table o$rtsysx_sql; -- will speed up next run

start osmclear