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.3
Rem
Rem Version history:
Rem Date          |Text
Rem --------------+--------------------------------------------------------------
Rem Nov. 05th 2004|Version 0.3 Included abbreviated name of day in the output.
Rem May. 28th 2004|Version 0.2 (Restrict the where condition to 5 min. around midnight to
Rem               |             speed up the select)
Rem May. 27th 2004|Version 0.1 (Newly created version without a selfjoin)
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

alter session set db_file_multiblock_read_count = 128;

variable latch_free                       number
variable enqueue                          number
variable free_buffer_waits                number
variable buffer_busy_waits                number
variable log_file_parallel_write          number
variable log_file_switch_checkpoint_inc   number
variable log_file_switch_archiving_need   number
variable log_file_sync                    number
variable db_file_sequential_read          number
variable db_file_scattered_read           number
variable library_cache_pin                number
variable library_cache_lock               number
variable log_file_sequential_read         number

begin
  select sum(decode(name,'latch free',event#,0)),
         sum(decode(name,'enqueue',event#,0)),
         sum(decode(name,'free buffer waits',event#,0)),
         sum(decode(name,'buffer busy waits',event#,0)),
         sum(decode(name,'log file parallel write',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,'log file sync',event#,0)),
         sum(decode(name,'db file sequential read',event#,0)),
         sum(decode(name,'db file scattered read',event#,0)),
         sum(decode(name,'library cache pin',event#,0)),
         sum(decode(name,'library cache lock',event#,0)),
         sum(decode(name,'log file sequential read',event#,0))
    into :latch_free                    , /* latch free */
         :enqueue                       , /* enqueue */
         :free_buffer_waits             , /* free buffer waits */
         :buffer_busy_waits             , /* buffer busy waits */
         :log_file_parallel_write       , /* log file parallel write */
         :log_file_switch_checkpoint_inc, /* log file switch (checkpoint incomplete) */
         :log_file_switch_archiving_need, /* log file switch (archiving needed) */
         :log_file_sync                 , /* log file sync */
         :db_file_sequential_read       , /* db file sequential read */
         :db_file_scattered_read        , /* db file scattered read */
         :library_cache_pin             , /* library cache pin */
         :library_cache_lock            , /* library cache lock */
         :log_file_sequential_read        /* log file sequential read */
    from sk_event_name;
end;
/

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD_HH24MISS') dcol
  from dual;

spool event_overview_&&mydate..out

select 'Timestamp'
       ||chr(9)||'Period in sec'
       ||chr(9)||'buffer busy wait in Million'
       ||chr(9)||'buffer busy wait avg. wait time in ms'
       ||chr(9)||'db file sequential read in Million'
       ||chr(9)||'db file sequential read avg. wait time in ms'
       ||chr(9)||'db file scattered read in Million'
       ||chr(9)||'db file scattered read avg. wait time in ms'
       ||chr(9)||'enqueue in Thousand'
       ||chr(9)||'enqueue avg. wait time in ms'
       ||chr(9)||'latch free in Million'
       ||chr(9)||'latch free avg. wait time in ms'
       ||chr(9)||'log file sync in Million'
       ||chr(9)||'log file sync avg. wait time in ms'
       ||chr(9)||'log file parallel write in Million'
       ||chr(9)||'log file parallel write avg. wait time in ms'
       ||chr(9)||'free buffer waits'
       ||chr(9)||'free buffer waits avg. wait time in ms'
       ||chr(9)||'log file switch (checkpoint incomplete)'
       ||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) avg. wait time in ms'
       ||chr(9)||'library cache lock'
       ||chr(9)||'library cache lock avg. wait time in ms'
       ||chr(9)||'library cache pin'
       ||chr(9)||'library cache pin avg. wait time in ms'
       ||chr(9)||'log file sequential read'
       ||chr(9)||'log file sequential read avg. wait time in ms'
  from dual
union all
select time||chr(9)||sec||chr(9)||
       to_char(round(bbwwaits/1000000,2))||chr(9)||
       to_char(round(decode(bbwwaits,0,0,bbwtimewait/bbwwaits*10),2))||chr(9)||
       to_char(round(seqwaits/1000000,2))||chr(9)||
       to_char(round(decode(seqwaits,0,0,seqtimewait/seqwaits*10),2))||chr(9)||
       to_char(round(scatwaits/1000000,2))||chr(9)||
       to_char(round(decode(scatwaits,0,0,scattimewait/scatwaits*10),2))||chr(9)||
       to_char(round(enqwaits/1000,2))||chr(9)||
       to_char(round(decode(enqwaits,0,0,enqtimewait/enqwaits*10),2))||chr(9)||
       to_char(round(lfwaits/1000000,2))||chr(9)||
       to_char(round(decode(lfwaits,0,0,lftimewait/lfwaits*10),2))||chr(9)||
       to_char(round(lfswaits/1000000,2))||chr(9)||
       to_char(round(decode(lfswaits,0,0,lfstimewait/lfswaits*10),2))||chr(9)||
       to_char(round(lfpwwaits/1000000,2))||chr(9)||
       to_char(round(decode(lfpwwaits,0,0,lfpwtimewait/lfpwwaits*10),2))||chr(9)||
       fbwwaits||chr(9)||
       to_char(round(decode(fbwwaits,0,0,fbwtimewait/fbwwaits*10),2))||chr(9)||
       lfsciwaits||chr(9)||
       to_char(round(decode(lfsciwaits,0,0,lfscitimewait/lfsciwaits*10),2))||chr(9)||
       lfsanwaits||chr(9)||
       to_char(round(decode(lfsanwaits,0,0,lfsantimewait/lfsanwaits*10),2))||chr(9)||
       lclwaits||chr(9)||
       to_char(round(decode(lclwaits,0,0,lcltimewait/lclwaits*10),2))||chr(9)||
       lcpwaits||chr(9)||
       to_char(round(decode(lcpwaits,0,0,lcptimewait/lcpwaits*10),2))||chr(9)||
       lfsrwaits||chr(9)||
       to_char(round(decode(lfsrwaits,0,0,lfsrtimewait/lfsrwaits*10),2))
  from (select time,sec,
               xbbwwaits-ibbwwaits bbwwaits,
               xbbwtimewait-ibbwtimewait bbwtimewait,
               xseqwaits-iseqwaits seqwaits,
               xseqtimewait-iseqtimewait seqtimewait,
               xscatwaits-iscatwaits scatwaits,
               xscattimewait-iscattimewait scattimewait,
               xenqwaits-ienqwaits enqwaits,
               xenqtimewait-ienqtimewait enqtimewait,
               xlfwaits-ilfwaits lfwaits,
               xlftimewait-ilftimewait lftimewait,
               xlfswaits-ilfswaits lfswaits,
               xlfstimewait-ilfstimewait lfstimewait,
               xlfpwwaits-ilfpwwaits lfpwwaits,
               xlfpwtimewait-ilfpwtimewait lfpwtimewait,
               xfbwwaits-ifbwwaits fbwwaits,
               xfbwtimewait-ifbwtimewait fbwtimewait,
               xlfsciwaits-ilfsciwaits lfsciwaits,
               xlfscitimewait-ilfscitimewait lfscitimewait,
               xlfsanwaits-ilfsanwaits lfsanwaits,
               xlfsantimewait-ilfsantimewait lfsantimewait,
               xlclwaits-ilclwaits lclwaits,
               xlcltimewait-ilcltimewait lcltimewait,
               xlcpwaits-ilcpwaits lcpwaits,
               xlcptimewait-ilcptimewait lcptimewait,
               xlfsrwaits-ilfsrwaits lfsrwaits,
               xlfsrtimewait-ilfsrtimewait lfsrtimewait
          from (select time,
                       (to_date(substr(maxresultrow,1,19),'YYYY-MM-DD-HH24:MI:SS')-
                        to_date(substr(minresultrow,1,19),'YYYY-MM-DD-HH24:MI:SS'))*86400 sec,
                       to_number(substr(minresultrow, 21,20)) ibbwwaits,
                       to_number(substr(maxresultrow, 21,20)) xbbwwaits,
                       to_number(substr(minresultrow, 41,20)) ibbwtimewait,
                       to_number(substr(maxresultrow, 41,20)) xbbwtimewait,
                       to_number(substr(minresultrow, 61,20)) iseqwaits,
                       to_number(substr(maxresultrow, 61,20)) xseqwaits,
                       to_number(substr(minresultrow, 81,20)) iseqtimewait,
                       to_number(substr(maxresultrow, 81,20)) xseqtimewait,
                       to_number(substr(minresultrow,101,20)) iscatwaits,
                       to_number(substr(maxresultrow,101,20)) xscatwaits,
                       to_number(substr(minresultrow,121,20)) iscattimewait,
                       to_number(substr(maxresultrow,121,20)) xscattimewait,
                       to_number(substr(minresultrow,141,20)) ienqwaits,
                       to_number(substr(maxresultrow,141,20)) xenqwaits,
                       to_number(substr(minresultrow,161,20)) ienqtimewait,
                       to_number(substr(maxresultrow,161,20)) xenqtimewait,
                       to_number(substr(minresultrow,181,20)) ilfwaits,
                       to_number(substr(maxresultrow,181,20)) xlfwaits,
                       to_number(substr(minresultrow,201,20)) ilftimewait,
                       to_number(substr(maxresultrow,201,20)) xlftimewait,
                       to_number(substr(minresultrow,221,20)) ilfswaits,
                       to_number(substr(maxresultrow,221,20)) xlfswaits,
                       to_number(substr(minresultrow,241,20)) ilfstimewait,
                       to_number(substr(maxresultrow,241,20)) xlfstimewait,
                       to_number(substr(minresultrow,261,20)) ilfpwwaits,
                       to_number(substr(maxresultrow,261,20)) xlfpwwaits,
                       to_number(substr(minresultrow,281,20)) ilfpwtimewait,
                       to_number(substr(maxresultrow,281,20)) xlfpwtimewait,
                       to_number(substr(minresultrow,301,20)) ifbwwaits,
                       to_number(substr(maxresultrow,301,20)) xfbwwaits,
                       to_number(substr(minresultrow,321,20)) ifbwtimewait,
                       to_number(substr(maxresultrow,321,20)) xfbwtimewait,
                       to_number(substr(minresultrow,341,20)) ilfsciwaits,
                       to_number(substr(maxresultrow,341,20)) xlfsciwaits,
                       to_number(substr(minresultrow,361,20)) ilfscitimewait,
                       to_number(substr(maxresultrow,361,20)) xlfscitimewait,
                       to_number(substr(minresultrow,381,20)) ilfsanwaits,
                       to_number(substr(maxresultrow,381,20)) xlfsanwaits,
                       to_number(substr(minresultrow,401,20)) ilfsantimewait,
                       to_number(substr(maxresultrow,401,20)) xlfsantimewait,
                       to_number(substr(minresultrow,421,20)) ilclwaits,
                       to_number(substr(maxresultrow,421,20)) xlclwaits,
                       to_number(substr(minresultrow,441,20)) ilcltimewait,
                       to_number(substr(maxresultrow,441,20)) xlcltimewait,
                       to_number(substr(minresultrow,461,20)) ilcpwaits,
                       to_number(substr(maxresultrow,461,20)) xlcpwaits,
                       to_number(substr(minresultrow,481,20)) ilcptimewait,
                       to_number(substr(maxresultrow,481,20)) xlcptimewait,
                       to_number(substr(minresultrow,501,20)) ilfsrwaits,
                       to_number(substr(maxresultrow,501,20)) xlfsrwaits,
                       to_number(substr(minresultrow,521,20)) ilfsrtimewait,
                       to_number(substr(maxresultrow,521,20)) xlfsrtimewait
                  from (select to_char(timestamp,'DY YYYY-MM-DD','NLS_DATE_LANGUAGE=ENGLISH') time,
                               min(resultrow) minresultrow,
                               max(resultrow) maxresultrow
                          from (select e.lfdnr,t.timestamp,
                                       to_char(t.timestamp,'YYYY-MM-DD-HH24:MI:SS')||'0'|| /* one extra byte to make a nice offset (20)*/
                                       lpad(to_char(sum(DECODE(e.event#,:buffer_busy_waits,total_waits,0))),20,'0')||              /* # of buffer busy waits */
                                       lpad(to_char(sum(DECODE(e.event#,:buffer_busy_waits,time_waited,0))),20,'0')||              /* buffer busy waits wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:db_file_sequential_read,total_waits,0))),20,'0')||        /* # of db file sequential read */
                                       lpad(to_char(sum(DECODE(e.event#,:db_file_sequential_read,time_waited,0))),20,'0')||        /* db file sequential read wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:db_file_scattered_read,total_waits,0))),20,'0')||         /* # of db file scattered read */
                                       lpad(to_char(sum(DECODE(e.event#,:db_file_scattered_read,time_waited,0))),20,'0')||         /* db file scattered read wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:enqueue,total_waits,0))),20,'0')||                        /* # of enqueue */
                                       lpad(to_char(sum(DECODE(e.event#,:enqueue,time_waited,0))),20,'0')||                        /* enqueue wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:latch_free,total_waits,0))),20,'0')||                     /* # of latch free */
                                       lpad(to_char(sum(DECODE(e.event#,:latch_free,time_waited,0))),20,'0')||                     /* latch free wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_sync,total_waits,0))),20,'0')||                  /* # of log file sync */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_sync,time_waited,0))),20,'0')||                  /* log file sync wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_parallel_write,total_waits,0))),20,'0')||        /* # of log file parallel write */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_parallel_write,time_waited,0))),20,'0')||        /* log file parallel write wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:free_buffer_waits,total_waits,0))),20,'0')||              /* # of free buffer waits */
                                       lpad(to_char(sum(DECODE(e.event#,:free_buffer_waits,time_waited,0))),20,'0')||              /* free buffer waits wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_switch_checkpoint_inc,total_waits,0))),20,'0')|| /* # of log file switch (checkpoint incomplete) */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_switch_checkpoint_inc,time_waited,0))),20,'0')|| /* log file switch (checkpoint incomplete) wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_switch_archiving_need,total_waits,0))),20,'0')|| /* # of log file switch (archiving needed) */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_switch_archiving_need,time_waited,0))),20,'0')|| /* log file switch (archiving needed) wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:library_cache_lock,total_waits,0))),20,'0')||             /* # of library cache lock */
                                       lpad(to_char(sum(DECODE(e.event#,:library_cache_lock,time_waited,0))),20,'0')||             /* library cache lock wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:library_cache_pin,total_waits,0))),20,'0')||              /* # of library cache pin */
                                       lpad(to_char(sum(DECODE(e.event#,:library_cache_pin,time_waited,0))),20,'0')||              /* library cache pin wait time */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_sequential_read,total_waits,0))),20,'0')||              /* # of log file sequential read */
                                       lpad(to_char(sum(DECODE(e.event#,:log_file_sequential_read,time_waited,0))),20,'0') resultrow      /* log file sequentail read wait time */
                                  from sk_system_event e, sk_timing t
                                 where (to_char(t.timestamp,'HH24MISS') between '235500' and '235959'
                                     or to_char(t.timestamp,'HH24MISS') between '000000' and '000500')
                                   and t.lfdnr = e.lfdnr
                                 group by e.lfdnr,t.timestamp)
                         group by to_char(timestamp,'DY YYYY-MM-DD','NLS_DATE_LANGUAGE=ENGLISH'))
                 where 86000 < (to_date(substr(maxresultrow,1,19),'YYYY-MM-DD-HH24:MI:SS')-
                                to_date(substr(minresultrow,1,19),'YYYY-MM-DD-HH24:MI:SS'))*86400)
         order by substr(time,5));
spool off

set heading on
set feedback on
