在不同的版本中,Oracle的检查点策略一直在不断的调整优化,而从Oracle7之后,Oracle不在文档中透漏更多的信息给我们。

关于检查点的描述,很多文档上大都是简单的一句话,通常是:

When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process. The CKPT process does not write blocks to disk; DBWn always performs that work.

这段描述实际上过于简略了,在不同的版本中,Oracle的检查点策略一直在不断的调整优化,而从Oracle7之后,Oracle不在文档中透漏更多的信息给我们.

在Oracle7中,通过跟踪CKPT进程我们可以看到,当我们手工执行检查点时:

C:orantBIN>SVRMGR23.EXE Oracle Server Manager Release 2.3.4.0.0 - Production Copyright (c) Oracle Corporation 1994, 1995. All rights reserved. Oracle7 Workgroup Server Release 7.3.4.0.0 - Production
With the distributed option
PL/SQL Release 2.3.4.0.0 - Production SVRMGR> connect internal
Connected to an idle instance.
SVRMGR> startup
Oracle instance started.
Total System Global Area 99967716 bytes
Fixed Size 35760 bytes
Variable Size 58808116 bytes
Database Buffers 40960000 bytes
Redo Buffers 163840 bytes
Database mounted.
Database opened.
SVRMGR> alter system checkpoint;
Statement processed.

Oracle后台CKPT进程如下动作:

WAIT #0: nam='control file sequential read' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=4 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=5 p3=1
WAIT #0: nam='db file sequential read' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=6 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=8 p3=1
WAIT #0: nam='db file sequential read' ela= 0 p1=2 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=2 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='db file sequential read' ela= 0 p1=3 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=3 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='db file sequential read' ela= 0 p1=4 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=4 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='rdbms ipc message' ela= 0 p1=300 p2=0 p3=0

注意,这里的等待事件 "db file single write" 既是对于数据文件头的更新,以刷新数据文件记录的检查点信息.

同样在Oracle7中,当发生log switch之后,触发检查点,CKPT进程需要更新数据文件头及控制文件:

SVRMGR> select * from v$version;
BANNER
----------------------------------------------------------
Oracle7 Workgroup Server Release 7.3.4.0.0 - Production
PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for 32-bit Windows: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 - Production
5 rows selected.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR>

我们看CKPT进程的动作:

WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=3 p3=1
WAIT #0: nam='rdbms ipc message' ela= 0 p1=202 p2=0 p3=0
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=3 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=6 p3=1
WAIT #0: nam='db file sequential read' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=1 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=5 p3=1
WAIT #0: nam='control file sequential read' ela= 0 p1=0 p2=7 p3=1
WAIT #0: nam='db file sequential read' ela= 0 p1=2 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=2 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='db file sequential read' ela= 0 p1=3 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=3 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='db file sequential read' ela= 0 p1=4 p2=1 p3=1
WAIT #0: nam='db file single write' ela= 0 p1=4 p2=1 p3=1
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='control file parallel write' ela= 0 p1=2 p2=2 p3=2
WAIT #0: nam='rdbms ipc message' ela= 0 p1=300 p2=0 p3=0

在Oracle7中,logfile switch 和 alter system checkpoint都会触发complete checkpoint, 此时DBWR会把Dirty Buffer写出到数据文件. 从DBWR的跟踪我们可以轻易的看出.

WAIT #0: nam='rdbms ipc message' ela= 0 p1=300 p2=0 p3=0
WAIT #0: nam='db file parallel write' ela= 0 p1=2 p2=3 p3=3
WAIT #0: nam='db file parallel write' ela= 0 p1=2 p2=3 p3=3
WAIT #0: nam='db file parallel write' ela= 0 p1=1 p2=2 p3=2
WAIT #0: nam='rdbms ipc message' ela= 0 p1=218 p2=0 p3=0

而从Oracle8i开始引入增量检查点之后,Oracle的检查点行为就发生了改变(待续...).