Oracle Log Switch 발생량 확인 (오라클 로그스위치) DBA 쿼리

1. DBA 사용 쿼리 - DB Redo Log Switch 횟수 확인 SQL

안녕하세요! DBA 여러분 오늘은 DBA분들이 분석에 많이 사용하시는 redo log switch 횟수 확인하는 SQL 입니다.

DB 분석이나 report를 작성하실때... 혹은 성능의 지표로 사용 할수도 있습니다.

한달동안 발생된 siwtch 횟수를 한번에 확인 할수 있으니 저장해 두고 두고두고 쓰시면 됩니다.!!

SELECT TO_CHAR(first_time,'yyyy-mm-dd') AS day
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') AS "00"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') AS "01"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') AS "02"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') AS "03"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') AS "04"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') AS "05"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') AS "06"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') AS "07"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') AS "08"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') AS "09"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') AS "10"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') AS "11"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') AS "12"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') AS "13"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') AS "14"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') AS "15"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') AS "16"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') AS "17"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') AS "18"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') AS "19"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') AS "20"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') AS "21"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') AS "22"
      ,TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') AS "23"
      ,COUNT(*) AS "Day Sum"
FROM v$log_history
WHERE thread# in ('1','2')
AND TO_CHAR(first_time,'yyyymm')='202201'
GROUP BY TO_CHAR(first_time,'yyyy-mm-dd')
ORDER BY day DESC
/