通过归档日志量和次数，可以定位业务的高峰期

    统计过去七天内每天的归档日志量
    SELECT SUM(BLOCKS *BLOCK_SIZE )/1024/1024/1024 AS “Size(G)”,
    TRUNC(completion_time) completion_time
    FROM v$archived_log
    WHERE completion_time> sysdate -7
    AND dest_id=1
    GROUP BY TRUNC(completion_time)
    ORDER BY completion_time desc;

    统计过去七天内每小时归档日志切换次数
    SELECT TRUNC (first_time) “Date”, 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 between sysdate-7 and sysdate
    GROUP BY TRUNC (first_time), TO_CHAR (first_time, ‘Dy’)
    ORDER BY 1;