oracle checkpoint作用释疑1

3. 对象检查点

那么object checkpoint是什么时候触发的呢？通常是如下两种情况：

drop table xxx/xxx purge;
drop index xxx;
truncate table xxx;

那么如何理解object checkpoint呢？我认为可以这样简单理解：

drop table killdb; 将触发一个object 检查点，将cache buffer中所有
跟killdb对象有关的脏块写入到disk中。

sql> ALTER system FLUSH BUFFER_CACHE;

System altered.

sql> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# >74 AND a.statistic# 4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR thread checkpoint buffers written 64
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 128
DBWR object DROP buffers written 33
DBWR TRANSACTION TABLE writes 76
DBWR undo block writes 169
DBWR revisited being-written buffer 0
DBWR make free requests 0
DBWR lru scans 0
DBWR checkpoints 15
DBWR fusion writes 0

11 ROWS selected.

sql> CREATE TABLE t1(id NUMBER);

TABLE created.

sql> INSERT INTO t1 VALUES(1);

1 ROW created.

sql> commit;

Commit complete.

SQL> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# >74 AND a.statistic# 4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR thread checkpoint buffers written 64
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 128
DBWR object DROP buffers written 33
DBWR TRANSACTION TABLE writes 76
DBWR undo block writes 169
DBWR revisited being-written buffer 0
DBWR make free requests 0
DBWR lru scans 0
DBWR checkpoints 15
DBWR fusion writes 0

11 ROWS selected.

SQL> DROP TABLE t1;

TABLE dropped.

SQL> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# >74 AND a.statistic# 4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR thread checkpoint buffers written 64
DBWR tablespace checkpoint buffers written 0
DBWR parallel query checkpoint buffers written 128
DBWR object DROP buffers written 41
DBWR TRANSACTION TABLE writes 76
DBWR undo block writes 169
DBWR revisited being-written buffer 0
DBWR make free requests 0
DBWR lru scans 0
DBWR checkpoints 16
DBWR fusion writes 0

11 ROWS selected.
另外，dbsnake大牛以前也写过一篇相关的文章，详见如下链接：

详细解析truncate引发的object checkpoint

4. 并行查询检查点

对应并行查询，由于是走direct read，所以oracle会触发检查点将buffer中的脏块写入到disk中，

SQL> SELECT statistic#,name FROM v$statname WHERE name LIKE ‘%DBWR%';

STATISTIC# NAME
———- —————————————————————-
74 DBWR checkpoint buffers written
75 DBWR thread checkpoint buffers written
76 DBWR tablespace checkpoint buffers written
77 DBWR parallel query checkpoint buffers written
78 DBWR object DROP buffers written
79 DBWR TRANSACTION TABLE writes
80 DBWR undo block writes
81 DBWR revisited being-written buffer
82 DBWR make free requests
83 DBWR lru scans
84 DBWR checkpoints
85 DBWR fusion writes

12 ROWS selected.

SQL> ALTER system FLUSH BUFFER_CACHE;

System altered.

SQL> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# IN (77, 84)
4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 123
DBWR checkpoints 9

SQL> DELETE FROM t1 WHERE rownum commit;

Commit complete.

SQL> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# IN (77, 84)
4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 123
DBWR checkpoints 9

SQL> SELECT/*+ parallel(t1,4)*/DISTINCT id FROM t1;

ID
———-
1314

SQL> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# IN (77, 84)
4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 124
DBWR checkpoints 10

SQL> DELETE FROM t1 WHERE rownum commit;

Commit complete.

SQL> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# IN (77, 84)
4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 124
DBWR checkpoints 10

SQL> ALTER SESSION SET events ‘10046 trace name context forever,level 12';

SESSION altered.

SQL> SELECT/*+ parallel(t1,4)*/DISTINCT id FROM t1;

ID
———-
1314

SQL> ALTER SESSION SET events ‘10046 trace name context off';

SESSION altered.

SQL> SELECT b.name, a.VALUE
2 FROM v$sysstat a, v$statname b
3 WHERE a.statistic# IN (77, 84)
4 AND a.statistic# = b.statistic#;

NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 128
DBWR checkpoints 11
我们可以看到，在第2次执行parallel query时，DBWR写了4个block (128-124)，但是
此时的checkpoint只发生了1次，这也直接验证了parallel query checkpoint。
这里其实还有object checkpoint产生，我们通过10046 trace可以看出，如下：
=====================
PARSING IN CURSOR #3 len=45 dep=0 uid=60 oct=3 lid=60 tim=1289872258867729 hv=4038107070 ad='25f2dd08′
select/*+ parallel(t1,4)*/distinct id from t1
END OF STMT
PARSE #3:c=57992,e=56729,p=460,cr=81,cu=0,mis=1,r=0,dep=0,og=1,tim=1289872258867724
BINDS #3:
WAIT #3: nam='enq: KO – fast object checkpoint' ela= 6 name|mode=1263468550 2=65557 0=1 obj#=52229 tim=1289872258868398
WAIT #3: nam='enq: KO – fast object checkpoint' ela= 5018 name|mode=1263468550 2=65557 0=1 obj#=52229 tim=1289872258873457
WAIT #3: nam='PX Deq: Join ACK' ela= 1732 sleeptime/senderid=268500992 passes=1 p3=0 obj#=52229 tim=1289872258875411
WAIT #3: nam='PX Deq: Join ACK' ela= 10 sleeptime/senderid=268500996 passes=1 p3=0 obj#=52229 tim=1289872258875734
WAIT #3: nam='PX Deq: Join ACK' ela= 1926 sleeptime/senderid=268500996 passes=2 p3=0 obj#=52229 tim=1289872258877717
WAIT #3: nam='PX Deq: Parse Reply' ela= 7 sleeptime/senderid=200 passes=1 p3=0 obj#=52229 tim=1289872258877917
WAIT #3: nam='PX Deq: Parse Reply' ela= 91227 sleeptime/senderid=200 passes=2 p3=0 obj#=52229 tim=1289872258969183
WAIT #3: nam='PX Deq: Parse Reply' ela= 7 sleeptime/senderid=200 passes=1 p3=0 obj#=52229 tim=1289872258969352
WAIT #3: nam='PX Deq: Parse Reply' ela= 2059 sleeptime/senderid=200 passes=2 p3=0 obj#=52229 tim=1289872258971446

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