检测数据库日志的切换频率及归档文件大小的sql

DBA的日常功能SQL之一，绝对原创。

查看数据库的日志切换频率及生成速度是DBA的日常工作之一，通过观察相关信息可以调整online redo 的大小及切换频率。

非归档模式主要是查询 v$log_history

归档模式主要是查询 v$archived_log，也可以查询   v$log_history ， 只是v$archived_log.completion_time 和 v$log_history.first_time 在时间上group by时稍有差异。

非归档模式

-- 日志切换频率
-- v$log_history
SELECT 
    trunc(first_time) "Date",
      to_char(first_time, 'Dy') "Day",
      THREAD#,
      count(1) "Total",
      SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0_1",
      SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1_2",
      SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2_3",
      SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3_4",
      SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4_5",
      SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5_6",
      SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6_7",
      SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7_8",
      SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8_9",
      SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9_10",
      SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10_11",
      SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11_12",
      SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12_13",
      SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13_14",
      SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14_15",
      SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15_16",
      SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16_17",
      SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17_18",
      SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18_19",
      SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19_20",
      SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20_21",
      SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21_22",
      SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22_23",
      SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23_24"
FROM v$log_history
group by trunc(first_time), to_char(first_time, 'Dy'),THREAD#
Order by 1 desc,3 
; 


归档模式

--归档生成频率
-- v$archived_log
SELECT 
      trunc(completion_time) "Date",
      to_char(completion_time, 'Dy') "Day",
      THREAD#,
      dest_id,
      count(1) "Total",
      SUM(decode(to_char(completion_time, 'hh24'),'00',1,0)) "h0_1",
      SUM(decode(to_char(completion_time, 'hh24'),'01',1,0)) "h1_2",
      SUM(decode(to_char(completion_time, 'hh24'),'02',1,0)) "h2_3",
      SUM(decode(to_char(completion_time, 'hh24'),'03',1,0)) "h3_4",
      SUM(decode(to_char(completion_time, 'hh24'),'04',1,0)) "h4_5",
      SUM(decode(to_char(completion_time, 'hh24'),'05',1,0)) "h5_6",
      SUM(decode(to_char(completion_time, 'hh24'),'06',1,0)) "h6_7",
      SUM(decode(to_char(completion_time, 'hh24'),'07',1,0)) "h7_8",
      SUM(decode(to_char(completion_time, 'hh24'),'08',1,0)) "h8_9",
      SUM(decode(to_char(completion_time, 'hh24'),'09',1,0)) "h9_10",
      SUM(decode(to_char(completion_time, 'hh24'),'10',1,0)) "h10_11",
      SUM(decode(to_char(completion_time, 'hh24'),'11',1,0)) "h11_12",
      SUM(decode(to_char(completion_time, 'hh24'),'12',1,0)) "h12_13",
      SUM(decode(to_char(completion_time, 'hh24'),'13',1,0)) "h13_14",
      SUM(decode(to_char(completion_time, 'hh24'),'14',1,0)) "h14_15",
      SUM(decode(to_char(completion_time, 'hh24'),'15',1,0)) "h15_16",
      SUM(decode(to_char(completion_time, 'hh24'),'16',1,0)) "h16_17",
      SUM(decode(to_char(completion_time, 'hh24'),'17',1,0)) "h17_18",
      SUM(decode(to_char(completion_time, 'hh24'),'18',1,0)) "h18_19",
      SUM(decode(to_char(completion_time, 'hh24'),'19',1,0)) "h19_20",
      SUM(decode(to_char(completion_time, 'hh24'),'20',1,0)) "h20_21",
      SUM(decode(to_char(completion_time, 'hh24'),'21',1,0)) "h21_22",
      SUM(decode(to_char(completion_time, 'hh24'),'22',1,0)) "h22_23",
      SUM(decode(to_char(completion_time, 'hh24'),'23',1,0)) "h23_24"
FROM v$archived_log
group by trunc(completion_time), 
         to_char(completion_time, 'Dy'),
         THREAD#,
         dest_id
Order by 1 desc,3,4 
; 


 


----归档生成大小
-- v$archived_log
SELECT 
      trunc(completion_time) "Date",
      to_char(completion_time, 'Dy') "Day",
      THREAD#,
      dest_id,
      round(sum(blocks*block_size)/1024/1024) "Total_mb",
      round(SUM(decode(to_char(completion_time, 'hh24'),'00',blocks*block_size,0))/1024/1024) h0_1_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'01',blocks*block_size,0))/1024/1024) h1_2_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'02',blocks*block_size,0))/1024/1024) h2_3_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'03',blocks*block_size,0))/1024/1024) h3_4_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'04',blocks*block_size,0))/1024/1024) h4_5_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'05',blocks*block_size,0))/1024/1024) h5_6_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'06',blocks*block_size,0))/1024/1024) h6_7_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'07',blocks*block_size,0))/1024/1024) h7_8_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'08',blocks*block_size,0))/1024/1024) h8_9_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'09',blocks*block_size,0))/1024/1024) h9_10_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'10',blocks*block_size,0))/1024/1024) h10_11_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'11',blocks*block_size,0))/1024/1024) h11_12_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'12',blocks*block_size,0))/1024/1024) h12_13_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'13',blocks*block_size,0))/1024/1024) h13_14_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'14',blocks*block_size,0))/1024/1024) h14_15_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'15',blocks*block_size,0))/1024/1024) h15_16_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'16',blocks*block_size,0))/1024/1024) h16_17_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'17',blocks*block_size,0))/1024/1024) h17_18_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'18',blocks*block_size,0))/1024/1024) h18_19_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'19',blocks*block_size,0))/1024/1024) h19_20_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'20',blocks*block_size,0))/1024/1024) h20_21_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'21',blocks*block_size,0))/1024/1024) h21_22_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'22',blocks*block_size,0))/1024/1024) h22_23_mb,
      round(SUM(decode(to_char(completion_time, 'hh24'),'23',blocks*block_size,0))/1024/1024) h23_24_mb
FROM v$archived_log
group by trunc(completion_time), 
         to_char(completion_time, 'Dy'),
         THREAD#,
         dest_id
Order by 1 desc,3,4
;