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