[20150911]關于增量檢查點.txt

[20150911]關于增量檢查點.txt

--昨天被人問及我們的測試庫，增量檢查點的問題，我告訴對方，我設定了參數log_checkpoints_to_alert=true，當寫增量檢查點時會

--同時寫入alert*.log日志檔案，被問及時間間隔問題，實際上增量檢查點的寫入與許多因素相關，比如如果資料庫修改很多，資料緩存

--大小，redo file的大小以及數量都存在關系。

--但是我的測試庫基本我自己用，沒事看看增量檢查點的間隔是多少，會與什麼參數有關。

$ grep -B1 "^Incremental checkpoint up to RBA" /u01/app/oracle11g/diag/rdbms/test/test/trace/alert_test.log | grep ' 2015' > /tmp/aa.txt

--使用vim編輯，删除開頭4個字元。

:%s/^....//

SCOTT@test> create table t ( d date);

Table created.

--選擇合适的日期格式。

SCOTT@test> alter session set NLS_DATE_FORMAT='Mon dd hh24:mi:ss YYYY';

Session altered.

SCOTT@test> select sysdate from dual ;

SYSDATE

-----------------------

Sep 11 10:25:26 2015

--修改/tmp/aa.txt檔案加入如下：

LOAD DATA

INFILE *

BADFILE './a.BAD'

DISCARDFILE './a.DSC'

APPEND INTO TABLE SCOTT.T

Fields terminated by ";" Optionally enclosed by '"'

(

  D DATE "Mon DD HH24:MI:SS YYYY" NULLIF (D="NULL")

)

BEGINDATA

--執行如下導入":

$ sqlldr scott/btbtms control=/tmp/aa.txt

SELECT *

  FROM (  SELECT TRUNC ( (d1 - d) * 86400) d2, COUNT (*)

            FROM (  SELECT d, LEAD (d, 1) OVER (ORDER BY d) d1

                      FROM t

                  ORDER BY d)

        GROUP BY TRUNC ( (d1 - d) * 86400)

        ORDER BY 2 DESC)

WHERE ROWNUM <= 10;

        D2   COUNT(*)

---------- ----------

      1830       3082

      1826       1587

      1829        500

      1832        113

      1827         60

      1825         46

      1833         42

      1834         21

      1836          3

      3665          2

10 rows selected.

--可以确定大約1830秒會發出一個增量檢查點。

SCOTT@test> @hide _dbwr_scan_interval

NAME                             DESCRIPTION                                           DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE

-------------------------------- ----------------------------------------------------- -------------- -------------- ----------------------

_dbwr_scan_interval              dbwriter scan interval                                TRUE           300            300

SCOTT@test> @hide _disable_selftune_checkpointing

_disable_selftune_checkpointing  Disable self-tune checkpointing                       TRUE           FALSE          FALSE

SCOTT@test>  @hide log_checkpoint_timeout

log_checkpoint_timeout           Maximum time interval between checkpoints in seconds  TRUE           1800           1800

--可以發現對于我的測試庫受這個參數log_checkpoint_timeout控制(因為沒有什麼業務)。不知道是否意味着寫出需要30秒，還是我的測

--試機器磁盤IO不行(不是存儲)？

$ strings spfiletest.ora | grep checkpoint

*.log_checkpoints_to_alert=TRUE

--很明顯這個是預設參數，修改看看：

SCOTT@test> alter system set log_checkpoint_timeout=60 scope=memory;

System altered.

--觀察alert的輸出情況：

Fri Sep 11 11:13:37 2015

Incremental checkpoint up to RBA [0xe09.12332.0], current log tail at RBA [0xe09.12460.0]

Fri Sep 11 11:14:38 2015

Incremental checkpoint up to RBA [0xe09.12468.0], current log tail at RBA [0xe09.124a5.0]

Fri Sep 11 11:15:39 2015

Incremental checkpoint up to RBA [0xe09.124aa.0], current log tail at RBA [0xe09.125b8.0]

Fri Sep 11 11:16:40 2015

Incremental checkpoint up to RBA [0xe09.125bd.0], current log tail at RBA [0xe09.12601.0]

Fri Sep 11 11:17:40 2015

Incremental checkpoint up to RBA [0xe09.12606.0], current log tail at RBA [0xe09.1265e.0]

Fri Sep 11 11:18:41 2015

Incremental checkpoint up to RBA [0xe09.12662.0], current log tail at RBA [0xe09.126ac.0]

Fri Sep 11 11:19:42 2015

Incremental checkpoint up to RBA [0xe09.126af.0], current log tail at RBA [0xe09.126f1.0]

Fri Sep 11 11:20:43 2015

Incremental checkpoint up to RBA [0xe09.126f4.0], current log tail at RBA [0xe09.12738.0]

Fri Sep 11 11:21:44 2015

Incremental checkpoint up to RBA [0xe09.12739.0], current log tail at RBA [0xe09.1277e.0]

Fri Sep 11 11:22:45 2015

Incremental checkpoint up to RBA [0xe09.1277f.0], current log tail at RBA [0xe09.127f1.0]

Fri Sep 11 11:23:31 2015

Beginning global checkpoint up to RBA [0xe09.1281c.10], SCN: 13200881205

Completed checkpoint up to RBA [0xe09.1281c.10], SCN: 13200881205

Fri Sep 11 11:23:46 2015

Incremental checkpoint up to RBA [0xe09.1281d.0], current log tail at RBA [0xe09.1283a.0]

Fri Sep 11 11:24:09 2015

Beginning global checkpoint up to RBA [0xe09.12851.10], SCN: 13200881256

Completed checkpoint up to RBA [0xe09.12851.10], SCN: 13200881256

Fri Sep 11 11:24:23 2015

Beginning log switch checkpoint up to RBA [0xe0a.2.10], SCN: 13200881278

Thread 1 advanced to log sequence 3594 (LGWR switch)

  Current log# 1 seq# 3594 mem# 0: /u01/app/oracle11g/oradata/test/redo01.log

Fri Sep 11 11:24:24 2015

LNS: Standby redo logfile selected for thread 1 sequence 3594 for destination LOG_ARCHIVE_DEST_2

Archived Log entry 5321 added for thread 1 sequence 3593 ID 0x806ffa4c dest 1:

Fri Sep 11 11:24:47 2015

Incremental checkpoint up to RBA [0xe09.12851.0], current log tail at RBA [0xe0a.f0.0]

--可以發現間隔僅僅60+1秒。另外我中間執行2次full checkpoint，可以發現它完全不受其它因素的影響。