ITPub博客

首页 > 数据库 > Oracle > [20150911]关于增量检查点.txt

[20150911]关于增量检查点.txt

Oracle 作者:lfree 时间:2015-09-11 11:21:02 0 删除 编辑

[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
NAME                             DESCRIPTION                                           DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
-------------------------------- ----------------------------------------------------- -------------- -------------- ----------------------
_disable_selftune_checkpointing  Disable self-tune checkpointing                       TRUE           FALSE          FALSE

SCOTT@test>  @hide log_checkpoint_timeout
NAME                             DESCRIPTION                                           DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
-------------------------------- ----------------------------------------------------- -------------- -------------- ----------------------
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
Fri Sep 11 11:24:24 2015
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,可以发现它完全不受其它因素的影响。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1797103/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2471
  • 访问量
    6279436