Oracle随笔：深入解析DBWR

认真就输DBA

于 2025-08-19 16:46:27 发布

阅读量454

点赞数 8

CC 4.0 BY-SA版权

分类专栏：学习随笔 Oracle 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/wwwhtzpw/article/details/150529476

Oracle 同时被 2 个专栏收录

168 篇文章

订阅专栏

学习随笔

84 篇文章

订阅专栏

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

本文转自朋友的真实案例分享。

Oracle随笔：深入解析DBWR

dbwr进程，Database Writer Process ，理论上最大支持20个dbwr进程，默认是 1/8 cpu_count的设置。dbwr进程的作用的将buffer cache的脏块写出到disk上。

dbwr进程写机制
首先我们要明白，dbwr进程的本质作用的是：将cache buffer中的脏块写入到disk上，使cache buffer尽量保持干净。那么这里就存在一个问题：dbwr 进程如何来写脏块？有什么机制会触发dbwr进程来写脏块？ dbwr进程是怎么进行写操作的？
在讲dbwr是如何来写脏块的之前，我们需要了解下checkpoint 机制：

checkpoint queue：检查点队列是从oracle 8i引入的，实际上是从引入增量检查点时引入的。那么什么是检查点队列呢？实际上，检查点队列就是一个列表，而这个列表上包含一系列的buffer cache中的脏块，当然，这些脏块的顺序是最早被修改的脏块在前面，稍后被修改的脏块在后，总之是根据time来排序的。每个checkpoint queue的一个位置我们称为postion，当然，这个postion就是通过rba来表示的。所以你可以理解为：checkpoint queue 就是一些脏块对应的buffer header列表，每个buffer header里面还包含了rba信息，只是这些rba是有一定顺序的。每个rba都对应buffer cache中的一个脏块，而脏块这里也是分时间前后的。

大家可以想象，oracle 的dbwn进程去写buffer cache中的脏块，是如何判断一个block是否被写或者不写的呢？这跟oracle引入的一个LRU算法有关系(Least Recently Used). 通过该算法去判断哪些脏块需要put 到checkpoint queue上。LRU 链表，它上面的信息其实是一系列的buffer header，并不是我们实际的脏块。每个buffer header里面存在一个指针，通过这个指针，可以定位到一个脏块，是一对一的关系。每次checkpoint 触发后，会触发dbwr去写脏块，dbwr进程根据checkpoint queue以及LRU-W ，顺序性的把脏块写出到disk上。

checkpoint触发的情况下，是根据checkpoint queue来写脏块，其他的一些触发机制，是根据LRU-W来的。

这里dbwr涉及2种情况写：

DBWR checkpoint buffers written —》 checkpoint queue的dbwr写块数
physical writes non checkpoint —》dbwr通过LRU-W写的脏块数
Performs writes for several reasons:

Make free requests
Checkpoints
Cleanout of cold dirty buffers
Ping writes

1）Make free requests
如果server process在搜索的lru没有发现可用的free buffer，那么就会等待DBWR进程写脏块，在等待的过程中，server process将会等待free buffer wait等待, 同时，如果你检测统计数据，你会发现dirty buffers inspected 指标的值会增加。

SYS@LX(lx):1>@sp db_block_max_scan_pct
 
-- show parameter by sp
 
-- show hidden parameter by sp
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_db_block_max_scan_pct                   40         Percentage of buffers to inspect when looking for free

这个参数的意思是：表示已经扫描的buffer header的总数占整个LRU链表上buffer header总数的百分比值。服务器进程在LRU中搜索可重用Buffer时，遇到脏块，会将脏块移至lruw，等待三秒DBWR超时，LRUW中的脏块被DBWR写磁盘。注意，如果进程在LRU中遇到TCH大于等于2的脏块，不会将其移到LRUW，而会移到热LRU。另外，被移到LRUW中的脏块，将从检查点队列中去除。

