pat98's always & forever

- 일자별 log switch 개수

select v2.DAY "DATE(Recent 15 Days)" , NVL(LOG_CNT,0) "Switch Count"

from

        (select to_char(first_time,'YYYY-MM-DD') DAY,count(*) LOG_CNT

         from V$LOG_HISTORY

         group by to_char(first_time,'YYYY-MM-DD')) v1,

        (select to_char((sysdate - 15),'YYYY-MM-DD') DAY from dual   union all    

         select to_char((sysdate - 14),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 13),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 12),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 11),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 10),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  9),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  8),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  7),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  6),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  5),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  4),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  3),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  2),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  1),'YYYY-MM-DD') DAY from dual) v2

where v1.DAY(+) = v2.DAY 

order by 1;

- 시간별 log switch 개수

select v2.Time || ' H' Time , NVL(LOG_CNT,0) SwitchCount

from (select to_char(first_time,'HH24') Time,count(*) LOG_CNT

          from V$LOG_HISTORY

         group by to_char(first_time,'HH24'))  v1,

        (select '00' Time from dual   union all    select '01' Time from dual  union all         

         select '02' Time from dual   union all    select '03' Time from dual  union all

         select '04' Time from dual   union all    select '05' Time from dual  union all

         select '06' Time from dual   union all    select '07' Time from dual  union all

         select '08' Time from dual   union all    select '09' Time from dual  union all

         select '10' Time from dual   union all    select '11' Time from dual  union all

         select '12' Time from dual   union all    select '13' Time from dual  union all

         select '14' Time from dual   union all    select '15' Time from dual  union all

         select '16' Time from dual   union all    select '17' Time from dual  union all

         select '18' Time from dual   union all    select '19' Time from dual  union all

         select '20' Time from dual   union all    select '21' Time from dual  union all

         select '22' Time from dual   union all    select '23' Time from dual  union all

         select '24' Time from dual   ) v2

where v1.Time(+) = v2.Time  

order by 1;

- 일별 로그생성 사이즈 (15일간)

set linesize 120

col v2.DAY for A20

col LOG_SIZE for A15

select v2.DAY "DATE (for 15 Days)" , NVL(LOG_SIZE,'-') "Archived Size" 

from

        (select to_char(COMPLETION_TIME,'YYYY-MM-DD') DAY,

                to_char(round(sum((BLOCKS * BLOCK_SIZE)/(1024*1024)),1)) || ' MB' LOG_SIZE

         from v$archived_log

         group by to_char(COMPLETION_TIME,'YYYY-MM-DD')) v1,

        (select to_char((sysdate - 15),'YYYY-MM-DD') DAY from dual   union all    

         select to_char((sysdate - 14),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 13),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 12),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 11),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate - 10),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  9),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  8),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  7),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  6),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  5),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  4),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  3),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  2),'YYYY-MM-DD') DAY from dual   union all

         select to_char((sysdate -  1),'YYYY-MM-DD') DAY from dual) v2

where v1.DAY(+) = v2.DAY 

order by 1;

- 시간대별 로그생성 크기

select v2.Time || ' H'  Time , NVL(LOG_SIZE,'-') SwitchCount

from 

         (select to_char(COMPLETION_TIME,'HH24') Time,

                 to_char(round(sum((BLOCKS * BLOCK_SIZE)/(1024*1024)),1)) || ' MB' LOG_SIZE

         from v$archived_log

         group by to_char(COMPLETION_TIME,'HH24'))  v1,

        (select '00' Time from dual   union all    select '01' Time from dual  union all         

         select '02' Time from dual   union all    select '03' Time from dual  union all

         select '04' Time from dual   union all    select '05' Time from dual  union all

         select '06' Time from dual   union all    select '07' Time from dual  union all

         select '08' Time from dual   union all    select '09' Time from dual  union all

         select '10' Time from dual   union all    select '11' Time from dual  union all

         select '12' Time from dual   union all    select '13' Time from dual  union all

         select '14' Time from dual   union all    select '15' Time from dual  union all

         select '16' Time from dual   union all    select '17' Time from dual  union all

         select '18' Time from dual   union all    select '19' Time from dual  union all

         select '20' Time from dual   union all    select '21' Time from dual  union all

         select '22' Time from dual   union all    select '23' Time from dual  union all

         select '24' Time from dual   ) v2

where v1.Time(+) = v2.Time  

order by 1;

- 시간대별 archive 발생량 확인

alter session set nls_date_format = 'YYYY-MM-DD HH24';

SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,

            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB

       FROM V$ARCHIVED_LOG

      GROUP BY TRUNC(COMPLETION_TIME, 'HH')

      ORDER BY 1;

- 시간대별 archive 발생량 확인 (per instance별)

SELECT

            TRUNC(COMPLETION_TIME) ARCHIVED_DATE,

            THREAD#,

            SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB

       FROM V$ARCHIVED_LOG

      GROUP BY TRUNC(COMPLETION_TIME), THREAD#

      ORDER BY 1, 2;

- 일일 아카이브 발생량 및 삭제량

SELECT SUM_ARCH.DAY,

         SUM_ARCH.GENERATED_MB,

         SUM_ARCH_DEL.DELETED_MB,

         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"

    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,

                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024 * 1024), 2))

                      GENERATED_MB

              FROM V$ARCHIVED_LOG

             WHERE ARCHIVED = 'YES'

          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,

         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,

                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024 * 1024), 2))

                      DELETED_MB

              FROM V$ARCHIVED_LOG

             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'

          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL

   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)

ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

- 시간별 발생량 확인

set pages 1000 

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives from v$archived_log 

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;