/*
Program     : optstat_statistic_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_stat_name - the name of the statistic. 
              **NOTE** since statistic names contain spaces, it must be passed in using the format '"statistic name"'
              @./statspack/optstat_statistic_matrix_weekly 21 '"session logical reads"'
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_stat_name = &2
SET VERIFY OFF LINESIZE 500 PAGESIZE 0

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 = '"Statistic Weekly Matrix"'
DEFINE v_spool_title = 'StatsWklyMtrx_&p_stat_name'
DEFINE v_comment = '"Statistic - &p_stat_name"'

@@optstat_matrix_header.sql

SET VERIFY OFF LINESIZE 132 PAGESIZE 400

COLUMN     snap_week    FORMAT A13     HEADING 'Week of'
COLUMN     snap_sun     FORMAT A16     HEADING 'Sunday'
COLUMN     snap_mon     FORMAT A16     HEADING 'Monday'
COLUMN     snap_tue     FORMAT A16     HEADING 'Tuesday'
COLUMN     snap_wed     FORMAT A16     HEADING 'Wednesday'
COLUMN     snap_thu     FORMAT A16     HEADING 'Thursday'
COLUMN     snap_fri     FORMAT A16     HEADING 'Friday'
COLUMN     snap_sat     FORMAT A16     HEADING 'Saturday'

WITH    statistic_snap_values AS
(
   SELECT     sn.snap_time snap_time,
              sn.snap_id snap_id,
              sy.value - ( CASE WHEN sn.startup_time > LAG(sn.snap_time) OVER (ORDER BY sn.snap_id) THEN 0
                                ELSE LAG(sy.value) OVER (ORDER BY sn.snap_id) 
                           END
                          ) snap_stat_values,
              (( sn.snap_time - ( CASE WHEN sn.startup_time > LAG(sn.snap_time) 
                                                                 OVER (ORDER BY sn.snap_id) THEN sn.startup_time
                                       ELSE LAG(sn.snap_time) OVER (ORDER BY sn.snap_id) 
                                  END
                                )) * 86400) snap_time_seconds                          
   FROM       stats$snapshot sn,
              stats$sysstat sy
   WHERE      sn.snap_time >= TO_DATE('&&v_begin_day')
     AND      sn.snap_time <  TRUNC(sysdate)
     AND      sy.snap_id = sn.snap_id
     AND      UPPER(sy.name) = UPPER('&p_stat_name')
),
day_statistic_values AS
(
   SELECT     TRUNC(snap_time) snap_day,
              SUM(snap_time_seconds) day_seconds,
              SUM(snap_stat_values) day_stat_values
   FROM       statistic_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)||'  Per Second' snap_week,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),1,TO_CHAR(day_stat_values,'99,999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),1,TO_CHAR(ROUND(day_stat_values/day_seconds),'99,999,999,999'), null)
          ) snap_sun,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),2,TO_CHAR(day_stat_values,'99,999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),2,TO_CHAR(ROUND(day_stat_values/day_seconds),'99,999,999,999'), null)
          ) snap_mon,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),3,TO_CHAR(day_stat_values,'99,999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),3,TO_CHAR(ROUND(day_stat_values/day_seconds),'99,999,999,999'), null)
          ) snap_tue,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),4,TO_CHAR(day_stat_values,'99,999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),4,TO_CHAR(ROUND(day_stat_values/day_seconds),'99,999,999,999'), null)
          ) snap_wed,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),5,TO_CHAR(day_stat_values,'99,999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),5,TO_CHAR(ROUND(day_stat_values/day_seconds),'99,999,999,999'), null)
          ) snap_thu,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),6,TO_CHAR(day_stat_values,'99,999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),6,TO_CHAR(ROUND(day_stat_values/day_seconds),'99,999,999,999'), null)
          ) snap_fri,
       MAX(DECODE(TO_CHAR(sw.snap_date,'D'),7,TO_CHAR(day_stat_values,'99,999,999,999'), null)||CHR(10)||
           DECODE(TO_CHAR(sw.snap_date,'D'),7,TO_CHAR(ROUND(day_stat_values/day_seconds),'99,999,999,999'), null)
          ) snap_sat
FROM snap_weekly sw,
     day_statistic_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