define prefix='v'
set document off

doc

$Source: C:\SRC\ORATOOLS\TOOLDISK\SQL\RCS\WAIT_ST.SQL $
$Revision: 1.5 $
$Author: guy $
$Date: 1996/03/25 11:47:27 $

This SQL*PLUS script reports on the contents of the 
V$SYSTEM_EVENT view.

V$SYSTEM_EVENT records the number of waits, and time waited
for various services.   Waits for db file IO and log file IO
are normal (although the time waited may be excessive).   Other
waits (such as "Client message") are "idle" waits and are
uninteresting.   Excessive waits for other resources may indicate
problems.

Guy Harrison
gharriso@werple.mira.net.au

#



column event format a28 heading "Event name"
column pct_total_waits format 99.99 heading "Pct of|Waits"
column pct_time_waited format 99.99 heading "Pct of|Time"
column average_wait format 99.99 heading "Avg|Wait|s*100"
column total_waits heading "No of|waits" format 9,999,999,999
column time_waited heading "Time|Waited|(secs)" format 99,999,999
column comments heading "" format a1 print


@stat_ctl

set feedback off
set termout off


column instname new_value instance_name noprint
column repdate new_value print_date noprint

select substr(name,1,20) instname,to_char(sysdate,'DD/MM/YY:HH24:MI') repdate from sys.v_$database;

drop table gh$events;

ttitle center 'Top ORACLE Wait events: ' instance_name right print_date skip center period_comment_  skip 2

spool off
spool &spool_dir.&instance_name..st2
set termout on 

create table gh$events as
       select event,total_waits total_waits,
              time_waited,
              average_wait,
              0 pct_time_waited,
              0 pct_total_waits,
	      0 sum_waits,
	      0 sum_times,
              rpad(' ',1) comments
  from &prefix.$system_event
 where event not in ('Null event','client message','smon timer',
	             'rdbms ipc message','pmon timer','WMON goes to sleep',
                     'virtual circuit status','dispatcher timer', 
                     'SQL*Net message from client','parallel query dequeue wait');

update gh$events
   set (sum_waits,sum_times)=
       (select sum(total_waits),sum(time_waited)
          from gh$events)
/
update gh$events
   set pct_total_waits=(total_waits/decode(sum_waits,0,1,sum_waits))*100,
       pct_time_waited=(time_waited/decode(sum_times,0,1,sum_times))*100
/
update gh$events
   set comments='*'
 where (pct_total_waits>1 or pct_time_waited > 1)
   and event not like 'db file%'
   and event not like 'log file%'
/
update gh$events
   set comments='*'
 where (pct_time_waited > 20)
   and event like 'log file%'
/
       
select event,
       total_waits,
       pct_total_waits ,
       time_waited/100 time_waited,
       pct_time_waited,
       average_wait,
       comments
  from gh$events
 where pct_total_waits > .1 or pct_time_waited > .1
 order by time_waited desc, total_waits desc;

prompt
prompt 
prompt Notes:
prompt -------------------------------------------------------------------------;
prompt 1.    Not all waits are shown.
prompt 
prompt 2.    Buffer busy waits may be caused by free list contention
prompt .     for specific tables (create more free lists if neccesary)
prompt .     or by insufficient rollback segments or extents
prompt 
prompt 3.    Free buffer waits may be caused by excessive disk sorts
prompt 
prompt 4.    Enqueue waits may indicate contention for table or row 
prompt .     locks (do you have foreign keys defined without indexes?)
prompt 
prompt 5.    Examine v$latch (or run latch_sta.sql) if latch waits are significant
prompt 
prompt 6.    Excessive log file write or sync waits may be caused by a 
prompt .     high value for LOG_BUFFER
prompt 
 
drop table gh$events;

DOC

Now calculate time spent by processes in elapsed, cpu, parse and other
categories
#

create table gh$events
 (name        varchar2(30),
  value       number,
  pct_total   number)
/

insert into gh$events(name,value)
 select 'CPU (recursive)',value/100 from &prefix.$sysstat
  where name = 'recursive cpu usage'
/
insert into gh$events(name,value)
 select 'CPU (parse)',value/100 from &prefix.$sysstat
  where name = 'parse time cpu'
/
insert into gh$events(name,value)
 select 'CPU (other)',(c.value-r.value-p.value)/100
   from &prefix.$sysstat p,
        &prefix.$sysstat c,
        &prefix.$sysstat r
  where p.name = 'parse time cpu'
    and r.name = 'parse time cpu'
    and c.name = 'CPU used by this session'
/
insert into gh$events(name,value)
 select 'DB file read waits',sum(time_waited)/100
   from &prefix.$system_event
  where event like 'db file % read'
/
insert into gh$events(name,value)
 select 'DB file write waits',sum(time_waited)/100
   from &prefix.$system_event
  where event like 'db file % write'
/
insert into gh$events(name,value)
 select 'Log file writes',sum(time_waited)/100
   from &prefix.$system_event
  where event like 'log file % write' 
     or event = 'log file sync'
/
insert into gh$events(name,value)
 select 'log file space/switch',sum(time_waited)/100
   from &prefix.$system_event
  where event like 'log file space/switch'
/
insert into gh$events(name,value)
 select 'latch waits',sum(time_waited)/100
   from &prefix.$system_event
  where event like 'latch free'
/
insert into gh$events(name,value)
 select 'Buffer waits',sum(time_waited)/100
   from &prefix.$system_event
  where event in ('write complete waits',
		  'free buffer waits',
		  'buffer busy waits')
/
insert into gh$events(name,value)
 select 'SQL*Net waits (inc remote SQL)',sum(time_waited)/100
   from &prefix.$system_event
  where event like 'SQL*Net%'
    and event !='SQL*Net message from client'
/
insert into gh$events(name,value)
 select 'lock waits',sum(time_waited)/100
   from &prefix.$system_event
  where event = 'enqueue'
/
insert into gh$events(name,value)
 select 'Other waits (non-idle)',sum(time_waited)/100
   from &prefix.$system_event
  where event not in ('Null event','client message','smon timer',
	             'rdbms ipc message','pmon timer','WMON goes to sleep',
                     'virtual circuit status','dispatcher timer', 
                     'SQL*Net message from client',
			'parallel query dequeue wait','latch free',
			'enqueue','write complete waits',
		  	'free buffer waits',
		  	'buffer busy waits')
    and event not like 'db file%'
    and event not like 'log file%'
    and event not like 'SQL*Net%'
/
update gh$events e
   set pct_total=(select (e.value/sum(value))*100
                    from gh$events)
/

column name heading Activity format a40
column Value heading "Time Waited|(seconds)" format 999,999,999,999.99
column pct_total heading "Pct of|Total" format 999.99

ttitle center 'Summary of session waits and CPU util.: ' instance_name right print_date skip center period_comment_  skip 2

select * from gh$events order by value desc;

drop table gh$events  ;      

set feedback on



spool off
by value desc;

drop table gh$events  ;      

set feedback on



spool off