How to check log switches per hour for the last 7 days in Oracle

Count log switches per hour per RAC instance for the last 7 days. Each row is date + instance; columns h0..h23 show the switch count for each hour, plus a total and 24-hour average. Great for redo sizing on a RAC.

SET PAGES 999 LINES 400
COL h0  FORMAT 999
COL h1  FORMAT 999
COL h2  FORMAT 999
COL h3  FORMAT 999
COL h4  FORMAT 999
COL h5  FORMAT 999
COL h6  FORMAT 999
COL h7  FORMAT 999
COL h8  FORMAT 999
COL h9  FORMAT 999
COL h10 FORMAT 999
COL h11 FORMAT 999
COL h12 FORMAT 999
COL h13 FORMAT 999
COL h14 FORMAT 999
COL h15 FORMAT 999
COL h16 FORMAT 999
COL h17 FORMAT 999
COL h18 FORMAT 999
COL h19 FORMAT 999
COL h20 FORMAT 999
COL h21 FORMAT 999
COL h22 FORMAT 999
COL h23 FORMAT 999

SELECT TRUNC(first_time) AS "Date",
       inst_id,
       TO_CHAR(first_time, 'Dy') AS "Day",
       COUNT(1) AS "Total",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) AS "h0",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) AS "h1",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) AS "h2",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) AS "h3",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) AS "h4",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) AS "h5",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) AS "h6",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) AS "h7",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) AS "h8",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) AS "h9",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) AS "h10",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) AS "h11",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) AS "h12",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) AS "h13",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) AS "h14",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) AS "h15",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) AS "h16",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) AS "h17",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) AS "h18",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) AS "h19",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) AS "h20",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) AS "h21",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) AS "h22",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) AS "h23",
       ROUND(COUNT(1) / 24, 2) AS "Avg"
FROM   gv$log_history
WHERE  thread#    = inst_id
AND    first_time > SYSDATE - 7
GROUP BY TRUNC(first_time), inst_id, TO_CHAR(first_time, 'Dy')
ORDER BY 1, 2;