关于dirty buffer

其实大家对v$bh这个视图可能一直重视不够.

我们来看一下这个视图主要字段说明:


STATUS

VARCHAR2(1)

Status of the buffer:
FREE - not currently in use XCUR - exclusive SCUR - shared current CR - consistent read READ - being read from disk MREC - in media recovery mode IREC - in instance recovery mode ?


DIRTY

VARCHAR2(1)

Y - block modified

TEMP

VARCHAR2(1)

Y - temporary block

PING

VARCHAR2(1)

Y - block pinged

STALE

VARCHAR2(1)

Y - block is stale

DIRECT

VARCHAR2(1)

Y - direct block


?

我们注意到v$dirty代表的就是block modified

?

如果我们进一步向下追溯:

SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME = @#GV$BH@#;

VIEW_DEFINITION
--------------------------------------------------------------------------------
select bh.inst_id, file#, dbablk, class, decode(state,0,@#free@#,1,@#xcur@#,2,@#scur@#
,3,@#cr@#, 4,@#read@#,5,@#mrec@#,6,@#irec@#,7,@#write@#,8,@#pi@#), x_to_null, forced_reads,
forced_writes, bh.le_addr, name,le_class, decode(bitand(flag,1), 0, @#N@#, @#Y@#), d
ecode(bitand(flag,16), 0, @#N@#, @#Y@#), decode(bitand(flag,1536), 0, @#N@#, @#Y@#), dec
ode(bitand(flag,16384), 0, @#N@#, @#Y@#), decode(bitand(flag,65536), 0, @#N@#, @#Y@#), @#
N@#, obj, ts# from x$bh bh, x$le le where bh.le_addr = le.le_addr (+)


我们可以看到v$bh的底层表示x$bh

其中v$bh中的dirty/temp/ping/stale/direct五个字段来源于x$bh中的flag字段

格式化一下输出:

SELECT bh.inst_id, file#, dbablk, CLASS,
?????? DECODE (state,
?????????????? 0, @#free@#,
?????????????? 1, @#xcur@#,
?????????????? 2, @#scur@#,
?????????????? 3, @#cr@#,
?????????????? 4, @#read@#,
?????????????? 5, @#mrec@#,
?????????????? 6, @#irec@#,
?????????????? 7, @#write@#,
?????????????? 8, @#pi@#
????????????? ),
?????? x_to_null, forced_reads, forced_writes, bh.le_addr, NAME, le_class,
?????? DECODE (BITAND (flag, 1), 0, @#N@#, @#Y@#),
?????? DECODE (BITAND (flag, 16), 0, @#N@#, @#Y@#),
?????? DECODE (BITAND (flag, 1536), 0, @#N@#, @#Y@#),
?????? DECODE (BITAND (flag, 16384), 0, @#N@#, @#Y@#),
?????? DECODE (BITAND (flag, 65536), 0, @#N@#, @#Y@#), @#N@#, obj, ts#
? FROM x$bh bh, x$le le
?WHERE bh.le_addr = le.le_addr(+)


由此我们得以窥视x$bh.flag的深层含义.

原文转自：http://www.ltesting.net