2）Checkpoints
3) Cleanout of cold dirty buffers
SYS@LX(lx):1>@sp db_large_dirty_queue
 
-- show parameter by sp
 
-- show hidden parameter by sp
 
NAME                                    VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_db_large_dirty_queue                    25        Number of buffers which force dirty queue to be written
4）Ping writes

5) timeout write

2020-11-24 09:26:22.475514 :80C3720D:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:22.475947 :80C3720E:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:22.476241 :80C3720F:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:22.476768 :80C37210:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:22.477077 :80C37211:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:25.486509 :80C3742F:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:25.486925 :80C37430:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:25.487087 :80C37431:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:25.487164 :80C37432:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:25.487220 :80C37433:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:28.500272 :80C37640:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:28.500344 :80C37641:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:28.500413 :80C37642:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:28.500510 :80C37643:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:28.500580 :80C37644:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:31.519711 :80C377F2:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:31.520010 :80C377F3:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:31.520224 :80C377F4:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:31.520339 :80C377F5:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:31.520414 :80C377F6:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:34.551728 :80C37A17:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:34.552351 :80C37A18:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:34.552409 :80C37A19:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:34.552506 :80C37A1A:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:34.552565 :80C37A1B:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:37.570449 :80C37C2E:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:37.570524 :80C37C2F:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:37.570579 :80C37C30:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:37.570656 :80C37C31:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:37.570710 :80C37C32:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:40.622597 :80C37DCF:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:40.622892 :80C37DD0:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:40.623337 :80C37DD1:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:40.623519 :80C37DD2:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:40.623577 :80C37DD3:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:43.654204 :80C37FE7:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:43.654392 :80C37FE8:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:43.654636 :80C37FE9:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:43.654901 :80C37FEA:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:43.655155 :80C37FEB:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:46.677944 :80C381E5:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:46.678242 :80C381E6:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:46.678533 :80C381E7:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:46.678679 :80C381E8:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:46.678737 :80C381E9:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:49.700315 :80C383F7:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:49.700730 :80C383F8:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:49.700790 :80C383F9:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:49.700879 :80C383FA:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:49.700936 :80C383FB:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:52.702428 :80C38610:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:52.702534 :80C38611:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:52.702605 :80C38612:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:52.702701 :80C38613:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:52.702770 :80C38614:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]
2020-11-24 09:26:55.707693 :80C3879F:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
2020-11-24 09:26:55.708179 :80C387A0:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[3] comment=[KSB action for bast checking]
2020-11-24 09:26:55.708493 :80C387A1:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[77] comment=[DBWR write buffers]
2020-11-24 09:26:55.708996 :80C387A2:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[251] comment=[kfc invalidate file extent]
2020-11-24 09:26:55.709305 :80C387A3:db_trace:ksb.c@1835:ksbcti(): [10254:10:5] KSBCTI: (DBW0) : (timeout action)   : acnum=[255] comment=[kfcb Poke DBW0]

注意：dbwr还存在一个固定唤醒机制，在10g中每300s会唤醒一次，如下参数进行控制：

SQL> show parameter _dbwr_scan_interval
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_dbwr_scan_interval integer 300

dbwr的写操作：

batch写
每个Batch中IO是异步，Batch与Batch之间为串行。比如，某个Batch是100个块，发现相连的块有50个、40个、8个，还有两个块不相连，将50个块从Buffer Cache的不同位置复制到共享池中一块连续的大内存中（此时如果内存不足，会报4031），提交异步IO，将此连续的大块内存写磁盘。再将40个块从Buffer Cache的不同位置也复制到共享池中一块连续的大内存，提交异步IO，等等。对于不相连的块，不在复制，直接提交异步IO从Buffer Cache写磁盘。然后等这个Batch中所有IO完成，开始下一个Batch。所有，Batch与Batch之间，是同步、串行的。
合并写

