SQL> SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME)
ORDER BY 1;
ARCHIVED_DATE SIZE_IN_MB
------------- ----------
2020-02-14 00:00:00 2406.45117
2020-02-15 00:00:00 3491.56445
2020-02-16 00:00:00 3196.87354
2020-02-17 00:00:00 2917.70605
2020-02-18 00:00:00 2798.62158
2020-02-19 00:00:00 2650.23242
Size of the archive log files each hour.
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';
Session altered.
SQL> SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,
SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME, 'HH')
ORDER BY 1;
ARCHIVED_DATE SIZE_IN_MB
------------- ----------
2020-01-21 02 13304.7856
Size of the archive log files each day per instance
SQL> SELECT
TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
THREAD#,
SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME), THREAD#
ORDER BY 1, 2;
ARCHIVED_ THREAD# SIZE_IN_MB
--------- ---------- ----------
26-JAN-20 1 148411.591
Online Redo Log Switch Frequency Map Query
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) "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 -7
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;
