Redo Log 발생량 분석

SELECT TO_CHAR(h.first_time,'YYYY-MM-DD') day
     , h.thread#
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'00',1,0)),'999') "00"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'01',1,0)),'999') "01"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'02',1,0)),'999') "02"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'03',1,0)),'999') "03"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'04',1,0)),'999') "04"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'05',1,0)),'999') "05"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'06',1,0)),'999') "06"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'07',1,0)),'999') "07"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'08',1,0)),'999') "08"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'09',1,0)),'999') "09"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'10',1,0)),'999') "10"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'11',1,0)),'999') "11"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'12',1,0)),'999') "12"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'13',1,0)),'999') "13"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'14',1,0)),'999') "14"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'15',1,0)),'999') "15"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'16',1,0)),'999') "16"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'17',1,0)),'999') "17"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'18',1,0)),'999') "18"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'19',1,0)),'999') "19"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'20',1,0)),'999') "20"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'21',1,0)),'999') "21"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'22',1,0)),'999') "22"
     , TO_CHAR(SUM(DECODE(TO_CHAR(h.first_time,'HH24'),'23',1,0)),'999') "23"
     , COUNT(*) "LOG_SWITCHES"
     , TO_CHAR(ROUND(SUM((blocks * block_size)/(1024*1024*1024)),2)) "LOG_SIZE(GB)"
FROM v$log_history h,v$archived_log a
WHERE h.sequence# = a.sequence# (+)
AND h.thread# = a.thread# (+)
--###############################
-- RAC : 1,2 호기 함께보기 & 합계
--###############################
--AND h.thread# in (1,2)
--GROUP BY ROLLUP (h.thread#),(TO_CHAR(h.first_time,'YYYY-MM-DD'))
------------------------------------------------------------------------------------
AND h.thread# = 1
GROUP BY h.thread#,TO_CHAR(h.first_time,'YYYY-MM-DD')
ORDER BY day
;