SYS@LX(lx):1>@sp db_writer_coalesce
 
-- show parameter by sp
 
-- show hidden parameter by sp
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_db_writer_coalesce_write_limit          131072     Limit on size of coalesced write
_db_writer_coalesce_area_size            4190016    Size of memory allocated to dbwriter for coalescing writes
 
xsv: slt=7f0cf1e2c880, nfr=4056, buf=0x8e3fab30, dba=0x10000a2, nwr=7, clse=1
xsv: slt=7f0cf218ec70, nfr=4055, buf=0x8e7f48c0, dba=0x10000a3, nwr=7, clse=1
xsv: slt=7f0cf1e2bba0, nfr=4054, buf=0x8e7f4b90, dba=0x10000a1, nwr=7, clse=1
xsv: slt=7f0cf210fa30, nfr=4053, buf=0x8d3ea9d8, dba=0x10000a4, nwr=7, clse=1
xsv: slt=7f0cf2382e40, nfr=4052, buf=0x9cbe6a90, dba=0x10000a0, nwr=7, clse=1
xsv: slt=7f0cf20e3c80, nfr=4051, buf=0x8cbd8990, dba=0x10000a5, nwr=7, clse=1
xsv: slt=7f0cf1d79160, nfr=4050, buf=0x8e3eaca8, dba=0x100009f, nwr=7, clse=1
xsv: slt=7f0cf1e2a850, nfr=4049, buf=0x85fe7030, dba=0x10000a6, nwr=7, clse=1
xsv: slt=7f0cf1d5ae90, nfr=4048, buf=0x8e3df8a8, dba=0x100009e, nwr=7, clse=1
xsv: slt=7f0cf1f67910, nfr=4047, buf=0x85ff9a50, dba=0x10000a7, nwr=7, clse=1
xsv: slt=7f0cf1d5b500, nfr=4046, buf=0x8e3f2598, dba=0x100009d, nwr=7, clse=1
xsv: slt=7f0cf20c9a10, nfr=4045, buf=0x8e3eae10, dba=0x100009c, nwr=7, clse=1
xsv: slt=7f0cf22cd080, nfr=4044, buf=0x8e3dfa10, dba=0x100009b, nwr=7, clse=1
xsv: slt=7f0cf2384800, nfr=4043, buf=0x8e3d67d0, dba=0x100009a, nwr=7, clse=1
xsv: slt=7f0cf1e2aec0, nfr=4042, buf=0x8e3eaf78, dba=0x1000099, nwr=7, clse=1
xsv: slt=7f0cf2037fb0, nfr=4041, buf=0x8e3dfb78, dba=0x1000098, nwr=7, clse=1
coalesce: 1 - slt=7f0cf1e2c880, nfr=4041, nwr=7, nbuf=16, maxbufs=16, base_rdba 0x10000a2

第一个参数可以控制dbwr进程在写的时候，batch的大小，换句话讲，batch越大，batch写的次数就少。可以适当提高dbwr进程的性能。

注意：根据我在生产环境中的观察，该参数可能会导致dbwr进行消耗过多的内存。需要慎重。

SYS@LX(lx):1>select * from v$sgastat where name like '%dbwriter coalesce%';             
 
POOL                                 NAME                                                                                BYTES
------------------------------------ ------------------------------------------------------------------------------ ----------
shared pool                          dbwriter coalesce struct                                                               48
shared pool                          dbwriter coalesce buffer                                                          3979264
shared pool                          dbwriter coalesce bitmap                                                              248

异步写

SYS@LX(lx):1>@sp dbwr_async
 
-- show parameter by sp
 
-- show hidden parameter by sp
 
NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_dbwr_async_io                           TRUE       Enable dbwriter asynchronous writes

dbwr进程写在赃块时是进行异步写，该机制默认是开启的。不仅如此，对于batch的写操作，一个batch内的IO也是异步的。batch和batch写之间是串行的。

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)