SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL)) "00-01",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL)) "01-02",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL)) "02-03",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL)) "03-04",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL)) "04-05",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL)) "05-06",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL)) "06-07",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL)) "07-08",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL)) "08-09",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL)) "09-10",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL)) "10-11",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL)) "11-12",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL)) "12-13",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL)) "13-14",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL)) "14-15",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL)) "15-16",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL)) "16-17",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL)) "17-18",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL)) "18-19",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL)) "19-20",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL)) "20-21",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL)) "21-22",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL)) "22-23",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL)) "23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

-- ALTERNATIVE 2 =================================================== 

set verify off
set feed off
set timing off
PROMPT
PROMPT number of hourly redo switches for the last 31 days
set pages 1000 lines 1000
SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
COUNT (1) "Total",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate-31
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;

hostgator