Rem ###########################################################################################
Rem 
Rem This SQL statement returns an overview of the most important event statistics.
Rem You need to run the "dbmon" script first in order to collect the raw statistic data.
Rem
Rem
Rem Copyright: (c) 2004
Rem
Rem  XXXXXX     XX    XXXXXX             XXXXXX    XXXX     XXXX
Rem XX    X    XXXX    XX  XX            X XX X   XX  XX   XX  XX
Rem XX        XX  XX   XX  XX              XX    XX       XX
Rem  XXXXX    XX  XX   XXXXX               XX    XX       XX
Rem      XX   XXXXXX   XX                  XX    XX    X  XX    X
Rem X    XX   XX  XX   XX                  XX     XX  XX   XX  XX
Rem XXXXXX    XX  XX  XXXX                XXXX     XXXX     XXXX
Rem
Rem           XXXXX                                XXX
Rem          XX   XX                                XX
Rem          XX   XX  XX XXX    XXXXX    XXXXX      XX     XXXXX
Rem          XX   XX   XXX XX       X   XX   XX     XX    XX    X
Rem          XX   XX   XX      XXXXXX   XX          XX    XXXXXXX
Rem          XX   XX   XX      X   XX   XX   XX     XX    XX
Rem           XXXXX   XXXX     XXXXX X   XXXXX     XXXX    XXXXX
Rem
Rem
Rem                     XXXX    XXXXX   XXXXXXX
Rem                    XX  XX  XX   XX   XX   X
Rem                   XX       XX   XX   XX X
Rem                   XX       XX   XX   XXXX
Rem                   XX    X  XX   XX   XX X
Rem                    XX  XX  XX   XX   XX   X
Rem                     XXXX    XXXXX   XXXXXXX
Rem
Rem Author: Stefan Kuhlmann
Rem
Rem Version 0.4
Rem
Rem Version history:
Rem Date          |Text
Rem --------------+--------------------------------------------------------------------------
Rem Jul. 31th 2005|Version 0.1 (Created, modified from event.sql; use event# instead of name)
Rem
Rem
Rem Suggestion for improvements are welcome !
Rem Please sent an email to stefan.kuhlmann@sap.com.
Rem
Rem
Rem DISCLAIMER OF WARRANTIES:
Rem
Rem The following SQL is sample code created by SAP. This SQL is not part of any standard SAP 
Rem product and is provided to you solely for the purpose of assisting you in the development 
Rem of your applications. The code is provided "AS IS", without warranty of any kind. SAP shall 
Rem not be liable for any damages arising out of your use of such sample code, even if you have 
Rem been advised of the possibility of such damage.								
Rem
Rem ###########################################################################################

set trimspool on
set pagesize 5000
set linesize 1500
set heading off
set feedback off
set verify off
set newpage none
clear buffer

variable bbw#   number
variable seq#   number
variable scat#  number
variable enq#   number
variable lf#    number
variable lfs#   number
variable lfpw#  number
variable fbw#   number
variable lfsci# number
variable lfsan# number
variable lcl#   number
variable lcp#   number
variable lbs#   number
variable lfsr#  number
variable snmtc# number

alter session set db_file_multiblock_read_count = 128;

begin
  select sum(decode(name,'buffer busy waits',event#,0)),
         sum(decode(name,'db file sequential read',event#,0)),
         sum(decode(name,'db file scattered read',event#,0)),
         sum(decode(name,'enqueue',event#,0)),
         sum(decode(name,'latch free',event#,0)),
         sum(decode(name,'log file sync',event#,0)),
         sum(decode(name,'log file parallel write',event#,0)),
         sum(decode(name,'free buffer waits',event#,0)),
         sum(decode(name,'log file switch (checkpoint incomplete)',event#,0)),
         sum(decode(name,'log file switch (archiving needed)',event#,0)),
         sum(decode(name,'library cache lock',event#,0)),
         sum(decode(name,'library cache pin',event#,0)),
         sum(decode(name,'log buffer space',event#,0)),
         sum(decode(name,'log file sequential read',event#,0)),
         sum(decode(name,'SQL*Net message to client',event#,0))
    into :bbw#,
         :seq#,
         :scat#,
         :enq#,
         :lf#,
         :lfs#,
         :lfpw#,
         :fbw#,
         :lfsci#,
         :lfsan#,
         :lcl#,
         :lcp#,
         :lbs#,
         :lfsr#,
         :snmtc#
    from sk_event_name;
end;
/

accept sdate prompt 'Please enter the date you want to see the event statistics from (format: YYYY-MM-DD): '

spool event_&&sdate..out

select 'Timestamp'
       ||chr(9)||'buffer busy wait'
       ||chr(9)||'buffer busy wait per sec'
       ||chr(9)||'buffer busy wait avg. wait time in ms'
       ||chr(9)||'db file sequential read'
       ||chr(9)||'db file sequential read per sec'
       ||chr(9)||'db file sequential read avg. wait time in ms'
       ||chr(9)||'db file scattered read'
       ||chr(9)||'db file scattered read per sec'
       ||chr(9)||'db file scattered read avg. wait time in ms'
       ||chr(9)||'enqueue'
       ||chr(9)||'enqueue per sec'
       ||chr(9)||'enqueue avg. wait time in ms'
       ||chr(9)||'latch free'
       ||chr(9)||'latch free per sec'
       ||chr(9)||'latch free avg. wait time in ms'
       ||chr(9)||'log file sync'
       ||chr(9)||'log file sync per sec'
       ||chr(9)||'log file sync avg. wait time in ms'
       ||chr(9)||'log file parallel write'
       ||chr(9)||'log file parallel write per sec'
       ||chr(9)||'log file parallel write avg. wait time in ms'
       ||chr(9)||'free buffer waits'
       ||chr(9)||'free buffer waits per sec'
       ||chr(9)||'free buffer waits avg. wait time in ms'
       ||chr(9)||'log file switch (checkpoint incomplete)'
       ||chr(9)||'log file switch (checkpoint incomplete) per sec'
       ||chr(9)||'log file switch (checkpoint incomplete) avg. wait time in ms'
       ||chr(9)||'log file switch (archiving needed)'
       ||chr(9)||'log file switch (archiving needed) per sec'
       ||chr(9)||'log file switch (archiving needed) avg. wait time in ms'
       ||chr(9)||'library cache lock'
       ||chr(9)||'library cache lock per sec'
       ||chr(9)||'library cache lock avg. wait time in ms'
       ||chr(9)||'library cache pin'
       ||chr(9)||'library cache pin per sec'
       ||chr(9)||'library cache pin avg. wait time in ms'
       ||chr(9)||'log buffer space'
       ||chr(9)||'log buffer space per sec'
       ||chr(9)||'log buffer space avg. wait time in ms'
       ||chr(9)||'log file sequential read'
       ||chr(9)||'log file sequential read per sec'
       ||chr(9)||'log file sequential read avg. wait time in ms'
       ||chr(9)||'Additional ''log file sync'' time because of timeouts'
       ||chr(9)||'SQL*Net message to client'
       ||chr(9)||'SQL*Net message to client per sec'
       ||chr(9)||'SQL*Net message to client avg. wait time in ms'
  from dual
union all
select time||chr(9)||
       bbwwaits||chr(9)||
       to_char(round(bbwwaits/sec,2))||chr(9)||
       to_char(round(decode(bbwwaits,0,0,bbwtimewait/bbwwaits*10),2))||chr(9)||
       seqwaits||chr(9)||
       to_char(round(seqwaits/sec,2))||chr(9)||
       to_char(round(decode(seqwaits,0,0,seqtimewait/seqwaits*10),2))||chr(9)||
       scatwaits||chr(9)||
       to_char(round(scatwaits/sec,2))||chr(9)||
       to_char(round(decode(scatwaits,0,0,scattimewait/scatwaits*10),2))||chr(9)||
       enqwaits||chr(9)||
       to_char(round(enqwaits/sec,2))||chr(9)||
       to_char(round(decode(enqwaits,0,0,enqtimewait/enqwaits*10),2))||chr(9)||
       lfwaits||chr(9)||
       to_char(round(lfwaits/sec,2))||chr(9)||
       to_char(round(decode(lfwaits,0,0,lftimewait/lfwaits*10),2))||chr(9)||
       lfswaits||chr(9)||
       to_char(round(lfswaits/sec,2))||chr(9)||
       to_char(round(decode(lfswaits,0,0,lfstimewait/lfswaits*10),2))||chr(9)||
       lfpwwaits||chr(9)||
       to_char(round(lfpwwaits/sec,2))||chr(9)||
       to_char(round(decode(lfpwwaits,0,0,lfpwtimewait/lfpwwaits*10),2))||chr(9)||
       fbwwaits||chr(9)||
       to_char(round(fbwwaits/sec,2))||chr(9)||
       to_char(round(decode(fbwwaits,0,0,fbwtimewait/fbwwaits*10),2))||chr(9)||
       lfsciwaits||chr(9)||
       to_char(round(lfsciwaits/sec,2))||chr(9)||
       to_char(round(decode(lfsciwaits,0,0,lfscitimewait/lfsciwaits*10),2))||chr(9)||
       lfsanwaits||chr(9)||
       to_char(round(lfsanwaits/sec,2))||chr(9)||
       to_char(round(decode(lfsanwaits,0,0,lfsantimewait/lfsanwaits*10),2))||chr(9)||
       lclwaits||chr(9)||
       to_char(round(lclwaits/sec,2))||chr(9)||
       to_char(round(decode(lclwaits,0,0,lcltimewait/lclwaits*10),2))||chr(9)||
       lcpwaits||chr(9)||
       to_char(round(lcpwaits/sec,2))||chr(9)||
       to_char(round(decode(lcpwaits,0,0,lcptimewait/lcpwaits*10),2))||chr(9)||
       lbswaits||chr(9)||
       to_char(round(lbswaits/sec,2))||chr(9)||
       to_char(round(decode(lbswaits,0,0,lbstimewait/lbswaits*10),2))||chr(9)||
       lfsrwaits||chr(9)||
       to_char(round(lfsrwaits/sec,2))||chr(9)||
       to_char(round(decode(lfsrwaits,0,0,lfsrtimewait/lfsrwaits*10),2))||chr(9)||
       to_char(round(decode((lfswaits-lfstimeout),0,0,lfstimewait/(lfswaits-lfstimeout)*10)-decode(lfswaits,0,0,lfstimewait/lfswaits*10),2))||chr(9)||
       snmtcwaits||chr(9)||
       to_char(round(snmtcwaits/sec,2))||chr(9)||
       to_char(round(decode(snmtcwaits,0,0,snmtctimewait/snmtcwaits*10),2))
  from (select to_char(timestamp,'YYYY-MM-DD-HH24:MI:SS') time,
               (timestamp-prev_timestamp)*86400 sec,
               bbwwaits-prev_bbwwaits bbwwaits,
               bbwtimewait-prev_bbwtimewait bbwtimewait,
               seqwaits-prev_seqwaits seqwaits,
               seqtimewait-prev_seqtimewait seqtimewait,
               scatwaits-prev_scatwaits scatwaits,
               scattimewait-prev_scattimewait scattimewait,
               enqwaits-prev_enqwaits enqwaits,
               enqtimewait-prev_enqtimewait enqtimewait,
               lfwaits-prev_lfwaits lfwaits,
               lftimewait-prev_lftimewait lftimewait,
               lfswaits-prev_lfswaits lfswaits,
               lfstimewait-prev_lfstimewait lfstimewait,
               lfstimeout-prev_lfstimeout lfstimeout,
               lfpwwaits-prev_lfpwwaits lfpwwaits,
               lfpwtimewait-prev_lfpwtimewait lfpwtimewait,
               fbwwaits-prev_fbwwaits fbwwaits,
               fbwtimewait-prev_fbwtimewait fbwtimewait,
               lfsciwaits-prev_lfsciwaits lfsciwaits,
               lfscitimewait-prev_lfscitimewait lfscitimewait,
               lfsanwaits-prev_lfsanwaits lfsanwaits,
               lfsantimewait-prev_lfsantimewait lfsantimewait,
               lclwaits-prev_lclwaits lclwaits,
               lcltimewait-prev_lcltimewait lcltimewait,
               lcpwaits-prev_lcpwaits lcpwaits,
               lcptimewait-prev_lcptimewait lcptimewait,
               lbswaits-prev_lbswaits lbswaits,
               lbstimewait-prev_lbstimewait lbstimewait,
               lfsrwaits-prev_lfsrwaits lfsrwaits,
               lfsrtimewait-prev_lfsrtimewait lfsrtimewait,
               snmtcwaits-prev_snmtcwaits snmtcwaits,
               snmtctimewait-prev_snmtctimewait snmtctimewait
          from (select lfdnr, 
                       timestamp,
                       lag(timestamp) over (order by lfdnr) prev_timestamp,
                       bbwwaits,lag(bbwwaits,1,-1) over (order by lfdnr) prev_bbwwaits,
                       bbwtimewait,lag(bbwtimewait) over (order by lfdnr) prev_bbwtimewait,
                       seqwaits,lag(seqwaits) over (order by lfdnr) prev_seqwaits,
                       seqtimewait,lag(seqtimewait) over (order by lfdnr) prev_seqtimewait,
                       scatwaits,lag(scatwaits) over (order by lfdnr) prev_scatwaits,
                       scattimewait,lag(scattimewait) over (order by lfdnr) prev_scattimewait,
                       enqwaits,lag(enqwaits) over (order by lfdnr) prev_enqwaits,
                       enqtimewait,lag(enqtimewait) over (order by lfdnr) prev_enqtimewait,
                       lfwaits,lag(lfwaits) over (order by lfdnr) prev_lfwaits,
                       lftimewait,lag(lftimewait) over (order by lfdnr) prev_lftimewait,
                       lfswaits,lag(lfswaits) over (order by lfdnr) prev_lfswaits,
                       lfstimewait,lag(lfstimewait) over (order by lfdnr) prev_lfstimewait,
                       lfstimeout,lag(lfstimeout) over (order by lfdnr) prev_lfstimeout,
                       lfpwwaits,lag(lfpwwaits) over (order by lfdnr) prev_lfpwwaits,
                       lfpwtimewait,lag(lfpwtimewait) over (order by lfdnr) prev_lfpwtimewait,
                       fbwwaits,lag(fbwwaits) over (order by lfdnr) prev_fbwwaits,
                       fbwtimewait,lag(fbwtimewait) over (order by lfdnr) prev_fbwtimewait,
                       lfsciwaits,lag(lfsciwaits) over (order by lfdnr) prev_lfsciwaits,
                       lfscitimewait,lag(lfscitimewait) over (order by lfdnr) prev_lfscitimewait,
                       lfsanwaits,lag(lfsanwaits) over (order by lfdnr) prev_lfsanwaits,
                       lfsantimewait,lag(lfsantimewait) over (order by lfdnr) prev_lfsantimewait,
                       lclwaits,lag(lclwaits) over (order by lfdnr) prev_lclwaits,
                       lcltimewait,lag(lcltimewait) over (order by lfdnr) prev_lcltimewait,
                       lcpwaits,lag(lcpwaits) over (order by lfdnr) prev_lcpwaits,
                       lcptimewait,lag(lcptimewait) over (order by lfdnr) prev_lcptimewait,
                       lbswaits,lag(lbswaits) over (order by lfdnr) prev_lbswaits,
                       lbstimewait,lag(lbstimewait) over (order by lfdnr) prev_lbstimewait,
                       lfsrwaits,lag(lfsrwaits) over (order by lfdnr) prev_lfsrwaits,
                       lfsrtimewait,lag(lfsrtimewait) over (order by lfdnr) prev_lfsrtimewait,
                       snmtcwaits,lag(snmtcwaits) over (order by lfdnr) prev_snmtcwaits,
                       snmtctimewait,lag(snmtctimewait) over (order by lfdnr) prev_snmtctimewait
                  from (select e.lfdnr,min(timestamp) timestamp,
                               sum(DECODE(event#,:bbw#,total_waits,0)) bbwwaits,
                               sum(DECODE(event#,:bbw#,time_waited,0)) bbwtimewait,
                               sum(DECODE(event#,:seq#,total_waits,0)) seqwaits,
                               sum(DECODE(event#,:seq#,time_waited,0)) seqtimewait,
                               sum(DECODE(event#,:scat#,total_waits,0)) scatwaits,
                               sum(DECODE(event#,:scat#,time_waited,0)) scattimewait,
                               sum(DECODE(event#,:enq#,total_waits,0)) enqwaits,
                               sum(DECODE(event#,:enq#,time_waited,0)) enqtimewait,
                               sum(DECODE(event#,:lf#,total_waits,0)) lfwaits,
                               sum(DECODE(event#,:lf#,time_waited,0)) lftimewait,
                               sum(DECODE(event#,:lfs#,total_waits,0)) lfswaits,
                               sum(DECODE(event#,:lfs#,time_waited,0)) lfstimewait,
                               sum(DECODE(event#,:lfs#,total_timeouts,0)) lfstimeout,
                               sum(DECODE(event#,:lfpw#,total_waits,0)) lfpwwaits,
                               sum(DECODE(event#,:lfpw#,time_waited,0)) lfpwtimewait,
                               sum(DECODE(event#,:fbw#,total_waits,0)) fbwwaits,
                               sum(DECODE(event#,:fbw#,time_waited,0)) fbwtimewait,
                               sum(DECODE(event#,:lfsci#,total_waits,0)) lfsciwaits,
                               sum(DECODE(event#,:lfsci#,time_waited,0)) lfscitimewait,
                               sum(DECODE(event#,:lfsan#,total_waits,0)) lfsanwaits,
                               sum(DECODE(event#,:lfsan#,time_waited,0)) lfsantimewait,
                               sum(DECODE(event#,:lcl#,total_waits,0)) lclwaits,
                               sum(DECODE(event#,:lcl#,time_waited,0)) lcltimewait,
                               sum(DECODE(event#,:lcp#,total_waits,0)) lcpwaits,
                               sum(DECODE(event#,:lcp#,time_waited,0)) lcptimewait,
                               sum(DECODE(event#,:lbs#,total_waits,0)) lbswaits,
                               sum(DECODE(event#,:lbs#,time_waited,0)) lbstimewait,
                               sum(DECODE(event#,:lfsr#,total_waits,0)) lfsrwaits,
                               sum(DECODE(event#,:lfsr#,time_waited,0)) lfsrtimewait,
                               sum(DECODE(event#,:snmtc#,total_waits,0)) snmtcwaits,
                               sum(DECODE(event#,:snmtc#,time_waited,0)) snmtctimewait
                          from sk_system_event e, sk_timing t
                         where t.timestamp between to_date('&&sdate','YYYY-MM-DD')-60/86400 and
                                                   to_date('&&sdate-23:59:59','YYYY-MM-DD-HH24:MI:SS')
                           and e.lfdnr = t.lfdnr
                           and e.event# in (:bbw#,:seq#,:scat#,:enq#,:lf#,:lfs#,:lfpw#,:fbw#,:lfsci#,
                                            :lfsan#,:lcl#,:lcp#,:lbs#,:lfsr#,:snmtc#)
                         group by e.lfdnr))
         where prev_bbwwaits != -1
         order by timestamp);
spool off

set heading on
set feedback on
