其实大家对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的深层含义.