分享一个实用脚本--获取最近一个月redo 日志切换情况

概述

很多时候我们想分析数据库在哪个时间段日志切换比较频繁，是否存在异常，可以怎么看呢？之前分享了一个脚本是看每天归档日志的数据量，并没有具体时间段，下面分享一个脚本来查看具体时间段的redo日志切换情况

---

获取redo日志切换频率

获取redo日志信息：

获取redo 日志切换频率脚本如下：

alter session set nls_date_format='YYYY-MM-DD';
 
SELECT trunc(first_time) "Date",
 to_char(first_time, 'Dy') "Day",
 count(1) "Total",
 SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
 SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
 SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
 SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
 SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
 SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
 SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
 SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
 SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
 SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
 SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
 SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
 SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
 SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
 SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
 SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
 SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
 SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
 SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
 SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
 SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
 SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
 SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
 SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
FROM V$log_history
where trunc(first_time)>sysdate-30
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;

这里可以发现在4月7号下午发生了很多DML操作，日志切换频率比较多，因为那时候做的几次实验都是插入1亿条数据，所以表现比较明显。