[20140612]确定每天产生的redo大小.txt

column h0 format 999
column h1 format 999
column h2 format 999
column h3 format 999
column h4 format 999
column h5 format 999
column h6 format 999
column h7 format 999
column h8 format 999
column h9 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column avg format 999.99
column day format a6

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 first_time >= trunc(SYSDATE) - 10
   and thread# = inst_id
GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
ORDER BY 1 DESC;

--但是这个仅仅确定产生的redo数量，并不是redo大小。自己随手写的一个。

  SELECT TRUNC (first_time), SUM (blocks * block_size)
    FROM (SELECT DISTINCT first_change#,
                          first_time,
                          blocks,
                          block_size,completion_time
            FROM v$archived_log
           WHERE dest_id = 1)
GROUP BY TRUNC (first_time)
ORDER BY TRUNC (first_time);