Oracle dbwr 什么意思,Oracle 过程之DBWR运行原理

Oracle 进程之DBWR运行原理

在讲DBWR进程之前，简单介绍下working set。即working set=LRU+LRUW。LRU和LRUW总是成对出现。LRU指的是替换列，主要分为主列和辅助列。主列指的是已使用的缓冲区列，以hot区域和cold区域区分管理。LRUW指的是记录列。也分为主列和辅助列。主列主要指的是已修改的缓冲区列。辅助列指的是当前DBWR写入中的缓冲区列。DBWR运行原理如下：

当进程在辅助LRU链表和主LRU链表上扫描以查找可以覆盖的buffer header(空闲缓冲区)时，如果已经扫描的buffer header的数量到达一定的限度(由隐藏参数：_db_block_max_scan_pct决定)时，触发DBWR进程。 _db_block_max_scan_pct表示已经扫描的buffer header的个数占整个LRU链表上buffer header总数的百分比。通过DBWR保证拥有空闲缓冲区为止，这时，搜索可用buffer header的进程挂起，在v$session_wait中表现为等待“free buffer wait”事件，同时增加v$sysstat中的“dirty buffers inspected”和“DBWR make free requests”的值。

引用

NAME                                          VALUE                PDESC

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

_db_block_max_scan_pct                        40                   Percentage of buffers to inspect when looking for free

引用

SQL> select KVITVAL,KVITTAG,KVITDSC from x$kvit

2  where KVITTAG='kcbfsp';

KVITVAL KVITTAG                             KVITDSC

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

40 kcbfsp                              Max percentage of LRU list foreground can scan for free

SQL> col VALUE for 999999999999

SQL> select * from v$sysstat

2  where name like '%dirty buffers inspected%';

STATISTIC# NAME                                               CLASS                  VALUE

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

76 dirty buffers inspected                                8                      0

当DBWR在获得cache buffer lru chain后，从cold区域的尾部开始检索LRUW列，主LRUW链表上查找已经更新完而正在等待被写入数据文件的buffer header时，如果找到的buffer header的数量超过一定限度(由隐藏参数：_db_writer_scan_depth_pct决定，表示已经扫描的脏数据块的个数占整个主LRUW链表上 buffer header总数的百分比。)时，DBWR就不再继续往下扫描，而转到辅助 LRUW链表上将其上的脏数据块写入数据文件。

引用

NAME                                          VALUE                PDESC

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

_db_writer_scan_depth_pct                     25                   Percentage of LRU buffers for dbwr to scan when looking for dirty

若找到想记录到磁盘上的缓冲区，在获得buffer lock后记录到磁盘，而记录到磁盘上的缓冲区变更为空闲缓冲区，并移动到LRU列。每当DBWR检索LRUW列时，DBWR lru scans统计值和DBWR buffers scanned统计值都会增加。

引用

SQL> select name,value from v$sysstat where name='DBWR lru scans';

NAME                                                                  VALUE

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

DBWR lru scans                                                            0

SQL> select name,value from v$sysstat where name='DBWR buffers scanned';

NAME                                                                  VALUE

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

DBWR buffers scanned                                                      0

如果主LRUW链表和辅助LRUW链表上的脏数据块的总数超过一定限度，也将触发DBWR进程。该限度由隐藏参数：_db_large_dirty_queue决定。

引用

NAME                                          VALUE                PDESC

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

_db_large_dirty_queue                         25                   Number of buffers which force dirty queue to be written

SQL> col KVITTAG for a35

SQL> set linesize 300

SQL> select KVITVAL,KVITTAG,KVITDSC from x$kvit

2  where KVITTAG='kcbldq';

KVITVAL KVITTAG                             KVITDSC

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

25 kcbldq                              large dirty queue if kcbclw reaches this

每隔10秒钟唤醒一次DBWR，由隐含参数_dbwr_scan_interval决定，在9i中该值为10s，在10g中该值调整为300s。

引用

NAME                                          VALUE                PDESC

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

_dbwr_scan_interval                           10                   dbwriter scan interval

另外要检索LRU和LRUW列，必须获得cache buffer lru chains锁存器，因此当并发进程检索LRU和LRUW列时，将引起latch:cache buffer lru chains等待事件。查看cache buffer lru chains锁存器由隐含参数_db_block_lru_latches或者可以用v$latch_children视图

引用

NAME                                          VALUE                PDESC

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

_db_block_lru_latches                         16                   number of lru latches

SQL> select count(*) from v$latch_children where name='cache buffers lru chain';

COUNT(*)

----------

16

Oracle 有多种缓冲池(buffer pool)，各缓冲池分别使用这些锁存器：

引用

SQL> select d.blk_size,c.child#,p.bp_name,c.gets,c.sleeps

2  from x$kcbwds d,v$latch_children c,x$kcbwbpd p

3  where d.set_latch=c.addr

4  and d.set_id between p.bp_lo_sid and p.bp_hi_sid

5  order by c.child#;

BLK_SIZE     CHILD# BP_NAME                    GETS     SLEEPS

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

8192          1 KEEP                         28          0

8192          2 KEEP                         28          0

8192          3 RECYCLE                      28          0

8192          4 RECYCLE                      28          0

8192          5 DEFAULT                   28531          0

8192          6 DEFAULT                 1048168          0

2048          7 DEFAULT                      28          0

2048          8 DEFAULT                      28          0

4096          9 DEFAULT                      28          0

4096         10 DEFAULT                      28          0

8192         11 DEFAULT                      28          0

BLK_SIZE     CHILD# BP_NAME                    GETS     SLEEPS

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

8192         12 DEFAULT                      28          0

16384         13 DEFAULT                      28          0

16384         14 DEFAULT                      28          0

32768         15 DEFAULT                      28          0

32768         16 DEFAULT                      28          0

16 rows selected.

另外锁存器的数量和DBWR数，CPU数量有关，若DBWR数小于4个，则创建4*CPU_COUNTlru个锁存器；若DBWR数大于4，则创建DB_WRITER_PROCESSED*CPU_COUNT个锁存器。

引用

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE

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

cpu_count                            integer     4

SQL> show parameter db_writ

NAME                                 TYPE        VALUE

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

db_writer_processes                  integer     1