You have been requested to check how much a DB is generating on archive logs, it can be for FRA size planing, standby lag analysis…. you name it.
set linesize 190
col 00h format 999
col 01h format 999
col 02h format 999
col 03h format 999
col 04h format 999
col 05h format 999
col 06h format 999
col 07h format 999
col 08h format 999
col 09h format 999
col 10h format 999
col 11h format 999
col 12h format 999
col 14h format 999
col 13h format 999
col 15h format 999
col 16h format 999
col 17h format 999
col 18h format 999
col 19h format 999
col 20h format 999
col 21h format 999
col 22h format 999
col 23h format 999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '999') "00h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '999') "01h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '999') "02h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '999') "03h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '999') "04h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '999') "05h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '999') "06h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '999') "07h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '999') "08h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '999') "09h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '999') "10h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '999') "11h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '999') "12h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '999') "13h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '999') "14h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '999') "15h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '999') "16h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '999') "17h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '999') "18h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '999') "19h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '999') "20h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '999') "21h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '999') "22h"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '999') "23h"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 10;
DAY 00h 01h 02h 03h 04h 05h 06h 07h 08h 09h 10h 11h 12h 13h 14h 15h 16h 17h 18h 19h 20h 21h 22h 23h
----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01/05 0 18 21 33 0 0 0 3 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0
30/04 1 16 19 35 0 0 0 1 0 0 2 0 1 4 4 0 0 0 0 1 0 4 0 4
29/04 0 14 19 36 3 0 0 0 0 2 2 0 1 1 4 2 1 3 0 3 2 4 0 4
28/04 1 13 22 43 0 0 0 3 4 0 2 2 1 1 6 0 1 1 0 3 1 2 0 4
27/04 0 13 22 49 0 0 0 0 1 3 3 1 3 1 3 1 1 3 0 2 2 1 0 4
26/04 1 18 20 45 5 0 0 0 1 0 1 0 0 0 1 4 0 0 0 4 0 0 0 4
25/04 0 13 21 44 5 0 0 0 0 0 0 1 0 1 4 0 0 0 4 0 0 0 0 4
24/04 0 15 20 54 12 0 0 0 1 0 0 0 0 2 4 0 0 0 0 0 4 0 0 4
23/04 0 14 19 43 1 0 0 2 1 0 1 2 3 0 4 0 0 0 4 0 0 0 0 4
9 rows selected.
SQL>
We can see here DB has the higher load during 1am to 3am.
Here you can get the size it is generating per day:
SQL> select trunc(COMPLETION_TIME,'DD') Day,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated
from v$archived_log
WHERE TRUNC(completion_time) BETWEEN TRUNC(SYSDATE-10) AND TRUNC(SYSDATE)
group by trunc(COMPLETION_TIME,'DD')order by 1;
DAY GB ARCHIVES_GENERATED
--------- ---------- ------------------
21-APR-20 401 236
22-APR-20 364 210
23-APR-20 359 196
24-APR-20 377 232
25-APR-20 330 194
26-APR-20 338 208
27-APR-20 444 226
28-APR-20 396 220
29-APR-20 383 202
30-APR-20 317 184
01-MAY-20 300 162
11 rows selected.
SQL>
Comments