oracle官方推荐redo log 20分钟左右自动切换一次
Checkpoint not complete 提案改善—redo log切换太频繁
现状:
Redo log三组,每组100M(每个node三个Redo log)
高峰时段六分钟切换一次redo log
影响:可能导致所有DML/DDL操作的hang住
可能的解决方法:
1. 设置archive_lag_target为0(两套生产库为360):
两套生产库都设置了参数archive_lag_target(=360,也即6分钟),其它DB默认为0
SQL> show parameter target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 360
2.提高I/O性能
增加db_writer_processes(1 or CPU_COUNT / 8, whichever is greater,已经调到最高)
合理规划DBF和redo log(目前的瓶颈在于DBWR,可以考虑把一个tablespace的不同disk)
3.增加redo log group数目
4.增加每个redo log member的大小(后两个方法安全,容易见效,副作用小)
建议改善目标:
Checkpoint not complete错误不再出现
Redo log每15-20分钟切换一次(metalink建议20分钟)
参考文献:
Metalink:
Note.435780.1 Checkpoint Not Complete In Alert.log Due To Setting Of Archive_lag_target
Note. 147468.1 Checkpoint Tuning and Troubleshooting Guide
Oracle document:
<>p73,table2-1
<>p164-167
>>>高峰时段六分钟切换一次redo log
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",
ROUND (COUNT (1) / 24, 2) "Avg",
COUNT(1)*100 "Redo size"
FROM gv$log_history
WHERE THREAD# = inst_id
GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
ORDER BY 1;
结果:
SQL>
Date Day Total h0 ... h23 Avg Redo size
----------- --------- ---------- ---------- ----...----- ---------- ----------
2010-9-6 Mon 18 0 ... 15 0.75 1800
2010-9-7 Tue 371 15 ... 15 15.46 37100
2010-9-8 Wed 388 14 ... 15 16.17 38800
2010-9-9 Thu 383 16 ... 14 15.96 38300
2010-9-10 Fri 373 14 ... 15 15.54 37300
2010-9-11 Sat 360 15 ... 14 15 36000
2010-9-12 Sun 293 12 ... 11 12.21 29300
2010-9-13 Mon 316 10 ... 14 13.17 31600
2010-9-14 Tue 224 15 ... 0 9.33 22400
...
9 rows selected
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23490498/viewspace-676939/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23490498/viewspace-676939/