free buffer inspected and dirty buffer inspected

Jack_ma

在reference里看到free buffer inspected的意思：
Number of buffers  skipped over from the end of an LRU queue in order to find a reusable buffer. The difference between this statistic and "dirty buffers inspected"  is the number of buffers that could not be used because they had a user, a waiter, or were  being read or written, or because they were busy or needed to be written after rapid aging out

没看懂什么是dirty buffer inspected， free buffer inspected 跳过的buffer不就是不能用的吗?("could not be used because ...")

求大神帮忙解释， 谢谢

Maclean Liu(刘相兵

ODM FINDING:

free buffer inspected  ==>The number of buffers skipped by foreground processes that are
searching for a free buffer to reuse

MORE INFO==

The number of buffers skipped over from the end of the LRU queue in order to find a free buffer.The difference between this and "dirty buffers inspected" is the number of buffers that could not be used because they were busy or needed to be written after rapid aging out. They may have a user, a waiter, or being read/written.





dirty buffers inspected ==> This is the number of times a foreground encountered a dirty
buffer which had aged out through the lru queue, when foreground
is looking for a buffer to reuse

The number of times a foreground encountered a dirty buffer which had aged out through the LRU queue, when foreground is looking for a buffer to reuse. This should be zero if DBWR is keeping up with foregrounds.

Maclean Liu(刘相兵

free buffer inspected 包含了 dirty buffers inspected

即 free buffer inspected include ( (dirty buffers inspected) &  (busy  buffers inspected) )

Busy Buffer 可能当前正被PIN、FAST_PIN ， 或有buffer waiter

Jack_ma

dirty buffers inspected ==> This is the number of times a foreground encountered a dirty buffer which had aged out through the lru queue, when foreground is looking for a buffer to reuse
问题：  一个lru上的buffer被修改以后应该立刻被送到lruw才对啊， 前台进程从lru尾端开始扫描free buffer， 怎么会遇到dirty buffer呢？

Maclean Liu(刘相兵

” 一个lru上的buffer被修改以后应该立刻被送到lruw才对啊“
==》如何得出这个结论的？

Jack_ma

我理解错了？   
哪里可以看到lru lruw的详细机制（主链，辅链，主辅链buffer转移机制，以及lru脏块合适转移到lruw等...），貌似manual里面没有， 我没有找到。
麻烦提供些资料或是link， 谢谢

Maclean Liu(刘相兵

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

[oracle@vrh8 ~]$ ps -ef|grep dbw
oracle    4321     1  0 Jun15 ?        00:01:04 ora_dbw0_G10R25
oracle   29550 29527  0 08:02 pts/2    00:00:00 grep dbw


SQL> oradebug setospid 4321
Oracle pid: 5, Unix process pid: 4321, image: oracle@vrh8.oracle.com (DBW0)
SQL> oradebug suspend;
Statement processed.


HANG住 DBWR

SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from dirty_int;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               67154                                    1
                               67154                                    1
                               67154                                    1
                                                          
                                                          
SQL> update dirty_int set t1=t1+10;

3 rows updated.






SQL> select state,dirty_queue,count(*) from x$bh group by state,dirty_queue;

     STATE DIRTY_QUEUE   COUNT(*)
---------- ----------- ----------
         1           0          4
         0           0      54490
         3           0          2

没有                 DIRTY_QUEUE!=0的buffer

等10分钟

SQL> exec dbms_lock.sleep(600);


SQL> select state,dirty_queue,count(*) from x$bh group by state,dirty_queue;

     STATE DIRTY_QUEUE   COUNT(*)
---------- ----------- ----------
         1           0       1434
         0           0      53060
         3           0          2
                 
                 
                 
                 
                 
另一个session中：


SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump buffers 1;
Statement processed.


BH (0x7cfb58e8) file#: 1 rdba: 0x00410652 (1/67154) class: 1 ba: 0x7c77a000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 20
  dbwrid: 0 obj: 93542 objn: 93542 tsn: 0 afn: 1
  hash: [80fbea78,a41d1dc0] lru: [77f87d58,82f94518]
  lru-flags: debug_dump
  obj-flags: object_ckpt_list
  ckptq: [77f7c538,77f87d08] fileq: [78f9a9c8,a38cd1b8] objq: [97901a40,97901a40]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty gotten_in_current_mode redo_since_read
  LRBA: [0x50a.14094.0] HSCN: [0x0.bd81c6] HSUB: [3]
BH (0x80fbea78) file#: 1 rdba: 0x00410652 (1/67154) class: 1 ba: 0x8088c000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 20
  dbwrid: 0 obj: 93542 objn: 93542 tsn: 0 afn: 1
  hash: [7fff9398,7cfb58e8] lru: [a42358f8,7fff9418]
  lru-flags: debug_dump moved_to_tail
  ckptq: [NULL] fileq: [NULL] objq: [7fff9488,97901a30]
  st: CR md: NULL tch: 2
  cr: [scn: 0x0.bd81c5],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.bd81c5],[sfl: 0x0],[lc: 0x0.0]
  flags: gotten_in_current_mode redo_since_read
BH (0x7fff9398) file#: 1 rdba: 0x00410652 (1/67154) class: 1 ba: 0x7ff70000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 20
  dbwrid: 0 obj: 93542 objn: 93542 tsn: 0 afn: 1
  hash: [73f85368,80fbea78] lru: [80fbeaf8,8cff8868]
  lru-flags: debug_dump moved_to_tail
  ckptq: [NULL] fileq: [NULL] objq: [86fd43a8,80fbeb68]
  st: CR md: NULL tch: 1
  cr: [scn: 0x0.bd81bf],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.bd81bf],[sfl: 0x0],[lc: 0x0.0]
  flags: only_sequential_access
BH (0x73f85368) file#: 1 rdba: 0x00410652 (1/67154) class: 1 ba: 0x731ca000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 20
  dbwrid: 0 obj: 93542 objn: 93542 tsn: 0 afn: 1
  hash: [73f85258,7fff9398] lru: [73f852d8,77fc5648]
  lru-flags: debug_dump on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0
  flags:
BH (0x73f85258) file#: 1 rdba: 0x00410652 (1/67154) class: 1 ba: 0x731c8000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 20
  dbwrid: 0 obj: 93542 objn: 93542 tsn: 0 afn: 1
  hash: [73f829f8,73f85368] lru: [73f82a78,73f853e8]
  lru-flags: debug_dump on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0
  flags:
BH (0x73f829f8) file#: 1 rdba: 0x00410652 (1/67154) class: 1 ba: 0x7317c000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 20
  dbwrid: 0 obj: 93542 objn: 93542 tsn: 0 afn: 1
  hash: [a41d1dc0,73f85258] lru: [79f7f668,73f852d8]
  lru-flags: debug_dump moved_to_tail on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0
  flags:  
               

开一个新session flush buffer_cache会被HANG
               
SQL> alter system flush buffer_cache;




再开一个session查询：

SQL>  select state,dirty_queue,count(*) from x$bh group by state,dirty_queue;

     STATE DIRTY_QUEUE   COUNT(*)
---------- ----------- ----------
         1           0         34
         1           3         37
         0           0      54425
                 
                 
                 
有37个Xcurrent buffer 的dirty queue为3了


SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump buffers 1;
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_29896.trc


BH (0x7cfb58e8) file#: 1 rdba: 0x00410652 (1/67154) class: 1 ba: 0x7c77a000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: 93542 objn: 93542 tsn: 0 afn: 1
  hash: [80fbea78,a41d1dc0] lrux-rng: [74fd1478,7dfd28a8]
  lru-flags: debug_dump
  obj-flags: object_ckpt_list
  ckptq: [77f7c538,77f87d08] fileq: [78f9a9c8,a38cd1b8] objq: [97901a40,97901a40]
  st: XCURRENT md: NULL tch: 1
  flags: buffer_dirty gotten_in_current_mode redo_since_read
          flush_after_writing
  LRBA: [0x50a.14094.0] HSCN: [0x0.bd81c6] HSUB: [3]
  
  
  
  lru: [77f87d58,82f94518]  VS  lrux-rng: [74fd1478,7dfd28a8]
  
  可以看出 LRU到LRU-X的移动 是这么触发的吧？
  
               

Jack_ma

不知道我说的对不对：
flush buffer_cache hang住的原因是： 当我们执行flush biffer_cache的时候， dbwr启动， 将脏块写回磁盘，但[ file#: 1 rdba: 0x00410652 (1/67154)]块被pin住，刷不出去，所以flush操作hang住。

你的意思是：只有当dbwr启动的时候，才会触发lru的dirty block移到lruw

不知道我理解的对不对？

Maclean Liu(刘相兵

不是，  是flush buffer_cache触发了 那个dirty buffer从 LRU 移动到LRUW

而在静止的10分钟内，这个dirty buffer都没有从LRU 移动到LRUW

这说明 需要有一只 有型的手 主动去做这样一件事 ，而事实是 FLUSH buffer_cache也这样做了

Jack_ma

1. 那为什么flush hang住呢？
2. 数据库不可能经常执行flush buffer_cache操作， 在没有执行flush操作的时候， 又是什么触发LRU 移动到LRUW的移动呢？

Maclean Liu(刘相兵

FOR Question 1:

因为FLUSH BUFFER_CACHE 要写出脏块， 但是DBW HANG住了

Maclean Liu(刘相兵

FOR QUESTION 2:

dirty buffers inspected ==> This is the number of times a foreground encountered a dirty
buffer which had aged out through the lru queue, when foreground
is looking for a buffer to reuse

The number of times a foreground encountered a dirty buffer which had aged out through the LRU queue, when foreground is looking for a buffer to reuse. This should be zero if DBWR is keeping up with foregrounds.