oracle日志切换频率多大,ORACLE日志切换频率 | 信春哥，系统稳，闭眼上线不回滚！...

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天之前的日志切换频率为例。

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