Rem ###########################################################################################
Rem 
Rem This SQL statement returns an overview of the database response time (important events and CPU).
Rem You need to run the "dbmon" script first in order to collect the raw statistic data.
Rem
Rem
Rem Copyright: (c) 2006
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.2
Rem
Rem Version history:
Rem Date          |Text
Rem --------------+--------------------------------------------------------------
Rem Mar. 22th 2006|Version 0.2 Use Analytic functions to simplify and make it more version independent
Rem Jul. 28th 2005|Version 0.1 Created
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 20000
set heading off
set feedback off
set verify off
set newpage none
clear buffer
set colsep '	'

alter session set db_file_multiblock_read_count = 128;

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

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 rcl#   number

variable CPU_used_by_this_session       number

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,'row cache lock',event#,0))
    into :bbw#,
         :seq#,
         :scat#,
         :enq#,
         :lf#,
         :lfs#,
         :lfpw#,
         :fbw#,
         :lfsci#,
         :lfsan#,
         :lcl#,
         :lcp#,
         :lbs#,
         :lfsr#,
         :rcl#
    from sk_event_name;

  select sum(decode(name,'CPU used by this session',statistic#,0))
    into :CPU_used_by_this_session        /* CPU used by this session */
    from sk_statname;
end;
/

spool responsetime_&&sdate..out

with lfdnr as (select lfdnr,timestamp
                 from sk_timing
                where timestamp between to_date('&&sdate','YYYY-MM-DD')-60/86400 and
                                        to_date('&&sdate-23:59:59','YYYY-MM-DD-HH24:MI:SS')),
     lfdnr2 as (select min(lfdnr) minlfdnr, max(lfdnr) maxlfdnr
                  from lfdnr),
     event as (select lfdnr,event#,to_date('20000101','YYYYMMDD') timestamp,
                      time_waited-lag(time_waited) over(partition by event# order by lfdnr) time_waited,0 sec
                 from sk_system_event
                where event# not in(select event#
                                      from sk_event_name
                                     where name in ('pmon timer',
                                                    'rdbms ipc message',
                                                    'wait for unread message on broadcast channel',
                                                    'wait for unread message on multiple broadcast channels',
                                                    'ges remote message',
                                                    'ges reconfiguration to start',
                                                    'gcs remote message',
                                                    'PX Deq: Par Recov Reply',
                                                    'PX Deq: Par Recov Execute',
                                                    'PX Deq: Par Recov Change Vector',
                                                    'wait for activate message',
                                                    'wakeup event for builder',
                                                    'wakeup event for preparer',
                                                    'wakeup event for reader',
                                                    'wait for transaction',
                                                    'parallel recovery coordinator waits for cleanup of slaves',
                                                    'smon timer',
                                                    'PX Deq: Txn Recovery Start',
                                                    'PX Deq: Txn Recovery Reply',
                                                    'statement suspended, wait error to be cleared',
                                                    'PX Deq: Index Merge Reply',
                                                    'PX Deq: Index Merge Execute',
                                                    'PX Deq: Index Merge Close',
                                                    'virtual circuit status',
                                                    'dispatcher timer',
                                                    'jobq slave wait',
                                                    'pipe get',
                                                    'PX Deque wait',
                                                    'PX Idle Wait',
                                                    'PX Deq: Join ACK',
                                                    'PX Deq Credit: need buffer',
                                                    'PX Deq: Msg Fragment',
                                                    'PX Deq: Parse Reply',
                                                    'PX Deq: Execute Reply',
                                                    'PX Deq: Execution Msg',
                                                    'PX Deq: Table Q Normal',
                                                    'PX Deq: Table Q Sample',
                                                    'PX Deq Credit: send blkd',
                                                    'PX Deq: Signal ACK',
                                                    'single-task message',
                                                    'SQL*Net message from client',
                                                    'SQL*Net message from dblink',
                                                    'PL/SQL lock timer',
                                                    'queue messages',
                                                    'wakeup time manager',
                                                    'STREAMS apply coord waiting for slave message',
                                                    'STREAMS apply coord waiting for some work to finish',
                                                    'STREAMS fetch slave waiting for txns',
                                                    'STREAMS capture process filter callback wait for ruleset',
                                                    'i/o slave wait',
                                                    'io done'))
                and lfdnr between (select minlfdnr from lfdnr2) and (select maxlfdnr from lfdnr2)),
     cpu as (select s.lfdnr,4711 event#,t.timestamp,
                    s.value-lag(s.value) over(order by s.lfdnr) time_waited,
                    to_number(t.timestamp-lag(t.timestamp) over(order by s.lfdnr))*86400 sec
               from sk_sysstat s, lfdnr t
              where s.lfdnr = t.lfdnr
                and s.statistic# = :CPU_used_by_this_session),
     event_time as (select lfdnr,event#,time_waited,
                           max(timestamp) over(partition by lfdnr) timestamp,
                           ratio_to_report(time_waited) over(partition by lfdnr)*100 pct,
                           sum(time_waited) over(partition by lfdnr) total,
                           max(sec) over(partition by lfdnr) sec
                      from (select * from event union all select * from cpu)
                     where time_waited is not null),
     rest as (select lfdnr,event#,time_waited,round(pct,2) pct,total,maximum,rest,sec,timestamp
                from (select lfdnr,event#,time_waited,pct,total,sec,timestamp,
                             max(pct) over(partition by lfdnr) maximum,
                             sum(pct) over(partition by lfdnr) rest
                        from event_time
                       where event# not in (:bbw#,:seq#,:scat#,:enq#,:lf#,:lfs#,:lfpw#,:fbw#,:lfsci#,:lfsan#,:lcl#,:lcp#,:lbs#,:lfsr#,4711))
               where pct = maximum),
     alltogether as (select lfdnr,event#,sec,round(time_waited/sec,2) time_waited,round(pct,2) pct,total,timestamp
                       from event_time
                      where event# in (:bbw#,:seq#,:scat#,:enq#,:lf#,:lfs#,:lfpw#,:fbw#,:lfsci#,:lfsan#,:lcl#,:lcp#,:lbs#,:lfsr#,4711)
                     union all
                    select lfdnr,event#+10000,sec,round(time_waited/sec,2),round(maximum,2),total,timestamp
                      from rest
                     union all
                    select lfdnr,4712,sec,round(time_waited/sec,2),round(rest,2),total,timestamp
                      from rest)
select 'Timestamp',
       'DB Time in hsec per second',
       'CPU in %',
       'buffer busy waits in %',
       'db file sequential read in %',
       'db file scattered read in %',
       'enqueue in %',
       'latch free in %',
       'log file sync in %',
       'log buffer space in %',
       'free buffer waits in %',
       'log file switch (checkpoint incomplete) in %',
       'log file switch (archiving needed) in %',
       'library cache pin in %',
       'library cache lock in %',
       'row cache lock in %',
       'Other in %',
       'Other Event with max contribution in %'
  from dual
union all
select to_char(timestamp,'YYYY-MM-DD-HH24:MI:SS'),to_char(round(total/sec,2)),to_char(cpu_pct),to_char(bbw_pct),to_char(seq_pct),
       to_char(scat_pct),to_char(enq_pct),to_char(lf_pct),
       to_char(lfs_pct),to_char(lbs_pct),to_char(fbw_pct),to_char(lfsci_pct),
       to_char(lfsan_pct),to_char(lcp_pct),to_char(lcl_pct),
       to_char(rcl_pct),to_char(other_pct),name
  from (select sec,total,timestamp,cpu_pct,bbw_pct,seq_pct,scat_pct,enq_pct,lf_pct,lfs_pct,
               lbs_pct,fbw_pct,lfsci_pct,lfsan_pct,lcp_pct,lcl_pct,rcl_pct,other_pct,
               to_char(rest_pct)||'% '||e.name name
          from (select sec,total,timestamp,
                       sum(decode(event#,4711,pct,0)) cpu_pct,
                       sum(decode(event#,:bbw#,pct,0)) bbw_pct,
                       sum(decode(event#,:seq#,pct,0)) seq_pct,
                       sum(decode(event#,:scat#,pct,0)) scat_pct,
                       sum(decode(event#,:enq#,pct,0)) enq_pct,
                       sum(decode(event#,:lf#,pct,0)) lf_pct,
                       sum(decode(event#,:lfs#,pct,0)) lfs_pct,
                       sum(decode(event#,:lbs#,pct,0)) lbs_pct,
                       sum(decode(event#,:fbw#,pct,0)) fbw_pct,
                       sum(decode(event#,:lfsci#,pct,0)) lfsci_pct,
                       sum(decode(event#,:lfsan#,pct,0)) lfsan_pct,
                       sum(decode(event#,:lcp#,pct,0)) lcp_pct,
                       sum(decode(event#,:lcl#,pct,0)) lcl_pct,
                       sum(decode(event#,:rcl#,pct,0)) rcl_pct,
                       sum(decode(event#,4712,pct,0)) other_pct,
                       sum(decode(sign(event#-10000),1,pct,0)) rest_pct,
                       sum(decode(sign(event#-10000),1,event#-10000,0)) rest_event#
                  from alltogether
                 group by sec,total,timestamp) a, sk_event_name e
         where a.rest_event# = e.event#
         order by timestamp);

spool off

set heading on
set feedback on
