#!/bin/csh
sqlplus user/pass << END

Doc     
      Name:     system_event.csh

      Author:   Mark Gurry

       This script provides a system wide snapshot of all waits that have occurred. 

       Note:  You must have TIMED_STATISTICS=TRUE to obtain the Time_Waited and Average_Wait

      The Waits that are of particular interest from a tuning perspective are:

      "buffer busy waits"     Which indicates segment header, index block or data block contention.
                              It can also indicatye free list or rollback block contention.  

      "enqueue"               Is caused by having too many DDL locks, DML locks or excessive use
                              of sequences. 

      "free buffer waits"     Means that the DBWR is having trouble keeping up. 
                             One way to fix the problem
                              is to have multiple database writers.  You should have more    
                              than one DBWR per disk because multiple DBWRs are often assigned the 
                              task of writing to the same disk. 

     "db file scattered read" SQL that is performing full table scans.

     "db file parallel write"  The buffer cache may not be large enough to be effective

     "latch free"             Can be caused by one of many latches

     "write complete waits"   Often occurs when disk I/O bottlenecks are occurring through                                                                                            excessive disk writes

     "redo log switch......" Indicates the waits that have occurred during a redo log switch  


#





set heading off
set pagesize 999
drop table system_period_waits_start;

create table system_period_waits_start
as select event, total_waits, time_waited, average_wait 
     from v\$system_event
    where event like '%buffer%'
      or  event like '%log%'
      or  event like '%write%'
      or  event like '%read%'
      or  event like '%enqueue%'
      or  event like '%latch%'
/

exit 
END

#    
#       Now Waiting 30 Seconds for Period delay
#   


sleep  30 

echo
echo    30 Seconds Complete *** Continue Processing!
echo 

sqlplus user/pass << END

set heading off
set pagesize 999

drop table system_period_waits_end;    

create table system_period_waits_end   
as select event, total_waits, time_waited, average_wait 
     from v\$system_event
    where event like '%buffer%'
      or  event like '%log%'
      or  event like '%write%'
      or  event like '%read%'
      or  event like '%enqueue%'
      or  event like '%latch%'
/


drop table session_waits;
create table session_waits 
   storage (INITIAL 200k NEXT 200k) 
   as
   select * from  v\$session_wait
    where event not like '%SQL*Net%'
      and event not like '%imer%'
      and event not like '%pipe%'
;



begin

  FOR loop_key IN 1 .. 100
    LOOP
       insert into session_waits  
        select * from v\$session_wait
         where event not like '%SQL*Net%'
           and event not like '%imer%'
           and event not like '%ipc%'
           and event not like '%pipe%';

    END LOOP ;

end;
/

        

Doc       

    Reporting All Database System Waits


#

column "Event" format a30
set pagesize 999
column "Average Wait" format 999.99
column "Total Time Waited" format 9,999.99

ttitle 'System Wide Waits Over n Seconds '
Set heading on

spool system_waits.lis

Doc     
      Name:     system_event.csh

      Author:   Mark Gurry

       This script provides a system wide snapshot of all waits that have occurred. 

       Note:  You must have TIMED_STATISTICS=TRUE to obtain the Time_Waited and Average_Wait

      The Waits that are of particular interest from a tuning perspective are:

      "buffer busy waits"     Which indicates segment header, index block or data block contention.
                              It can also indicatye free list or rollback block contention.  

      "enqueue"               Is caused by having too many DDL locks, DML locks or excessive use
                              of sequences. 

      "free buffer waits"     Means that the DBWR is having trouble keeping up. One way to fix the problem
                              is to have multiple database writers.  You should have more    
                              than one DBWR per disk because multiple DBWRs are often assigned the 
                              task of writing to the same disk. 

     "db file scattered read" SQL that is performing full table scans.

     "db file parallel write"  The buffer cache may not be large enough to be effective

     "latch free"             Can be caused by one of many latches

     "write complete waits"   Often occurs when disk I/O bottlenecks are occurring through excessive 
                              disk writes

     "redo log switch......" Indicates the waits that have occurred during a redo log switch  


#

select a.event "Event" , 
       a.total_waits - b.total_waits "Total Waits" , 
       a.time_waited - b.time_waited "Total Time Waited" , 
       (a.time_waited - b.time_waited) / (a.total_waits - b.total_waits) "Average Wait"
  from system_period_waits_end a, system_period_waits_start b
 where (a.event like '%buffer%'
   or  a.event like '%log%'
   or  a.event like '%write%'
   or  a.event like '%read%'
   or  a.event like '%enqueue%'
   or  a.event like '%latch%')
  and  a.event = b.event
  and  a.total_waits - b.total_waits > 0
order by 'Average_Wait' desc
/

drop table system_period_waits_start;
drop table system_period_waits_end;    
drop table session_waits;
spool off
exit   
END