| Profilo di haiOracle & StarcraftBlogElenchi |  Strumenti  Guida |
|
15 marzo DBWR parallel query checkpoint buffers writtenDBWR 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而写出的块数。
Commenti (20)Per aggiungere un commento, accedi con il tuo Windows Live ID (se utilizzi Hotmail, Messenger o Xbox LIVE possiedi già un Windows Live ID). Accedi Non hai ancora un Windows Live ID? Registrati
RiferimentiL'URL di riferimento per questo intervento è: http://wzwanghai.spaces.live.com/blog/cns!56626E237AFBD116!302.trak Blog che fanno riferimento a questo intervento
|
|
|
