如何知道incremental checkpoint的进度

shift_1

有朋友问我："incremental checkpoint似乎看不见，摸不着，我怎么知道我系统里incremental checkpoint发生的频率呢，或者说我如何才能知道incremental checkpoint的进度？"

首先要说明的是，什么是incremental checkpoint？

These disjointed activities
of writing buffers in ascending low RBA order and periodically recording the lowest low RBA in the control file constitute incremental checkpoint。

注意这里理解incremental checkpoint最为关键的地方就是上述我标明的两个单词----disjointed activities。

接着我们来说上述问题的答案：

Oracle里有一个内存结构X$KCBBES，以indx为4的条件去查询上述结构，列reason的值就是当前系统的number of db block buffers written to the disk for incremental checkpoint，换句话说，这就可以用来衡量严格意义上的incremental checkpoint的进度。

X$KCBBES = [K]ernel [C]ache [B]uffer Management Buffer Event Statistics

Column

Type

Comments

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

------

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

ADDR
RAW(4)

Address

INDX

NUMBER
Reason / Priority / Savecode number

INST_ID

NUMBER
Instance id

REASON
NUMBER
Count of buffers written for this REASON

PRIORITY
NUMBER
Count of buffers written at this PRIORITY

SAVECODE
NUMBER
Count of occurrences of return to write buffers

indx的具体含义为：

KCBB_INVALID

0
invalid reason

KCBB_PING

1
Ping write

KCBB_HIPR_CKPT

2
High priority thread checkpoint

KCBB_IRCV_CKPT

3
Instance recovery checkpoint

KCBB_MDPR_CKPT

4
Medium priority thread checkpoint

                                  (typically due to incremental checkpoint)

KCBB_AGING

5
Aging writes

KCBB_MRCV_CKPT

6
Media recovery checkpoint

KCBB_LOPR_CKPT


7
Low priority thread checkpoint

KCBB_TBSP_CKPT

8
Tablespace checkpoint

KCBB_REUSE_OBJ

9
Reuse
object

KCBB_REUSE_RNG
10
Reuse block range

KCBB_DBUF_LMT

11
Limit dirty buffers

我们看一个实际的例子，如下的这个系统中午大家都吃饭去了，很闲，还没有达到触发DBWn进程写dirty buffer的条件，所以Checkpoint Queue上的Checkpoint Position（即low cache RBA）在一段时间内始终没有发生变化：

11:49:52 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4
4404345

11:52:44 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4

4404345

11:56:46 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4
4404345

12:08:33 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4
4404345

即此时的incremental checkpoint虽然也在不停的发生，但从严格意义上说，并不能算真正的incremental checkpoint，因为只是CKPT进程每隔3秒去更新一下control文件（记录下low cache RBA），DBWn进程始终没有写Checkpoint Queue上的dirty buffer（所以low cache RBA没有变化）。

下午1点半，大家吃完饭回来了，开始干活了，真正意义上的incremental checkpoint一下子变得频繁起来：

13:31:17 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4
4404345

13:35:22 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4
4404580

13:38:37 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4
4404583

13:42:15 SQL> select indx,reason from x$kcbbes where indx=4;

INDX
REASON

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

4
4409684

renxiao2003

从什么地方转载的啊。