oracle checkpoint作用释疑4

sql> commit;

Commit complete.

sql> SELECT cpdrt,
2 cplrba_seq || ‘.' || cplrba_bno || ‘.' || cplrba_bof “low_rba”,
3 cpodr_seq || ‘.' || cpodr_bno || ‘.' || cpodr_bof “on_disk_rba”
4 FROM x$kcccp;

CPDRT low_rba on_disk_rba
———- —————————— ——————————
26 11.1432.0 13.4.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0

8 ROWS selected.

sql> ALTER system switch logfile;

System altered.

sql> SELECT cpdrt,
2 cplrba_seq || ‘.' || cplrba_bno || ‘.' || cplrba_bof “low_rba”,
3 cpodr_seq || ‘.' || cpodr_bno || ‘.' || cpodr_bof “on_disk_rba”
4 FROM x$kcccp;

CPDRT low_rba on_disk_rba
———- —————————— ——————————
3 13.2.0 14.2.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0

8 ROWS selected.

sql> SELECT * FROM v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 13 52428800 1 NO ACTIVE 918247 07-NOV-11
2 1 14 52428800 1 NO CURRENT 918274 07-NOV-11
3 1 12 52428800 1 NO ACTIVE 918241 07-NOV-11

SQL>
SQL> SELECT cpdrt,
2 cplrba_seq || ‘.' || cplrba_bno || ‘.' || cplrba_bof “low_rba”,
3 cpodr_seq || ‘.' || cpodr_bno || ‘.' || cpodr_bof “on_disk_rba”
4 FROM x$kcccp;

CPDRT low_rba on_disk_rba
———- —————————— ——————————
3 13.2.0 14.2.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0

8 ROWS selected.

SQL> ALTER system switch logfile;

System altered.

SQL> SELECT cpdrt,
2 cplrba_seq || ‘.' || cplrba_bno || ‘.' || cplrba_bof “low_rba”,
3 cpodr_seq || ‘.' || cpodr_bno || ‘.' || cpodr_bof “on_disk_rba”
4 FROM x$kcccp;

CPDRT low_rba on_disk_rba
———- —————————— ——————————
0 4294967295.4294967295.65535 15.2.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0

8 ROWS selected.

SQL> SELECT cpdrt,
2 cplrba_seq || ‘.' || cplrba_bno || ‘.' || cplrba_bof “low_rba”,
3 cpodr_seq || ‘.' || cpodr_bno || ‘.' || cpodr_bof “on_disk_rba”
4 FROM x$kcccp;

CPDRT low_rba on_disk_rba
———- —————————— ——————————
0 4294967295.4294967295.65535 15.2.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0

8 ROWS selected.

SQL> UPDATE t1 SET owner='www.killdb.com' WHERE object_id=28;

1 ROW updated.

SQL> commit;

Commit complete.

SQL> SELECT cpdrt,
2 cplrba_seq || ‘.' || cplrba_bno || ‘.' || cplrba_bof “low_rba”,
3 cpodr_seq || ‘.' || cpodr_bno || ‘.' || cpodr_bof “on_disk_rba”
4 FROM x$kcccp;

CPDRT low_rba on_disk_rba
———- —————————— ——————————
3 15.2.0 15.4.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0
0 0.0.0 0.0.0

8 ROWS selected.
www.2cto.com
SQL> SELECT * FROM v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 13 52428800 1 NO INACTIVE 918247 07-NOV-11
2 1 14 52428800 1 NO INACTIVE 918274 07-NOV-11
3 1 15 52428800 1 NO CURRENT 918300 07-NOV-11

我们可以看到，对应由脏块组成的checkpoint queue队列，DBWR是按照seq为顺序将脏块写入到disk中的。
另外通过controlfile的dump，我们也能看到low rba和on disk scn的信息，如下：
SQL> ALTER SESSION SET events ‘immediate trace name controlf level 8';

SESSION altered.++++++ trace 部分信息如下：++++++

CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 – status:0x2 flags:0x0 dirty:3
low cache rba:(0xf.2.0) on disk rba:(0xf.4.0)
on disk scn: 0x0000.000e0330 11/07/2011 01:43:51
resetlogs scn: 0x0000.000cf315 11/03/2011 02:05:04
heartbeat: 766575498 mount id: 2470768355
THREAD #2 – status:0x0 flags:0x0 dirty:0

low cache rba:(0xf.2.0) — （f->15,2->2,0->0),跟上面查询x$kcccp的结果是一样的。
最后补充一下，10g oracle引入了增量检查点自动调节机制，其中相关的几个参数如下：

SQL> SHOW parameter checkpoint

NAME TYPE VALUE
———————————— ———– ——————————
_disable_incremental_checkpoints BOOLEAN FALSE
_disable_selftune_checkpointing BOOLEAN FALSE
_log_checkpoint_recovery_check INTEGER 0
_selftune_checkpoint_write_pct INTEGER 3
_selftune_checkpointing_lag INTEGER 300
log_checkpoint_interval INTEGER 0
log_checkpoint_timeout INTEGER 1800
log_checkpoints_to_alert BOOLEAN FALSE

_disable_incremental_checkpoints 决定是否启用增量检查点
_disable_selftune_checkpointing 决定是否启动增量检查点启动调节，默认是FALSE
_selftune_checkpoint_write_pct 是一个百分比
_selftune_checkpointing_lag 默认值是300s。

声明: 除非转自他站（如有侵权，请联系处理）外，本文采用 BY-NC-SA 协议进行授权 | 智乐兔
转载请注明：转自《oracle checkpoint作用释疑4》
本文地址：https://www.zhiletu.com/archives-455.html
关注公众号：