A database in ARCHIVELOG mode copies redo log entries in the achive log files. Archive log number and size is a metric of a database transactions load in time. Use the following query to monitor the Archive log production per hour for the last 30 days:
SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",<br>COUNT (1) "Total",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23"<br>FROM v$log_history<br>GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')<br>ORDER BY 1;
OR the following to get size approach:
SELECT TO_CHAR(FIRST_TIME,'MM-DD') DAY,<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',MB,0))) AS "00",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',MB,0))) AS "01",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',MB,0))) AS "02",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',MB,0))) AS "03",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',MB,0))) AS "04",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',MB,0))) AS "05",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',MB,0))) AS "06",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',MB,0))) AS "07",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',MB,0))) AS "08",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',MB,0))) AS "09",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',MB,0))) AS "10",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',MB,0))) AS "11",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',MB,0))) AS "12",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',MB,0))) AS "13",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',MB,0))) AS "14",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',MB,0))) AS "15",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',MB,0))) AS "16",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',MB,0))) AS "17",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',MB,0))) AS "18",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',MB,0))) AS "19",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',MB,0))) AS "20",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',MB,0))) AS "21",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',MB,0))) AS "22",<br>ROUND(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',MB,0))) AS "23",<br>ROUND(SUM(MB)) as "Tot"<br>FROM (SELECT DISTINCT SEQUENCE#, (BLOCKS*BLOCK_SIZE)/1048576 mb, FIRST_TIME FROM V$ARCHIVED_LOG)<br>GROUP BY TO_CHAR(FIRST_TIME,'MM-DD')<br>ORDER BY 1;
In case you need to check this production for a specific time window, this can be rewritten as:
SELECT SUM (BLOCKS*BLOCK_SIZE)/1048576 MB, TRUNC(FIRST_TIME) FROM V$ARCHIVED_LOG WHERE FIRST_TIME BETWEEN TO_DATE('20/12/2021 16:00','DD/MM/YYYY HH24:MI') and to_date('20/12/2021 19:00','DD/MM/YYYY HH24:MI')<br>GROUP BY TRUNC(FIRST_TIME);