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
;