Oracle & Starcraft

DBWR parallel query checkpoint buffers written是oracle的一种特殊的checkpoint的产物，这种checkpoint在oracle8 以前称作Extent-based Checkpoint，当发生parallel query时oracle必须做checkpoint把脏数据写入磁盘，因为parallel query走的时direct read，直接从文件读入pga，如果有脏数据没有被写回磁盘，那么读出来的结果将会不一致。
在oracle8以前，如果发生direct read，那么当每一个extent被读入的时候oracle会先去检查data buffer中有没和这个extent相关的dirty block，如果有就会驱动dbwr来写出，同时增加一次checkpoint的次数。这样的话如果这个表有N个extent都存在dirty block,那么将会发生N次checkpoint,这样的话将会使parallel query的执行时间大大超过normal query。在oracle8中这种情况有了改进，Extent-based Checkpoint变成了object-based checkpoint,不再会对单独的extent做checkpoint。

让我们再回到主题，DBWR parallel query checkpoint buffers written

首先来看一下v$statname

SQL 10G>select statistic#,name from v$statname where name like ‘%DBWR%’;
STATISTIC# NAME
———- —————————————————————-
69 DBWR checkpoint buffers written
70 DBWR thread checkpoint buffers written
71 DBWR tablespace checkpoint buffers written
72 DBWR parallel query checkpoint buffers written
73 DBWR object drop buffers written
74 DBWR transaction table writes
75 DBWR undo block writes
76 DBWR revisited being-written buffer
77 DBWR make free requests
78 DBWR lru scans
79 DBWR checkpoints
STATISTIC# NAME
———- —————————————————————-
80 DBWR fusion writes

再来创建一张测试表

create table test(a number)

SQL 10G>select count(*) from test;
COUNT(*)
———-
4194496

SQL 10G>select distinct a from test;
A
———-
1

这个测试表包含了4194496条值为1的记录

先刷新data buffer

SQL 10G>ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;
Session altered.

我们来更新其中一条记录

SQL 10G>update test set a=2 where rownum<2;
1 row updated.

SQL 10G>commit;
Commit complete.

执行parallel query

SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#;
NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 28615
DBWR checkpoints 552

SQL 10G>select/*+ parallel(test,4)*/distinct a from test;
A
———-
2
1

SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#;
NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 28616
DBWR checkpoints 553

checkpoint和buffer written都增加1

再来更新分布在多个extent上的block来证实一下是不是现在的oracle版本用的是object-based checkpoint

SQL 10G>update test set a=10 where mod(dbms_rowid.ROWID_BLOCK_NUMBER(rowid),1000)=0 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=0;
6 rows updated.

SQL 10G>commit;
Commit complete.

SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#;
NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 28616
DBWR checkpoints 553

SQL 10G>select/*+ parallel(test,4)*/distinct a from test;
A
———-
10
2
1

SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#;
NAME VALUE
—————————————————————- ———-
DBWR parallel query checkpoint buffers written 28622
DBWR checkpoints 554

可以看到写出了6个块，但是只发生一次checkpoint，所以我们可以知道oracle已经采用了object-based checkpoint。

总结来说，DBWR parallel query checkpoint buffers written就是当发生parallel query时导致checkpoint而写出的块数。