col BEGIN_TIME format a30
col SECONDS format a20
SELECT /*+ OPT_PARAM('_OPTIMIZER_DISTINCT_AGG_TRANSFORM', 'FALSE') */
  NULL BEGIN_TIME, NULL SECONDS, NULL ACT_SESS, NULL CPUS_USED, NULL "T_MS/USERC.", NULL "READS/USERC.", 
  NULL "HITRATIO", NULL "LOG. READS/S", NULL "DISK READS/S", 
  NULL "USER CALLS/S", 
  NULL "COMMITS/S", NULL "REDO BLKS/S" FROM DUAL WHERE 1 = 0
UNION ALL (
SELECT NULL BEGIN_TIME, NULL SECONDS, NULL ACT_SESS, NULL CPUS_USED, NULL "T_MS/USERC.", NULL "READS/USERC.", 
  NULL "HITRATIO", NULL "LOG. READS/S", NULL "DISK READS/S", 
  NULL "USER CALLS/S",
  NULL "COMMITS/S", NULL "REDO BLKS/S" FROM DUAL WHERE 1 = 0
) UNION ALL ( SELECT * FROM (
WITH BASIS_INFO AS
( SELECT 
    DECODE(DBID, -1, OWN_DBID, DBID) DBID,
    DECODE(INSTANCE_NUMBER, -1, USERENV('INSTANCE'), INSTANCE_NUMBER) INSTANCE_NUMBER,
    BEGIN_DATE,
    END_DATE,
    TO_TIMESTAMP(TO_CHAR(BEGIN_DATE, 'dd.mm.yyyy hh24:mi:ss'), 
      'dd.mm.yyyy hh24:mi:ss') BEGIN_TIME,
    TO_TIMESTAMP(TO_CHAR(END_DATE, 'dd.mm.yyyy hh24:mi:ss'), 
      'dd.mm.yyyy hh24:mi:ss') END_TIME,
    AGGREGATE_BY,
    SORT_ORDER,
    EXCLUDE_WEEKENDS
  FROM
  ( SELECT
      -1 DBID,
      -1 INSTANCE_NUMBER,        /* -1 for current instance, -2 for all instances */
      TO_DATE('01.01.1000 12:57:00', 'dd.mm.yyyy hh24:mi:ss') BEGIN_DATE,
      TO_DATE('31.12.9999 00:05:00', 'dd.mm.yyyy hh24:mi:ss') END_DATE,
      'DAY' AGGREGATE_BY,  /* SNAPSHOT, DAY, HOUR_OF_DAY, INSTANCE, SS_INST, DAY_INST, HOD_INST */
      'DESC' SORT_ORDER,     /* ASC, DESC */
      ' ' EXCLUDE_WEEKENDS
    FROM
      DUAL
  ),
  ( SELECT DBID OWN_DBID FROM V$DATABASE )
),
SNAPSHOTS AS
( SELECT /*+ MATERIALIZE */ 
    DBID,
    INSTANCE_NUMBER,
    SNAP_ID,
    PREV_SNAP_ID,
    MIN_SNAP_ID,
    BEGIN_INTERVAL_TIME,
    END_INTERVAL_TIME,
    INTERVAL_SECONDS,
    SUM(INTERVAL_SECONDS) OVER () TOTAL_SECONDS,
    RESTART
  FROM
  ( SELECT
      HSS2.DBID,
      HSS2.INSTANCE_NUMBER,
      HSS2.SNAP_ID,
      HSS1.SNAP_ID PREV_SNAP_ID,
      MIN(HSS2.SNAP_ID) OVER (PARTITION BY HSS2.INSTANCE_NUMBER) MIN_SNAP_ID,
      HSS2.BEGIN_INTERVAL_TIME,
      HSS2.END_INTERVAL_TIME,
      TO_CHAR(HSS2.END_INTERVAL_TIME, 'SSSSS') -
        TO_CHAR(HSS2.BEGIN_INTERVAL_TIME, 'SSSSS') +
        86400 * (TO_CHAR(HSS2.END_INTERVAL_TIME, 'J') - 
                 TO_CHAR(HSS2.BEGIN_INTERVAL_TIME, 'J'))
        INTERVAL_SECONDS,
      DECODE(HSS2.STARTUP_TIME, HSS1.STARTUP_TIME, 'NO', 'YES') RESTART
    FROM 
      BASIS_INFO BI,
      DBA_HIST_SNAPSHOT HSS1, 
      DBA_HIST_SNAPSHOT HSS2
    WHERE
      HSS2.DBID = BI.DBID AND
      HSS1.DBID (+) = HSS2.DBID AND
      ( BI.INSTANCE_NUMBER = -2 OR 
        HSS2.INSTANCE_NUMBER = BI.INSTANCE_NUMBER ) AND
      HSS1.INSTANCE_NUMBER (+) = HSS2.INSTANCE_NUMBER AND
      HSS2.END_INTERVAL_TIME BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND
      HSS1.SNAP_ID (+) = HSS2.SNAP_ID - 1
    ORDER BY
      HSS2.SNAP_ID
  )
),
KEYFIG_PER_INTERVAL AS
( SELECT
    SNAP_ID,
    MIN(BEGIN_INTERVAL_TIME) OVER (PARTITION BY SNAP_ID) BEGIN_INTERVAL_TIME,
    PREV_SNAP_ID,
    INSTANCE_NUMBER,
    SECONDS,
    READS,
    USER_CALLS,
    RECURSIVE_CALLS,
    PHYSICAL_READS,
    COMMITS,
    REDO_BLOCKS,
    DB_TIME_US,
    CPU_TIME_US,
    PHYSICAL_READS_CACHE
  FROM
  ( SELECT
      SS.INSTANCE_NUMBER,
      SS.SNAP_ID,
      SS.BEGIN_INTERVAL_TIME,
      SS.INTERVAL_SECONDS SECONDS,
      DECODE(SS.RESTART, 'YES', HSY1.VALUE, HSY1.VALUE - 
        LAG(HSY1.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) READS,
      DECODE(SS.RESTART, 'YES', HSY2.VALUE, HSY2.VALUE - 
        LAG(HSY2.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) USER_CALLS,
      DECODE(SS.RESTART, 'YES', HSY3.VALUE, HSY3.VALUE - 
        LAG(HSY3.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) RECURSIVE_CALLS,
      DECODE(SS.RESTART, 'YES', HSY4.VALUE, HSY4.VALUE - 
        LAG(HSY4.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) PHYSICAL_READS,
      DECODE(SS.RESTART, 'YES', HSY5.VALUE, HSY5.VALUE - 
        LAG(HSY5.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) COMMITS,
      DECODE(SS.RESTART, 'YES', HSY6.VALUE, HSY6.VALUE - 
        LAG(HSY6.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) REDO_BLOCKS,
      DECODE(SS.RESTART, 'YES', HSY7.VALUE, HSY7.VALUE - 
        LAG(HSY7.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) DB_TIME_US,
      DECODE(SS.RESTART, 'YES', HSY8.VALUE, HSY8.VALUE - 
        LAG(HSY8.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) CPU_TIME_US,
      DECODE(SS.RESTART, 'YES', HSY9.VALUE, HSY9.VALUE - 
        LAG(HSY9.VALUE, 1) OVER (PARTITION BY SS.INSTANCE_NUMBER ORDER BY SS.SNAP_ID)) PHYSICAL_READS_CACHE,
      SS.PREV_SNAP_ID,
      SS.MIN_SNAP_ID
    FROM
      SNAPSHOTS SS,
      DBA_HIST_SYSSTAT HSY1, 
      DBA_HIST_SYSSTAT HSY2, 
      DBA_HIST_SYSSTAT HSY3,
      DBA_HIST_SYSSTAT HSY4,
      DBA_HIST_SYSSTAT HSY5,
      DBA_HIST_SYSSTAT HSY6,
      DBA_HIST_SYS_TIME_MODEL HSY7,
      DBA_HIST_SYS_TIME_MODEL HSY8,
      DBA_HIST_SYSSTAT HSY9
    WHERE
      HSY1.DBID = SS.DBID AND
      HSY2.DBID = SS.DBID AND
      HSY3.DBID = SS.DBID AND
      HSY4.DBID = SS.DBID AND
      HSY5.DBID = SS.DBID AND
      HSY6.DBID = SS.DBID AND
      HSY7.DBID = SS.DBID AND
      HSY8.DBID = SS.DBID AND
      HSY9.DBID = SS.DBID AND
      HSY1.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY2.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY3.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY4.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY5.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY6.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY7.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY8.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY9.INSTANCE_NUMBER = SS.INSTANCE_NUMBER AND
      HSY1.SNAP_ID = SS.SNAP_ID AND
      HSY2.SNAP_ID = SS.SNAP_ID AND
      HSY3.SNAP_ID = SS.SNAP_ID AND
      HSY4.SNAP_ID = SS.SNAP_ID AND
      HSY5.SNAP_ID = SS.SNAP_ID AND
      HSY6.SNAP_ID = SS.SNAP_ID AND
      HSY7.SNAP_ID = SS.SNAP_ID AND
      HSY8.SNAP_ID = SS.SNAP_ID AND
      HSY9.SNAP_ID = SS.SNAP_ID AND
      HSY1.STAT_NAME = 'session logical reads' AND
      HSY2.STAT_NAME = 'user calls' AND
      HSY3.STAT_NAME = 'recursive calls' AND
      HSY4.STAT_NAME = 'physical reads' AND
      HSY5.STAT_NAME = 'user commits' AND
      HSY6.STAT_NAME = 'redo blocks written' AND
      HSY7.STAT_NAME = 'DB time' AND
      HSY8.STAT_NAME = 'DB CPU' AND
      HSY9.STAT_NAME = 'physical reads cache'
  )
  WHERE
    SNAP_ID != MIN_SNAP_ID
)
SELECT 
  'BEGIN TIME:' BEGIN_INTERVAL_TIME, 
  TO_CHAR(MIN(END_INTERVAL_TIME), 'dd.mm.yyyy') SECONDS, 
  TO_CHAR(MIN(END_INTERVAL_TIME), 'hh24:mi:ss') ACT_SESS, 
  NULL CPUS_USED, 
  NULL "T_MS/USERC.", 
  NULL "READS/USERC.", 
  NULL "HITRATIO", 
  NULL "LOG. READS/S", 
  NULL "DISK READS/S", 
  NULL "USER CALLS/S", 
  NULL "COMMITS/S", 
  NULL "REDO BLKS/S" 
FROM
  SNAPSHOTS
UNION ALL
( SELECT 
    'END TIME:' BEGIN_INTERVAL_TIME, 
    TO_CHAR(MAX(END_INTERVAL_TIME), 'dd.mm.yyyy') SECONDS, 
    TO_CHAR(MAX(END_INTERVAL_TIME), 'hh24:mi:ss') ACT_SESS, 
    NULL CPUS_USED, 
    NULL "T_MS/USERC.", 
    NULL "READS/USERC.", 
    NULL "HITRATIO", 
    NULL "LOG. READS/S", 
    NULL "DISK READS/S", 
    NULL "USER CALLS/S", 
    NULL "COMMITS/S", 
    NULL "REDO BLKS/S" 
  FROM 
    SNAPSHOTS
)
UNION ALL
( SELECT 
    'INSTANCE:' BEGIN_INTERVAL_TIME, 
    DECODE(INSTANCE_NUMBER, -2, 'ALL', TO_CHAR(INSTANCE_NUMBER)) SECONDS, 
    NULL ACT_SESS, 
    NULL CPUS_USED, 
    NULL "T_MS/USERC.", 
    NULL "READS/USERC.", 
    NULL "HITRATIO", 
    NULL "LOG. READS/S", 
    NULL "DISK READS/S", 
    NULL "USER CALLS/S", 
    NULL "COMMITS/S", 
    NULL "REDO BLKS/S" 
  FROM 
    BASIS_INFO
)
UNION ALL
( SELECT 
    'AGGREGATION BY:' BEGIN_INTERVAL_TIME, 
    AGGREGATE_BY SECONDS, 
    NULL ACT_SESS, 
    NULL CPUS_USED, 
    NULL "T_MS/USERC.", 
    NULL "READS/USERC.", 
    NULL "HITRATIO", 
    NULL "LOG. READS/S", 
    NULL "DISK READS/S", 
    NULL "USER CALLS/S", 
    NULL "COMMITS/S", 
    NULL "REDO BLKS/S" 
  FROM 
    BASIS_INFO
)
UNION ALL
( SELECT 
    'WEEKENDS EXCLUDED:' BEGIN_INTERVAL_TIME, 
    DECODE(EXCLUDE_WEEKENDS, 'X', 'YES', 'NO') SECONDS, 
    NULL ACT_SESS, 
    NULL CPUS_USED, 
    NULL "T_MS/USERC.", 
    NULL "READS/USERC.", 
    NULL "HITRATIO", 
    NULL "LOG. READS/S", 
    NULL "DISK READS/S", 
    NULL "USER CALLS/S", 
    NULL "COMMITS/S", 
    NULL "REDO BLKS/S" 
  FROM 
    BASIS_INFO
)
UNION ALL
( SELECT 
    NULL BEGIN_INTERVAL_TIME, 
    NULL SECONDS, 
    NULL ACT_SESS, 
    NULL CPUS_USED, 
    NULL "T_MS/USERC.", 
    NULL "READS/USERC.", 
    NULL "HITRATIO", 
    NULL "LOG. READS/S", 
    NULL "DISK READS/S", 
    NULL "USER CALLS/S", 
    NULL "COMMITS/S", 
    NULL "REDO BLKS/S" 
  FROM 
    DUAL
)
UNION ALL
( SELECT 
    * 
  FROM
  ( SELECT
      BEGIN_TIME,
      TO_CHAR(SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER)), 9999999990) SECONDS, 
      TO_CHAR(SUM(DB_TIME_US) / 1000000 / (SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER))), 999990.99) ACT_SESS,
      TO_CHAR(SUM(CPU_TIME_US) / 1000000 / (SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER))), 99990.99) CPUS_USED,
      TO_CHAR(SUM(DB_TIME_US) / 1000 / SUM(USER_CALLS), 9999990.99)
        "T_MS/USERC.",
      TO_CHAR(SUM(READS) / SUM(USER_CALLS), 99999990.99)
        "READS/USERC.",
      TO_CHAR((SUM(READS) - SUM(PHYSICAL_READS_CACHE)) / SUM(READS) * 100, 9990.99) 
        "HITRATIO",
      TO_CHAR(SUM(READS) / (SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER))), 99999990.99) 
      "LOG. READS/S",
      TO_CHAR(SUM(PHYSICAL_READS) / (SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER))), 99999990.99) 
      "DISK READS/S",
      TO_CHAR(SUM(USER_CALLS) / (SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER))), 99999990.99) 
      "USER CALLS/S",
      TO_CHAR(SUM(COMMITS) / (SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER))), 99990.99) "COMMITS/S",
      TO_CHAR(SUM(REDO_BLOCKS) / (SUM(SECONDS) / COUNT(DISTINCT(INSTANCE_NUMBER))), 9999990.99) 
      "REDO BLKS/S"
    FROM
    ( SELECT
        DECODE(BI.AGGREGATE_BY,
          'SNAPSHOT', TO_CHAR(K.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS'),
          'DAY', TO_CHAR(K.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD (DY)'),
          'HOUR_OF_DAY', TO_CHAR(K.BEGIN_INTERVAL_TIME, 'HH24') || ':00',
          'INSTANCE', 'Instance: ' || TO_CHAR(K.INSTANCE_NUMBER),
          'SS_INST', TO_CHAR(K.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS') ||
             ' (Instance: ' || TO_CHAR(K.INSTANCE_NUMBER) || ')',
          'DAY_INST', TO_CHAR(K.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD (DY)') ||
             ' (Instance: ' || TO_CHAR(K.INSTANCE_NUMBER) || ')',
          'HOD_INST', TO_CHAR(K.BEGIN_INTERVAL_TIME, 'HH24') || ':00' ||
             ' (Instance: ' || TO_CHAR(K.INSTANCE_NUMBER) || ')') BEGIN_TIME,
        K.*,
        BI.SORT_ORDER,
        BI.AGGREGATE_BY
      FROM
        BASIS_INFO BI,
        KEYFIG_PER_INTERVAL K
      WHERE
        K.PREV_SNAP_ID IS NOT NULL AND
        ( BI.EXCLUDE_WEEKENDS = ' ' OR 
          TO_CHAR(K.BEGIN_INTERVAL_TIME, 'D') NOT IN (7, 1) )
    )
    GROUP BY
      BEGIN_TIME,
      SORT_ORDER,
      AGGREGATE_BY
    ORDER BY
      DECODE(SORT_ORDER, 
        'ASC', MIN(BEGIN_TIME), SYSDATE),
      DECODE(SORT_ORDER, 
        'DESC', MIN(BEGIN_TIME), SYSDATE) DESC
  )
)
));