/*
Program     : optstat_event_matrix_weekly.sql
Purpose     : Display the event waits and times in seconds for a given event for the last 30 days in a weekly format
Author      : Daniel W. Fink OptimalDBA.com
Created     : January 29, 2008
Update      : 
Parameters  : p_days - the number of days to examine
              p_event_name - The name of the event. 
              **NOTE** since event names contain spaces, it must be passed in using the format '"event name"'
              @./statspack/optstat_event_matrix_weekly 21 '"db file sequential read"'
Exit Codes  : Not Used
Comments    : 
Disclaimer  : No warranty is provided for any use of the script, statements or logic included. 
              This script, statements and logic are for personal use only and may not be included 
              as part of a commercial product. 
              Please address any comments to scripts@optimaldba.com
              This comment block and all lines above must be included.
*/

DEFINE p_days = &1
DEFINE p_event_name = &2

SET LINESIZE 500 PAGESIZE 0 VERIFY OFF

COLUMN v_begin_day NEW_VALUE v_begin_day NOPRINT
SELECT     MIN((TRUNC(snap_time) - TO_NUMBER(TO_CHAR(snap_time, 'D'))+1)) v_begin_day
FROM       stats$snapshot
WHERE      snap_time >= sysdate - (&p_days + (TO_NUMBER(TO_CHAR((sysdate - &p_days), 'D'))-1))
/

-- Set up variables for the report
DEFINE v_days = &p_days
DEFINE v_report_title = '"Event Daily Matrix"'
DEFINE v_spool_title = 'EventDailyMtrx_&p_event_name'
DEFINE v_comment = '"Event - &p_event_name"'
DEFINE v_col_size = 13

@@optstat_matrix_header.sql

SET VERIFY OFF LINESIZE 113 PAGESIZE 400

COLUMN     snap_week    FORMAT A15               HEADING 'Week of'
COLUMN     snap_sun     FORMAT A&&v_col_size     HEADING 'Sunday'
COLUMN     snap_mon     FORMAT A&&v_col_size     HEADING 'Monday'
COLUMN     snap_tue     FORMAT A&&v_col_size     HEADING 'Tuesday'
COLUMN     snap_wed     FORMAT A&&v_col_size     HEADING 'Wednesday'
COLUMN     snap_thu     FORMAT A&&v_col_size     HEADING 'Thursday'
COLUMN     snap_fri     FORMAT A&&v_col_size     HEADING 'Friday'
COLUMN     snap_sat     FORMAT A&&v_col_size     HEADING 'Saturday'

WITH    event_snap_values AS
(
   SELECT     sn.snap_time snap_time,
              sn.snap_id snap_id,
              se.total_waits - ( CASE WHEN sn.startup_time > LAG(sn.snap_time) OVER (ORDER BY sn.snap_id) THEN 0
                                      ELSE LAG(se.total_waits) OVER (ORDER BY sn.snap_id) 
                                 END
                               ) snap_waits,
              se.time_waited_micro - ( CASE WHEN sn.startup_time > LAG(sn.snap_time) OVER (ORDER BY sn.snap_id) THEN 0
                                            ELSE LAG(se.time_waited_micro) OVER (ORDER BY sn.snap_id) 
                                       END
                                     ) snap_time_waited_micro
   FROM       stats$snapshot sn,
              stats$system_event se
   WHERE      sn.snap_time >= TO_DATE('&&v_begin_day')
     AND      sn.snap_time <  TRUNC(sysdate)
     AND      se.snap_id = sn.snap_id
     AND      se.event = '&&p_event_name'
),
day_event_values AS
(
   SELECT     TRUNC(snap_time) snap_day,
              MIN(snap_id) day_start_snap_id,
              SUM(snap_waits) day_waits,
              SUM(snap_time_waited_micro) day_time_microseconds,
              ROUND(SUM(snap_time_waited_micro)/1000000) day_time_seconds
   FROM       event_snap_values
   GROUP BY   TRUNC(snap_time)
),
snap_weekly AS
( SELECT     (TRUNC(snap_time) - TO_NUMBER(TO_CHAR(snap_time, 'D'))+1) snap_week_start,
             TRUNC(snap_time) snap_date,
             MIN(snap_id) first_snap
  FROM       stats$snapshot
  WHERE      snap_time >= TO_DATE('&&v_begin_day')
    AND      snap_time <  TRUNC(sysdate)
  GROUP BY   (TRUNC(snap_time) - TO_NUMBER(TO_CHAR(snap_time, 'D'))+1), TRUNC(snap_time)
)
SELECT TO_CHAR(sw.snap_week_start,'Mon DD, YYYY')||CHR(10)||'        Waits'||
               CHR(10)||'    Time(sec)'||CHR(10)||' Avg Wait(us)' snap_week,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),1,TO_CHAR(sw.first_snap, '99999999999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),1,TO_CHAR(day_waits,'999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),1,TO_CHAR(day_time_seconds,'999,999,999'),null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),1,TO_CHAR(ROUND(day_time_microseconds/day_waits),'999,999,999'),null)
          ) snap_sun,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),2,TO_CHAR(sw.first_snap, '99999999999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),2,TO_CHAR(day_waits,'999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),2,TO_CHAR(day_time_seconds,'999,999,999'),null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),2,TO_CHAR(ROUND(day_time_microseconds/day_waits),'999,999,999'),null)
          ) snap_mon,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),3,TO_CHAR(sw.first_snap, '99999999999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),3,TO_CHAR(day_waits,'999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),3,TO_CHAR(day_time_seconds,'999,999,999'),null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),3,TO_CHAR(ROUND(day_time_microseconds/day_waits),'999,999,999'),null)
          ) snap_tue,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),4,TO_CHAR(sw.first_snap, '99999999999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),4,TO_CHAR(day_waits,'999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),4,TO_CHAR(day_time_seconds,'999,999,999'),null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),4,TO_CHAR(ROUND(day_time_microseconds/day_waits),'999,999,999'),null)
          ) snap_wed,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),5,TO_CHAR(sw.first_snap, '99999999999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),5,TO_CHAR(day_waits,'999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),5,TO_CHAR(day_time_seconds,'999,999,999'),null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),5,TO_CHAR(ROUND(day_time_microseconds/day_waits),'999,999,999'),null)
          ) snap_thu,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),6,TO_CHAR(sw.first_snap, '99999999999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),6,TO_CHAR(day_waits,'999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),6,TO_CHAR(day_time_seconds,'999,999,999'),null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),6,TO_CHAR(ROUND(day_time_microseconds/day_waits),'999,999,999'),null)
          ) snap_fri,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),7,TO_CHAR(sw.first_snap, '99999999999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),7,TO_CHAR(day_waits,'999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),7,TO_CHAR(day_time_seconds,'999,999,999'),null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),7,TO_CHAR(ROUND(day_time_microseconds/day_waits),'999,999,999'),null)
          ) snap_sat
FROM snap_weekly sw,
     day_event_values dev
WHERE  sw.snap_date = dev.snap_day
GROUP BY snap_week_start
ORDER BY snap_week_start

SPOOL &v_spool_name
/
SPOOL OFF

COLUMN     snap_week    CLEAR
COLUMN     snap_sun     CLEAR
COLUMN     snap_mon     CLEAR
COLUMN     snap_tue     CLEAR
COLUMN     snap_wed     CLEAR
COLUMN     snap_thu     CLEAR
COLUMN     snap_fri     CLEAR
COLUMN     snap_sat     CLEAR