ORACLE数据库REDO日志切换频率在一定程度上可以反映业务的繁忙程度,正常来讲REDO日志每小时切换3次左右为最佳，当然不同的业务不同的需求，不能同日而语。

下面是网上流传较广的查询日志切换频率的SQL，与其说是查询日志切换频率，不如说是查询日志切换周期。

SQL> select sequence#,
  2         first_time,
  3         nexttime,
       round(((first_time - nexttime) * 24) * 60, 2) diff
  4    5    from (select sequence#,
  6                 first_time,
  7                 lag(first_time) over(order by sequence#) nexttime
          from v$log_history
  8    9           where thread# = 1)
 10   order by sequence# desc;

 SEQUENCE# FIRST_TIME          NEXTTIME                  DIFF
---------- ------------------- ------------------- ----------
       229 2015-06-02 22:14:41 2015-06-02 22:00:04      14.62
       228 2015-06-02 22:00:04 2015-06-02 12:00:13     599.85
       227 2015-06-02 12:00:13 2015-06-01 22:13:36     826.62
       226 2015-06-01 22:13:36 2015-06-01 22:00:17      13.32
       225 2015-06-01 22:00:17 2015-06-01 22:00:07        .17
       224 2015-06-01 22:00:07 2015-06-01 12:00:07        600
       223 2015-06-01 12:00:07 2015-05-31 22:12:30     827.62
       222 2015-05-31 22:12:30 2015-05-31 22:02:29      10.02
       221 2015-05-31 22:02:29 2015-05-31 16:00:13     362.27
       220 2015-05-31 16:00:13 2015-05-31 06:00:13        600
       219 2015-05-31 06:00:13 2015-05-30 22:11:24     468.82
       218 2015-05-30 22:11:24 2015-05-30 22:01:26       9.97
       217 2015-05-30 22:01:26 2015-05-30 18:00:12     241.23
       216 2015-05-30 18:00:12 2015-05-30 09:00:48      539.4
       215 2015-05-30 09:00:48 2015-05-30 05:00:37     240.18
       214 2015-05-30 05:00:37 2015-05-29 22:10:15     410.37
       213 2015-05-29 22:10:15 2015-05-29 22:00:04      10.18

这种查询方式查询的结果是日志切换的时间间隔，也就是两个相邻的日志切换的时间间隔，如果说是日志切换频率，很不直观。

在看下比较直观展现日志切换频率的SQL，这种方式目前网上基本还没有人分享。

SELECT TO_CHAR(first_time, 'MM/DD') DAY,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
       SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
       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,
       COUNT(*) TOTAL
  FROM (SELECT ROWNUM RN, FIRST_TIME
          FROM V$LOG_HISTORY
         WHERE first_time > sysdate - 18
           and FIRST_TIME > ADD_MONTHS(SYSDATE, -1)
         ORDER BY FIRST_TIME)
 GROUP BY TO_CHAR(first_time, 'MM/DD')
 ORDER BY MIN(RN);

以下是SQL的查询结果，以查询18天之前的日志切换频率为例。

这种方式是查询每小时日志切换的次数，很直观的展示日志切换频率。如果在不同日期的同一时间，发现有很不协调的切换次数，就需要关注了。

本文固定链接: http://www.dbdream.com.cn/2015/06/oracle%e6%97%a5%e5%bf%97%e5%88%87%e6%8d%a2%e9%a2%91%e7%8e%87/ | 信春哥，系统稳，闭眼上线不回滚！